查询数据库中是否存在url

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

假设我有$_POST['url']='http://myexamplesite.com/images/image.jpg';如果这个url存在于数据库中，但是看起来像:http://www.myexamplesite.com/images/imagese.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

这不会总是返回正确的结果。正确的方法是什么?谢谢

2 个解决方案

#1

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

如果需要纯SQL解决方案，可以使用SUBSTRING_INDEX():

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.

这将删除http://和www。从两个字符串中进行比较。

#2

Either

要么

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

1)标准化数据库中的url，如果它们都在您自己的站点上

或

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

2)删除http://、http://和www。从搜索URL如下:

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';

#1