searching with php for multiple keywords...

[commun1]

I have a textfield where I enter keywords. Once this form is sent through php it queries the database to look for the keywords that exist in one particular field.

e.g.

Let's say I'm looking for the word "widgets".

$keyword = "widgets";
$mysql_data = mysql_query("SELECT text FROM test WHERE text LIKE '%$keyword%'");

It works fine when I search for one single keyword but let's assume I have some words within the "text"-field.
Something like this "this widgets are blue" and the keyword is "blue widgets".

When I query blue widgets it gives me no result but instead it should get me the "this widgets are blue" query since both words (blue, widgets) appear within the same field in my database...

How does my "SELECT" have to be written?

 

[Bromrrrrr]

You need to write your query dynamically. When you search for '%blue widgets%' you search for any record where the exact string 'blue widgets' apears, not for the individual words.

$search = 'blue widgets';
// spit the words into an array
$keywords = split(' ', $search);

$sql = 'SELECT text FROM test';

$clause = ' WHERE (' ;

if (count($keywords)) {
   foreach($keywords as $keyword) {
      $sql .= "$clause text LIKE '%$keyword%' ";
      $clause = 'OR';
   }
   $sql .= ")";
}

$mysql_data = mysql_query($sql);

Please note that you'd probably get better input data using one of the preg_ functions rather than split, since you could strip out unwanted characters (commas, semicolons and such) using regular expressions but since I don't use regular expressions very often I couldn't give an example off the top of my head.

 

[commun1]

it works flawlessly!

thank you so much =)