Multi Word Search Problem

JayVee · May 1, 2008

Having some trouble with this search code. I want a simple multi word search form but when I run this code only the last word of any search string returns. i.e. search for 'dog cat' - cat returns. Alternately search for 'cat dog' - dog returns. I'm pretty new to this so any help would be appreciated.

Here's the code

//search variable = data in search box or url
if(isset($_GET['search']))
{
$search = $_GET['search'];
}

//trim whitespace from variable
$search = trim($search);
$search = preg_replace('/\s+/', ' ', $search);

//seperate multiple keywords into array space delimited
$keywords = explode(" ", $search);

//Clean empty arrays so they don't get every row as result
$keywords = array_diff($keywords, array(""));

//Set the MySQL query
if ($search == NULL or $search == '%'){
} else {
for ($i=0; $i<count($keywords); $i++) {
$query = "SELECT * FROM CourseTbl " .
"WHERE CourseID LIKE '%".$keywords[$i]."%'".
" OR CourseTitle LIKE '%".$keywords[$i]."%'" .
" OR CourseAims LIKE '%".$keywords[$i]."%'" .
" ORDER BY CourseTitle";
}

//Store the results in a variable or die if query fails
$result = mysql_query($query) or die(mysql_error());
}
if ($search == NULL or $search == '%'){
} else {
//Count the rows retrived
$count = mysql_num_rows($result);
}

echo "<html>";
echo "<head>";
echo "<title>Your Title Here</title>";
echo "<link rel=\"stylesheet\" type=\"text/css\" href=\"style.css\" />";
echo "</head>";
echo "<body onLoad=\"self.focus();document.searchform.search.focus()\">";
echo "<center>";
echo "<br /><form name=\"searchform\" method=\"GET\" action=\"searchfull.php\">";
echo "<input type=\"text\" name=\"search\" size=\"20\" TABINDEX=\"1\" />";
echo " <input type=\"submit\" value=\"search\" />";
echo "</form>";
//If search variable is null do nothing, else print it.
if ($search == NULL) {
} else {
echo "You searched for <b><FONT COLOR=\"blue\">";
foreach($keywords as $value) {
   print "$value ";
}
echo "</font></b>";
}
echo "<p> </p><br />";
echo "</center>";

//If users doesn't enter anything into search box tell them to.
if ($search == NULL){
echo "<center><b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br /></center>";
} elseif ($search == '%'){
echo "<center><b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br /></center>";
//If no results are returned print it
} elseif ($count <= 0){
echo "<center><b><FONT COLOR=\"red\">Your query returned no results from the database.</font></b><br /></center>";
//ELSE print the data in a table
} else {
//Table header
echo "<center><table id=\"search\" bgcolor=\"#AAAAAA\">";
echo "<tr>";
echo "<td><b>COLUMN 1:</b></td>";
echo "<td><b>COLUMN 2:</b></td>";
echo "<td><b>COLUMN 3:</b></td>";
echo "<td><b>COLUMN 4:</b></td>";
echo "<td><b>COLUMN 5:</b></td>";
echo "<tr>";
echo "</table></center>";

//Colors for alternation of row color on results table
$color1 = "#face9e";
$color2 = "#bdebf2";
//While there are rows, print it.
while($row = mysql_fetch_array($result))
{
//Row color alternates for each row
$row_color = ($row_count % 2) ? $color1 : $color2;
//table background color = row_color variable
echo "<center><table bgcolor=".$row_color.">";
echo "<tr>";
echo "<td>".$row['CourseID']."</td>";
echo "<td>".$row['CourseTitle']."</td>";
echo "<td>".$row['CourseAims']."</td>";
echo "<td>".$row['CourseDuration']."</td>";
echo "<td>".$row['EntryRequirements']."</td>";
echo "</tr>";
echo "</table></center>";
$row_count++;
//end while
}
//end if
}

echo "</body>";
echo "</html>";
if ($search == NULL or $search == '%') {
} else {
//clear memory
mysql_free_result($result);
}
?>

sasa · May 1, 2008

//Set the MySQL query
if ($search == NULL or $search == '%'){
} else {
// generate WHERE part
foreach ($keywords as $word){
	$where[] = "CourseID LIKE '%$word%' OR CourseTitle LIKE '%$word%' OR CourseAims LIKE '%$word%'";
}
if (count($where)) $where = 'WHERE ('.implode(') OR (', $where).')';
$query = "SELECT * FROM CourseTbl " .$where." ORDER BY CourseTitle";
}

Rohan Shenoy · May 1, 2008

Instead of searching for the "phrase" search for the inividual keywords and then sort them by relevance using the MATCH() function(of mysql).

See the entry "Posted by Brad Satoris on December 13 2004 9:14pm" on http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

Sign In

Multi Word Search Problem

Recommended Posts

JayVee

Link to comment

Share on other sites

sasa

Link to comment

Share on other sites

Rohan Shenoy

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information