multiple search for multiple words?

[empegger]

Hi there.....
Trying to write a query which will search several mysql-tables for several words...but it just won't work...HELP !

1. I start off with a simple search input field. Let's say we fill in the words; 'Robert goes nuts'.

2. I've got a databse with 4 table-cols ; id, name,details & keywords. I want him to display every row which contains all three words in total. So if the table looks like this;
   id name details keywords
   1 Robert gets sleepy
   2 Goes Nuts Robert
   3 Nuts Robert Lazy
   4 Nuts Robert goes

it should find both 2 and 4.

3. Here's what I came up with:

$split= explode (" ", $search);
$sql = "SELECT * FROM $table WHERE name LIKE '%$split[0]%' OR details LIKE '%$split[0]%' OR keywords LIKE '%$split[0]%'";

for ($k=1; $k<count($split); $k++){
$sql.=" AND name LIKE '%$split[$k]%' OR details LIKE '%$split[$k]%' OR keywords LIKE '%$split[$k]%'";
}

But it won't work...........help ?!

[jeremuck]

three loops:

One goes around each table
one goes around the table's fields
one goes inside the array.

//outside array
foreach($tables as $table)
{
//
Add an OR clause (

  foreach($fields as $field)
  {
  // AND this field equals (
    foreach($split as $term)
    {
      this or this or this or this...
    }
// )
  }
//  )

}

hopefully that will get you started.

[empegger]

three loops....that's nice....my head was allready spinning. 😉
anyway...can;t seem to get it to work wth this information.

Is there anyone who can edit just the query and get it working..?

thnx.

empegger

[milanisti]

try this code.

$split= explode (" ", $search);
// this your sql variable
$sql = "SELECT * FROM test WHERE ";
$fields = mysql_list_fields("$your_database", "$your_table", $your_connection);
$columns = mysql_num_fields($fields);
for ($i = 1; $i < $columns; $i++):
$sql.="(";
$field_name=mysql_field_name($fields, $i);

for ($k=0; $k<count($split); $k++):
$sql.="$field_name = '$split[$k]'";
if ($k<>(count($split)-1)):
$sql.=" or ";
endif;
endfor;
if ($i<>($columns-1)):
$sql.=" ) and ";
else:
$sql.=" )";
endif;

endfor;
$query=mysql_query($sql,$your_connection);
while ($hasil=mysql_fetch_array($query)):
print_r($hasil);
endwhile;

or change your sql into :

SELECT * FROM $table WHERE (name = 'Robert' or name = 'goes' or name = 'nuts' ) and (details = 'Robert' or details = 'goes' or details = 'nuts' ) and (keywords = 'Robert' or keywords = 'goes' or keywords = 'nuts' ).

CMIIW

[empegger]

thnx !