Displaying all records if searched.

Anon

I have a simple form which consists fo two list/menu items to select from. I have figured out how to query the results when a value is selected, but am having trouble with the code if the user selects the "Display All" value. Any thoughts?

<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

    <select name="cuisine">
      <option selected>Display All</option>
      <option value="Mexican">Mexican</option>
      <option value="American">American</option>
      <option value="Chinese">Chinese</option>
    </select>
 </td>
<td>&nbsp;</td>

</tr>
<tr>
<td>
<select name="location">
<option selected>Display All</option>
<option value="Austin">Austin</option>
<option value="Lakeway">Lakeway</option>
</select>
</td>
<td>
<input type="submit" name="Submit" value="Submit">
</td>
</tr>
</table> </form>
</body>

Anon

Attached is the code I am using for the above questions.

$query1 = "SELECT id, company, address, city, state, zip, email, website, map, cuisine1, cuisine2, type, phone FROM restaurants WHERE (cuisine1 LIKE '$cuisine%' OR cuisine2 LIKE '$cuisine%' OR type LIKE '$cuisine%') AND (location LIKE '$location%' OR city LIKE '$location%') limit $offset,$limit";

$query_results = mysql_query("$query1");

echo "<table width=100% border=0 cellspacing=0 cellpadding=3>\n";

echo "<tr>\n";

if ($row = mysql_fetch_array($result)) {
while($row = mysql_fetch_object($query_results))

				{
				    printf("<td class=reg><span class=busname><A HREF=\"details.php?id=$row->id\">$row->company</A></span> -- (%s%s)</td></tr><tr><td class=reg>%s %s, %s %s %s</td></tr><tr><td class=listing><a href=\"$row->map\" target=\"_blank\">Map</a> | <a href=\"mailto:$row->email\">Email</A> | <a href=\"http://$row->website\" target=\"_blank\">Website</A></td></tr><tr><td><hr></td></tr>\n", $row->cuisine1, $row->cuisine2, $row->address, $row->city, $row->state, $row->zip, $row->phone);
				}

Anon

you have to put together your sql statement in various steps ...

$query1 = "SELECT id, company, address, city, state, zip, email, website, map, cuisine1, cuisine2, type, phone FROM restaurants ";

if($cuisine != "Display All")
{
$wherecuisine = "(cuisine1 LIKE '$cuisine%' OR cuisine2 LIKE '$cuisine%' OR type LIKE '$cuisine%') ";
}

if($location != "Display All")
{
$wherelocation = "(location LIKE '$location%' OR city LIKE '$location%') ";
}

if($wherecuisine || $wherelocation)
$query1 .= "where ";

if($wherecuisine)
$query1 .= $wherecuisine;

if($wherecuisine && $wherelocation)
$query1 .= "and ".$wherelocation;

if(!$wherecuisine && $wherelocation)
$query1 .= $wherelocation;

$query1 .= "limit $offset,$limit";

note this is very little sophisticated programming. I intend to show the principle.

bastien

way to complex matto,

how about

if ((!$cuisine)&&(!$location)) {
$sql = "select from restaurants";
}elseif ((isset($cuisine)&&(!$location)){
$sql = "select from restaurants where cuisine=$cuisine";
}elseif ((isset($location)&&(!$cuisine)){
$sql = "select * from restaurants where location=$location";
}

$sql.= "limit $offset,$limit";

hth

bastien

Anon

I am still having trouble. I keep receiving the following error. "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource"

Below is the php code.

$query1 = "SELECT id, company, address, city, state, zip, email, website, map, cuisine1, cuisine2, type, phone FROM restaurants ";

				if($cuisine != "Display All") 
				{ 
				$wherecuisine = "(cuisine1 LIKE '$cuisine%' OR cuisine2 LIKE '$cuisine%' OR type LIKE '$cuisine%') "; 
				} 

				if($location != "Display All") 
				{ 
				$wherelocation = "(location LIKE '$location%' OR city LIKE '$location%') "; 
				} 

				if($wherecuisine || $wherelocation) 
				$query1 .= "where "; 

				if($wherecuisine) 
				$query1 .= $wherecuisine; 

				if($wherecuisine && $wherelocation) 
				$query1 .= "and ".$wherelocation; 

				if(!$wherecuisine && $wherelocation) 
				$query1 .= $wherelocation; 

				$query_results = mysql_db_query("database","$query1"); 

				echo "<table width=100% border=0 cellspacing=0 cellpadding=3>\n";
				echo "<tr>\n"; 

				while($row = mysql_fetch_object($query_results)) 

				{
				    	printf("<td class=reg><span class=busname><A HREF=\"details.php?id=$row->id\">$row->company</A></span> -- (%s%s)</td></tr><tr><td class=reg>%s %s, %s %s %s</td></tr><tr><td class=listing><a href=\"$row->map\" target=\"_blank\">Map</a> | <a href=\"mailto:$row->email\">Email</A> | <a href=\"http://$row->website\" target=\"_blank\">Website</A></td></tr><tr><td><hr></td></tr>\n",
						$row->cuisine1, $row->cuisine2, $row->address, $row->city, $row->state, $row->zip, $row->phone);
				}

				echo "</table>\n";

bastien

try mysql_fetch_array($query_results)

hth
bastien