Zaggs
March 9, 2010, 10:40am
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'])."%'
r937
March 9, 2010, 11:59am
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)
Zaggs
March 9, 2010, 12:01pm
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?
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.
r937
March 9, 2010, 5:49pm
7
Zaggs:
The domains are always stored like hotgolf.com (so it will only be the first dot ).
okay, now you’re in business!!
WHERE [COLOR="Blue"]SUBSTRING_INDEX(domain,'.',1)[/COLOR] LIKE ...
Zaggs
March 9, 2010, 5:57pm
8
r937:
okay, now you’re in business!!
WHERE [COLOR="Blue"]SUBSTRING_INDEX(domain,'.',1)[/COLOR] LIKE ...
will that work for .co.uk domains? Or does that make it more complicated?
r937
March 9, 2010, 6:14pm
9
dude…
what happened when you tested it?
dacoda
March 9, 2010, 6:21pm
10
I think you just need to do this "select * from table where column LIKE ‘%$var’ "
Zaggs
March 9, 2010, 6:33pm
11
It returned “.com” domains but no “.co.uk”
dacoda
March 9, 2010, 6:40pm
12
I mean “SElect * from table where column LIKE ‘$var%’”;
Zaggs
March 9, 2010, 6:42pm
13
That won’t work. Thanks anyway
r937
March 9, 2010, 6:43pm
14
then ur doing it wrong
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