[SOLVED] Help with MySQL search form

[sublimefn87]

I have this code written for my website using PHP and it searches my MySQL database and everything works fine. My only problem is my database can only be searched by one term, and I want to be able to search with multiple terms.

<?php
/**************************************************************************************
* Main Search Page - search.php
* Author: Your Name <[email protected]>
* This file searches the database
**************************************************************************************/

//Get variables from config.php to connect to mysql server
require 'config.php';

// connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
//select the database
mysql_select_db($dbname) or die('Cannot select database');

//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 drinkspecials " .
"WHERE Night LIKE '%".$keywords[$i]."%'".
" ORDER BY Night";
}

//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 "<br /><form name=\"searchform\" method=\"GET\" action=\"search.php\">";
echo "<Select name=\"search\"><Option value=\"Monday\">Monday</option>
						  <Option value=\"Tuesday\">Tuesday</option>
						  <Option value=\"Wednesday\">Wednesday</option>
						  <Option value=\"Thursday\">Thursday</option>
						  <Option value=\"Friday\">Friday</option>
						  <Option value=\"Saturday\">Saturday</option>
						  <Option value=\"Sunday\">Sunday</option></select>";					  
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 {


//Colors for alternation of row color on results table
$color1 = "#d5d5d5";
$color2 = "#e5e5e5";
//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 width=400 bgcolor=".$row_color.">";
echo "<tr>";
echo "<td>".$row['Special']."<br>";
echo "<b>".$row['Name']."</b><br>";
echo "".$row['Address']."<br>";
echo "".$row['City'].", ";
echo "".$row['State']." ";
echo "".$row['Zip']."<br>";
echo "".$row['Phone']."</td>";
echo "</tr>";
echo "</table></center>";
$row_count++;
//end while
}
//end if
}


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

 

Sorry for that absurd amount of code, but it will help explain what I mean. Right now I can only search by Nights of the week, Monday-Sunday, but I want to also searching by a city. So I want the whole database searched by both night and city. How can I do this?

[Ken2k7]

for ($i=0; $i<count($keywords); $i++) {
$query = "SELECT * FROM drinkspecials " .
"WHERE Night LIKE '%".$keywords[$i]."%'".
" ORDER BY Night";
}

 

That part of the code seems odd. Why would you keep replacing the $query variable with a new query string? If you're going to do that, you might as well replace that whole block of code with this:

 

$query = "SELECT * FROM drinkspecials " .
"WHERE Night LIKE '%".$keywords[count($keywords) - 1]."%'".
" ORDER BY Night";

[sublimefn87]

Ok, I changed that Ken, but I am still stuck with my same problem.

 

What I have is a table with multiple columns and rows, ranging like this

 

Night                  City                        Name

Monday            Winston-Salem        Burke Street

Monday            Boone                    King Street Pub

Tuesday          Winston-Salem        Burke Street

Tuesday          Boone                    King Street Pub

 

 

And it goes on from there with multiple entries. Lets say I want users to be able to search my database returning ONLY Monday night in Boone. Then, they can query for Tuesday night in Boone and so on. Right now when I run a search, Every Monday shows up whether or not I have selected Winston-Salem or Boone only results. Please help if you can!

[Ken2k7]

Well you are only matching Night in your query. Perhaps you should include a LIKE or something for City?

[sublimefn87]

Well I have tried adding in another LIKE for City and when I run my search I get no results at all.

[sublimefn87]

I have this in my code for my query where $keywords is for nights and $search2 is for city.

 

$query = "SELECT * FROM drinkspecials WHERE Night LIKE '$keywords' AND City LIKE '$search2' LIMIT 0, 30 ";

 

But this query returns no results even though it should because I have information available to be found.

[sublimefn87]

Can anybody help me?

[Hinty]

try using..

 

$query = "SELECT * FROM drinkspecials WHERE Night LIKE '%$keywords%' AND City LIKE '%$search2%' LIMIT 0, 30 ";