Select statement MySql

Question

I'm trying to do a simple select statement on a mysql database. In MsSql it works fine, but I can't get it to work in MySql.

What I want to do is the following:

Lets say I have a table (named TestTable) like this:

ID | DOMAIN | NrOfVisitors
--------------------------------------------
1 | something.com | 435
--------------------------------------------
2 | blabla.com | 231

And I have a string, like http://bla.ttt.something.com/blabla

Now I want to pass in the string, and select the rows where the domain is in the string. In MsSQL I can do the following:

SELECT * 
 FROM TestTable 
 WHERE "http://bla.ttt.something.com/blabla" LIKE "%" DOMAIN "%"

But in MySql it returns 0 rows. WHat am I doing wrong?

Thanks in advance!

• Gumbo: Maybe you should extract the host first. That will make the lookup easier.

Answer 1

SELECT * FROM TestTable WHERE "http://bla.ttt.something.com/blabla" LIKE CONCAT("%",DOMAIN,"%")

Answer 2

Well, the first step for a robust solution would be to extract the domain from the string (using parse_url ):

$string = 'http://bla.ttt.something.com/blabla';
$domain = parse_url($string, PHP_URL_HOST);

Then, assuming you want to match on the top two levels of the domain only (something.com), you need to extract that from the domain...

$parts = explode('.', $domain);
$top2parts = array_slice($parts, -2);
$rootDomain = implode('.', $top2parts);

Then, we build the query:

$sql = "SELECT * 
 FROM TestTable 
 WHERE domain LIKE '%".mysql_real_escape_string($rootDomain)."%'";

That should do it for you. For the input given, it'll generate:

SELECT * 
 FROM TestTable 
 WHERE domain LIKE '%something.com%'

Of course you could do it the other way, and search by concating wildcards to the domain:

SELECT * 
 FROM TestTable 
 WHERE CONCAT('%', domain, '%') LIKE 'http://bla.ttt.something.com/blabla'

But the problem with that is shown by the sample URL: http://foo.bar/blah/something.com.html... Which will return a false positive for something.com since it's in the URL, but it's not in the domain... So, one method of avoiding that (while still searching for the full domain) would be to do a combination:

$string = 'http://bla.ttt.something.com/blabla';
$domain = parse_url($string, PHP_URL_HOST);
$sql = "SELECT * 
 FROM TestTable 
 WHERE CONCAT('%', domain, '%') LIKE '".mysql_real_escape_string($domain)."'";

Which for this case will generate:

SELECT * 
 FROM TestTable 
 WHERE CONCAT('%', domain, '%') LIKE 'bla.ttt.something.com'