Need help with multiple search!

[mdalene]

Hello, im working on a food recepie database with the use of flash, php and MySql.
Inside the application there is going to be a search function for ingredients.
The ingredients are placed in different fields ingr01, ingr02, ingr03 and so on...
I want my php code to perform a simpe multiple search in ingr01, ingr02...
It works fine (for one word) as long as I only search in one field, but as soon as I add one more field the trouble starts. if I use SELECT * FROM feedme2 WHERE ingr01 OR ingr02 LIKE '%searchword% the php only displays results from the ingr02. Am I off track here or is it just small adjustments?

Here is the code:

<?php
define ('DB_USER', 'XXXX');
define ('DB_HOST', 'XXXX');
define ('DB_PASSWORD', 'XXXX');
define ('DB_NAME', 'XXXX');
$ingr = $_POST[ "search" ];
$d_base = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD);
mysql_select_db (DB_NAME);
if (mysql_select_db ("database", $d_base) != FALSE) {
echo 'well done you have connected to the database called '.DB_NAME . '</br>';
} else {
echo "oh dear";
}
$qr = mysql_query ("SELECT * FROM thetable WHERE ingr01 OR ingr02 LIKE '%$ingr%'", $d_base);
$rowNum = mysql_num_rows ($qr);
$rString = "&n=".$rowNum;
echo "$rString";
for ($i=0; $i<$rowNum; $i++) {
$row = mysql_fetch_array ($qr);
$name = $row['name'];
$returnName .= "&thisName".$i."=".$row['name'];
$returnPrice .= "&thisPrice".$i."=".$row['price'];
$returnID .= "&thisID".$i."=".$row['id'];
$returnRec .= "&thisRec".$i."=".$row['rec'];
$returnSkill .= "&thisSkill".$i."=".$row['skill'];
$returnTime .= "&thisTime".$i."=".$row['time'];
$returnCat .= "&thisCat".$i."=".$row['cat'];
$returnIngr01 .= "&thisIngr01".$i."=".$row['ingr01'];
$returnIngr02 .= "&thisIngr02".$i."=".$row['ingr02'];
}
echo $returnName;
echo $returnPrice;
echo $returnID;
echo $returnRec;
echo $returnSkill;
echo $returnTime;
echo $returnCat;
echo $returnIngr01;
echo $returnIngr02;
?>

The last bit with $i is to give variables about rows to flash for listing. Might that be the problem??

[mdalene]

problem solved!

"SELECT * FROM tablename WHERE ingr01 LIKE '%$searchword%' OR ingr02 LIKE '%$searchword%'"

But if sending more than one search word it does not find anything...

What is the best solution to perform a search with several words?

[theeper]

You could explode you searchword by space character, then loop through the resultant array and concatenate the 'OR' conditions onto your SQL query string. It could get unwieldy and hard on your DB if you have too many fields, and/or allow too many seach words.

Something like this.

$sql = "SELECT * FROM tablename WHERE ingr01 LIKE '%" . $searchword . "%' OR ingr02 LIKE '%" . $searchword . "%'";

$word_array = explode(" ",$searchword);

foreach ($word_array as $word) {
$sql .= " OR ingr01 LIKE '%" . $word . "%' OR ingr02 LIKE '%" . $word . "%'";
}