[SOLVED] Problems using URLs in queries

[hellonoko]

My code below compares a link to a DB full of links.

If the link is already in the DB it display the appropriate response.

 

As it stands both links (fisrt two lines) are in the DB.

If test is used my code works fine. However when I use the real url I get no output. No errors. Is there something I need to be doing when I am handling URLS in and outside of my DB?

 

Any ideas?

 

//$last_cralwed_link = "this.bigstereo.net/wp-content/uploads/2009/03/tomorrow-wow-remix.mp3";
$last_crawled_link = "test";

$query = mysql_query("SELECT * FROM `primarylinks` WHERE `link` = '$last_crawled_link' LIMIT 1") or die(mysql_error());

$rows = mysql_num_rows($query) or die(mysql_error());
//
if ( $rows == 0)
	{
	echo ' no such link';
}
else
{
	echo ' link in DB!';
}

[Yesideez]

What type of web crawler is this and what are the links being used for?

[hellonoko]

Well its crawling music blogs. The links will eventually be used to copy mp3s.

 

I worked through my code a little deeper and found that at this line:

 

echo $last_crawled_link = mysql_real_escape_string($last_crawled_link) or die(mysql_error());

 

The variable just goes away. That is mysql_real_escape_string turns it from a url into empty.

 

Any ideas?

[redarrow]

<?php
//$last_cralwed_link = "this.bigstereo.net/wp-content/uploads/2009/03/tomorrow-wow-remix.mp3";
   $last_crawled_link = "test";
   
   $query = mysql_query("SELECT * FROM `primarylinks` WHERE `link` LIKE = '%$last_crawled_link%' LIMIT 1") or die(mysql_error());
   
   $rows = mysql_num_rows($query) or die(mysql_error());
   //
   if ( $rows == 0)
    {
      echo ' no such link';
   }
   else
   {
      echo ' link in DB!';
   }

[hellonoko]

Well...... That returns a 'link in db!'

 

And thats good. But above where I echo $last_crawled_link for the hell of it. It is blank.

 

So I dunno how its working if the variable is empty... What do % do around a variable in a SQL statement?

[Yesideez]

When used with LIKE the % just means "any data"

 

LIKE '%DATA'

 

Matches anything ending in data

 

A bit like *DATA when matching filenames

[hellonoko]

I see but don't I want to be a bit more exact since urls could be very similar but different?

 

Still not sure what is happening as the below code returns neither in db or not in db.

 

//echo $last_cralwed_link = "this.bigstereo.net/wp-content/uploads/2009/03/tomorrow-wow-remix.mp3";
echo $last_crawled_link = "thisisnotinthedb";

$query = mysql_query("SELECT * FROM `primarylinks` WHERE `link` LIKE '%$last_crawled_link%' LIMIT 1") or die(mysql_error());

$rows = mysql_num_rows($query) or die(mysql_error());
//
if ( $rows == 0)
	{
	echo ' no such link';
}
else
{
	echo ' link in DB!';
}

 

 

[thebadbad]

Has it got something to do with the misspelled $last_cralwed_link in the commented out code?

[hellonoko]

Might have been. I rewrote it and got it working... sorta.

[thebadbad]

And I agree that you shouldn't be using LIKE, but do an exact comparison instead. Just be sure the links in the DB match up perfectly, e.g. check that it doesn't have http:// in front when you don't expect it etc.

[Yesideez]

One thing you can do is strip out all the "normal & expected" stuff like "http://" and "www" at the start.