[SOLVED] Search using LIKE in mysql

[Julian]

Hello,  I'm 

 

I'm dealing with a simple tags search on my website.  Each tag is stored separated by a comma in the same column. (pool, ball, dinner)

 

I'm working with this script that actually do the trick, but I'm facing a MySQL error:

 

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tags LIKE '%test%' ORDER BY fecha ASC' at line 1"

 

Here's the code:

$q = $row_noticias['tags'];

//bit of sanitizing
$qtrim = strtolower($q);
$qtrim = stripslashes($q);
$qtrim = strip_tags($q);
$qtrim = trim($q);

//split values by whitespace into an array, then for each array member stick them together with "OR columnname LIKE '%$arrayvalue%' "
$qtrim = explode(',', $q);
foreach($qtrim as $value)
   {
      $qtrim = $qtrim."OR tags LIKE '%".$value."%' ";
   }
//we now have a string which has the following value:
//OR fullname LIKE '%".$value1."%' OR fullname LIKE '%".$value2."%' 

//we now need to remove the first OR, so the string looks like this:
//fullname LIKE '%".$value1."%' OR fullname LIKE '%".$value2."%' 
$qtrim = substr($qtrim,2);

//then we use the string in a mysql query
//$query  = "SELECT * FROM products WHERE ".$qtrim;

   $query_relacionados = "SELECT * FROM noticias WHERE " .$qtrim. " ORDER BY fecha ASC";
   $relacionados = mysql_query($query_relacionados, $rcpc) or die(mysql_error());

 

Thanks for your help!!!!

[kickstart]

Hi

 

You seem to be doing a foreach around an array, but then overwriting the array with a string in the foreach.

 

Try this:-

 

//split values by whitespace into an array, then for each array member stick them together with "OR columnname LIKE '%$arrayvalue%' "

$qtrim = explode(',', $q);

$likeString = "";

foreach($qtrim as $value)

  {

      $likeString .= "OR tags LIKE '%".$value."%' ";

  }

//we now have a string which has the following value:

//OR fullname LIKE '%".$value1."%' OR fullname LIKE '%".$value2."%'

 

//we now need to remove the first OR, so the string looks like this:

//fullname LIKE '%".$value1."%' OR fullname LIKE '%".$value2."%'

$likeString = substr($likeString,2);

 

//then we use the string in a mysql query

//$query  = "SELECT * FROM products WHERE ".$qtrim;

 

  $query_relacionados = "SELECT * FROM noticias WHERE $likeString ORDER BY fecha ASC";

  $relacionados = mysql_query($query_relacionados, $rcpc) or die(mysql_error());

 

All the best

 

Keith

[Julian]

Thanks Keith

 

  I got rid the error message, working now.  Test the script but I found that the results are not as expected.

 

  Practical example:  On record 1, I have tags: "test, prueba, example".  On record 2, I have tags: "test"

 

  When display record 2, results are as expected.  query shows anything related to "test".  On the other hand record 1 only show itself.

 

  Thanks again...

[Julian]

Never mind....  I have to trim white space!!!  It works now.  Thank you very much!!!

[kickstart]

Hi

 

Might be the spaces after the commas. Might be worth trimming $value as you use it in $likeString.

 

EDIT - 

 

All the best

 

Keith