PHP MySQL Search Question

windisch · 05-23-2006, 12:34 PM

I have a PHP script that is monitoring a filename and then searches my MySQL database for info on the file. It works unless there is/are ' or - in the filename, and the search fails. I'm assuming that MySQL uses those symbols to modify the search. Is there a way to tell MySQL to use the entire string as the search? Or do I need to rename my files?

PHP Code:



mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM `musicmetadata` WHERE `filename` LIKE '%$string%'";
$result=mysql_query($query);

$string carries the file name obviously.

example of file names:

Code:

01 - artist - song title

artist song title

artist_song_title

etc

michaelsanford · 05-23-2006, 01:28 PM

Yeah, ' is a reserved symbol and needs to be escaped. I would guess that using mysql_real_escape_string() around the variables would fix that.

windisch · 05-23-2006, 03:30 PM

I'm not sure what I'm doing wrong, but I tried both addslashes() and mysql_real_escape_string() and it doesn't seem to be changing the string. I echo out the variable after the line and before the search, but there are no slashes. What could I be doing wrong?

chrism01 · 05-23-2006, 09:05 PM

Please show us an example or two.

windisch · 05-24-2006, 06:34 AM

Sure thing. Here are my two trys so far:

PHP Code:



function remover($string, $sep1, $sep2) 
{ 
       $string = substr($string, 0, strpos($string,$sep2));     //Finds end position of filename 
       $string = substr(strstr($string, $sep1), 14);            //Finds beginning of filename 
        $string = trim($string);                                //Trims blank space(s) at the beginning and end of string 
        $string = addslashes ($string); 
#echo get_magic_quotes_gpc(); 
#echo "($string)"; 
$username="mythtv"; 
$password="mythtv"; 
$database="mythconverg"; 
#$string = mysql_real_escape_string($string); 
mysql_connect('localhost',$username,$password); 
@mysql_select_db($database) or die( "Unable to select database"); 
$query="SELECT * FROM `musicmetadata` WHERE `filename` LIKE '%$string%'"; 
#       mysql_real_escape_string($string)); 
$result=mysql_query($query); 
 
$num=mysql_numrows($result); 
 
mysql_close();

windisch · 05-25-2006, 07:35 PM

Does anyone have a suggestion on what I'm doing incorrectly to escape out the ' in my file names?

chrism01 · 05-25-2006, 09:03 PM

Actually we'd like to see the input & output strings so we can see what it's doing... so uncomment your 'echo's and copy/paste the results here.

windisch · 05-26-2006, 06:26 AM

I echoed out the data after the addslashes() and mysql_real_escape_string() and It doesn't appear to be doing anything. Here is an example output that just happened:

Code:

(Billy Joel - Don`t Ask Me Why)(Billy Joel - Don`t Ask Me Why)

windisch · 05-29-2006, 04:10 PM

Anyone have an idea on what I'm doing wrong?

smallville · 05-31-2006, 10:06 PM

can you please post the error message....

graemef · 06-01-2006, 06:37 AM

One suggestion. Have you run your SQL statement directly in MySQL? Your PHP line is given as:

PHP Code:



$query="SELECT * FROM `musicmetadata` WHERE `filename` LIKE '%$string%'";

so echo out $query and then send that line directly through MySQL.

I've not used MySQL for a long time but I wonder why you have ticks around musicmetadata and filename.

windisch · 06-01-2006, 09:43 AM

Here is the offical SQL error when I put in the search:

Code:

ERROR: Unclosed quote @ 66
STR: '
SQL: SELECT * FROM `musicmetadata` WHERE `filename` LIKE '%Something's%'

If I add the \ before the ' it does work. So I can't figure out why the addslashes is not working.

windisch · 06-01-2006, 10:14 AM

Ok, I think I fixed it. Addslashes doesn't work but I found a little function that appeared that I could use. It seems to be working so far. Thanks for your help!

PHP Code:



function mysql_addslashes($string) {
   $string= str_replace("'", "\'", $string);
   $string= str_replace("`", "\'", $string);
return $string;
}
$string=mysql_addslashes($string);

graemef · 06-01-2006, 11:32 AM

You may want to look at the mysql_escape_string() function

smallville · 06-02-2006, 12:25 AM

ERROR: Unclosed quote @ 66
STR: '
SQL: SELECT * FROM `musicmetadata` WHERE `filename` LIKE '%Something's%'

I'm not sure how you used the addslashes() function but it should work. But if you can;t make it work, try this:

SELECT * FROM `employee` WHERE fname LIKE replace('%".$string."%','\'','');

I used mysql replace() function