Well, the first step for a robust solution would be to extract the domain from the string (using parse_url 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'
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'
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'
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'