MySQL query help required

Databases
1

Hi Guys,

I have some domains stored in a database and I have created a search to return matching domains based on what the user types.

For example if the user types “h” all domains containing a “h” should be returned.

hotgolf.com
hhh.com
sphere.co.uk

The problem comes when a user searches for an “o” for example as “o” is contained in the extension (.com and .co.uk).

I am guessing that I am going to need some kind of regex to achieve what I am trying to do. Any help would be greatly appreciated!

Here is the current MySQL query I am using:


domain like '%".mysql_real_escape_string($_GET['keywords'])."%'
2

simplest solution: don’t do the analysis when retrieving the data, do it when storing the data – split the domain (e.g. example.com) into two columns, the domain (e.g. example) and the tld (e.g. com)

3

Hmmm yes I thought of doing it this way, but im integrating with an existing system and this will simply be too much work.

Is there a way using regex?

4

you could add two cols to the table and write a short query script to take the domain name, and split it and insert the results in the two new cols. Personally I would loose the ’ . ’ altogether and program for that on the view side. This would also allow you to do future searches if the visitor only wanted to look at .com names. You would then have the ability to get all the .com names from the table.

url | domain | extension

Just a suggestion.

5

A couple of questions

  1. Is the part of the domain name you wish to match on effectively up to the first dot. That is hotgolf.com not www.hotgolf.com or www3.hot.golf.com.

  2. When you have two or more characters for example “ho”, do you want them as consecutive characters in the part of the domain name being searched.

6

  1. The domains are always stored like hotgolf.com (so it will only be the first dot).

  2. Yes when I have “ho” being searched it should return hotgolf.com.

Hope this makes sense.

7

okay, now you’re in business!! :slight_smile:

WHERE [COLOR="Blue"]SUBSTRING_INDEX(domain,'.',1)[/COLOR] LIKE ...
8

will that work for .co.uk domains? Or does that make it more complicated? :slight_smile:

9

dude…

what happened when you tested it?

:smiley:

10

I think you just need to do this "select * from table where column LIKE ‘%$var’ "

11

It returned “.com” domains but no “.co.uk”

12

I mean “SElect * from table where column LIKE ‘$var%’”;

13

That won’t work. Thanks anyway

14

then ur doing it wrong :smiley: :smiley:

CREATE TABLE zaggs
( domain VARCHAR(99)
);
INSERT INTO zaggs VALUES
 ( 'hotgolf.com' )
,( 'example.com' )
,( 'hotgolf.co.uk' )
,( 'puke.com' )
;

SELECT * FROM zaggs 
WHERE SUBSTRING_INDEX(domain,'.',1) LIKE '%ho%'
;
domain
hotgolf.com
hotgolf.co.uk

SELECT * FROM zaggs 
WHERE SUBSTRING_INDEX(domain,'.',1) LIKE '%uk%'
;
domain
puke.com