Mysql query to check if url exists in database

Question

lets say I have $_POST['url']='http://myexamplesite.com/images/image.jpg'; how can I get correct result if this url exists in the database but looks like: http://www.myexamplesite.com/images/image.jpg

if I do:

$q='select id from products where url LIKE "%'.$_POST['url'].'%"';

This will not return always correct results. What is the correct way? Thanks

probably you are looking for that: http://stackoverflow.com/questions/9364242/how-to-remove-http-www-and-slash-from-url-in-php — mitkosoft, Jun 29 '16 at 07:56
It is clear how to compare both in php, but how to do this in mysql query? — Europeuser, Jun 29 '16 at 07:58

score 2 · Accepted Answer · answered Jun 29 '16 at 08:08

2

If you need pure SQL solution, you can use SUBSTRING_INDEX() for that:

SELECT * FROM products WHERE
SUBSTRING_INDEX(SUBSTRING_INDEX(url, 'http://', -1), 'www.',-1) = 
SUBSTRING_INDEX(SUBSTRING_INDEX('".$_POST['url']."', 'http://', -1), 'www.',-1)

This will remove http:// and www. from both strings and will compare the rest.

answered Jun 29 '16 at 08:08

mitkosoft

5,262
1
13
31

1

Well done man, this does exactly what is suppose to do, thanks ! – Europeuser Jun 29 '16 at 08:14

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

Either

1) Standardise the URLs within the database if they are all on your own site

or

2) Remove the http://, http://, and www. from search URL like so:

function pure_url($url) {
   if ( substr($url, 0, 7) == 'http://' ) {
      $url = substr($url, 7);
   }
   if ( substr($url, 0, 8) == 'https://' ) {
      $url = substr($url, 8);
   }
   if ( substr($url, 0, 6) == 'ftp://') {
      $url = substr($url, 6);
   }
   if ( substr($url, 0, 4) == 'www.') {
      $url = substr($url, 4);
   }
   return $url;
}

$url = pure_url('http://www.myexamplesite.com/images/image.jpg');
// $url = 'myexamplesite.com/images/image.jpg';

edited May 23 '17 at 12:22

Community

1
1

answered Jun 29 '16 at 07:58

Jamie Bicknell

2,306
17
35

What about `ftp` protocol ? – tektiv Jun 29 '16 at 08:00
if ( substr($url, 0, 4) == 'ftp.') { $url = substr($url, 4); } – Jun 29 '16 at 08:00
Jamie, thanks, but this aint solve the problem because first url-s are for different sites each time and second $_POST may look 'http://www.myexamplesite.com/images/image.jpg' but in database to be 'http://myexamplesite.com/images/image.jpg' and opposite – Europeuser Jun 29 '16 at 08:01
@Europeuser You would apply the function to the posted URL, which removes the protocols then when you search the database it will return both cases. – Jamie Bicknell Jun 29 '16 at 08:04
@Europeuser Correct, and your SQL using the `%` wildcard at the start and end of the search string, so both cases will be returned. – Jamie Bicknell Jun 29 '16 at 08:08

Mysql query to check if url exists in database

2 Answers2