Query Question

tyhoerr

I just started using mysql and php to code a "Plant Database". I've got the table made and a basic search code written up to recall specific entries using three search options, but I want to expand on it a little bit more. Here's the PHP I'm currently using:

<?php
mysql_connect("localhost","root","******"); 
mysql_select_db("Plant_Database"); 

$search=$_POST["search"];
$search2=$_POST["search2"];
$search3=$_POST["search3"];

$result = mysql_query("SELECT * FROM Master_Table 
WHERE 	Common_Name LIKE '%$search%' AND Light_Conditions LIKE '%$search2%' AND Flower_Color LIKE '%$search3%'|| 
	Botanical_Name LIKE '%$search%' AND Light_Conditions LIKE '%$search2%' AND Flower_Color LIKE '%$search3%'");

while($r=mysql_fetch_array($result))
{	
   $Botanical_Name=$r["Botanical_Name"];
   $Common_Name=$r["Common_Name"];
   $Light_Conditions=$r["Light_Conditions"];
   $Flower_Color=$r["Flower_Color"];

   echo "<b>Common Name:</b>&nbsp;$Common_Name <br> <b>Botanical Name:</b>&nbsp;$Botanical_Name <br> <b>Light Conditions:</b>&nbsp;$Light_Conditions <br><b>Flower Color:</b>&nbsp;$Flower_Color <br><br>";
}

$anymatches=mysql_num_rows($result);
if ($anymatches == 0)
{
echo "No Matches";
} 
?>

I'm going to have 7 drop-down menus all searching different parts of the table, and one general key-word search. Though expanding on the WHERE clause I'm using could work, it would be extremely inconvenient and ridiculously long (I'd have to provide an Or operator for every possible combination...madness).I'm assuming theres a better way to code this... hopefully.

The other problem I'm having (which might be corrected with different code) is that when I only use one of the search fields (and leave the other two blank), it still excludes any entries that are missing information in either of those two field (NULL). It should do this when those fields are being used, but when it's left blank, I assume the person searching doesn't want to use that search option and the results should include entries that don't have info for this field with the others. How can I instruct the page to ignore a searchquery if the value coming from the form's field is blank?

Any help, or direction on either part (specifically the first) would be appreciated! If you need clarification, just ask.

peytonrm

I think the best way to do what you are wanting is to programmatically build up the where clause based on what is being requested. So maybe something like this:

if($_POST['search1'] !== null){
 $where .= " AND Common_Name LIKE '%$search%'";
}

if($_POST['search2'] !== null){
 $where .= " AND Light_Conditions LIKE '%$search2%'";
}

... and so on

This approach of checking for nulls and only adding criteria if they are specified fulfills both your goals I think. As a side note, your code is susceptible to SQL injection attacks. You should call mysql_real_escape_string on all input coming from users before giving it to a query.

tyhoerr

Thanks for the reply! A few questions though. Looks like this will solve the blank field problem, but I'm not sure about providing for multiple search options, especially if I want one of the options to search more than one column (in which case I'd have to use an or statement, which would require all other search options to follow after both sides of the or statement, which the code wouldn't do). In any case, using just what you've provided, I haven't had much luck. I'm probably leaving something really simple out or some silly mistake! I'm getting this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/search.php on line 25

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/search.php on line 35

And here's my code with your code snippet added in:

<?php
mysql_connect("localhost","root","*****"); 
mysql_select_db("Plant_Database"); 

$search=$_POST["search"];
$search2=$_POST["search2"];
$search3=$_POST["search3"];

$result = mysql_query("SELECT * FROM Master_Table WHERE");

if(search !== null){
 $result .= " AND Common_Name LIKE '%$search%'";
}

if(search2 !== null){
 $result .= " AND Light_Conditions LIKE '%$search2%'";
}

if(search3 !== null){
 $result .= " AND Flower_Color LIKE '%$search3%'";
}

while($r=mysql_fetch_array($result))
{	
   $Botanical_Name=$r["Botanical_Name"];
   $Common_Name=$r["Common_Name"];
   $Light_Conditions=$r["Light_Conditions"];
   $Flower_Color=$r["Flower_Color"];

   echo "<b>Common Name:</b>&nbsp;$Common_Name <br> <b>Botanical Name:</b>&nbsp;$Botanical_Name <br> <b>Light Conditions:</b>&nbsp;$Light_Conditions <br><b>Flower Color:</b>&nbsp;$Flower_Color <br><br>";
}

$anymatches=mysql_num_rows($result);
if ($anymatches == 0)
{
echo "No Matches";
} 

?>

Thanks again for the help!