[SOLVED] Search form with blank fields - how do I get it to return results?

[waitingtoderail]

Please excuse me if this is a completely obvious answer, I'm a complete php newbie in a database management class. I've been searching message boards and going through Peachpit Press manuals all day and can't make heads or tails of how to do it.

 

Here's my situation:

 

I'm trying to create a search form with four variables that will return results no matter how many of the fields are filled in. For example, a state field and a city field. If the searcher only fills in state, I want it to come back with all the cities listed in the database for that state.

 

So the search form and php form I created work, but only if I fill in all fields. Here's the code for the php form, can anyone help me out?

 

Baby steps...

 

$sql="select farm_name, farm_address, farm_city, farm_state, farm_zip, 

farm_description from farm_farm, farm_type, farm_product, farm_farmproduct 

where farm_product.product_id=farm_farmproduct.product_id and 

farm_farm.farm_id=farm_farmproduct.farm_id and 

farm_farm.type_id=farm_type.type_id and (farm_city like \"$_POST[city]\") and 

(farm_state like \"$_POST[state]\") and (farm_farm.type_id like 

\"$_POST[farmtype]\") and (farm_product.product_name like 

\"$_POST[product]\")" ;


$result=mysql_query($sql);

if($result==0)
echo ("<p>Error".mysql_errno()." is ".mysql_error()."</p>");

else
{
   echo("<table border=\"5\">");
   for($i=0;$i<mysql_num_rows($result);$i++)
   {
      echo("<tr>");
      $row_array=mysql_fetch_row($result);
      for($j=0;$j<mysql_num_fields($result);$j++)
        {
         echo("<td>".$row_array[$j]."</td>");
         }
       echo("</tr>");
    }
echo("</table>");
}
?>

[.josh]

... (column like 'somethin') OR (column like 'something') OR etc...

[waitingtoderail]

So just change all the ands to ors?

[.josh]

not all of them...only the ones in-between your like statements.

[waitingtoderail]

not all of them...only the ones in-between your like statements.

 

Well, that worked, but it is returning each result into the table about 75 times - I get a huge table with repeating records.

[.josh]

$sql="select farm_name, farm_address, farm_city, farm_state, farm_zip,

farm_description from farm_farm, farm_type, farm_product, farm_farmproduct

where farm_product.product_id=farm_farmproduct.product_id and

farm_farm.farm_id=farm_farmproduct.farm_id and

farm_farm.type_id=farm_type.type_id and ((farm_city like \"$_POST[city]\") or

(farm_state like \"$_POST[state]\") or (farm_farm.type_id like

\"$_POST[farmtype]\") or (farm_product.product_name like

\"$_POST[product]\"))" ;

[lynxus]

I may be missunderstanding the problem, But i would do this:

 

I would build the SQL select on the fly based on the submitted info.

 

Ie, only select for stuff that has been submitted.

 

So if i would search for age sex location, but only filled in age location.

 

id only do a select * from foo where age = bar and location = bar;

 

if i filled all in , then my statement would be

select * from foo where age = bar and sex = bar and location = bar;

 

if that made sense?

 

Probably not the best way to do it, but thats how i would

 

[waitingtoderail]

$sql="select farm_name, farm_address, farm_city, farm_state, farm_zip,

farm_description from farm_farm, farm_type, farm_product, farm_farmproduct

where farm_product.product_id=farm_farmproduct.product_id and

farm_farm.farm_id=farm_farmproduct.farm_id and

farm_farm.type_id=farm_type.type_id and ((farm_city like \"$_POST[city]\") or

(farm_state like \"$_POST[state]\") or (farm_farm.type_id like

\"$_POST[farmtype]\") or (farm_product.product_name like

\"$_POST[product]\"))" ;

 

This worked, but if I search on more than one field, it gives me ALL the results for each field search. What I need is, if I search for "RI" in state, and "Goats" in product, it will only return farms that sell goats in Rhode Island. As it stands it returns all farms in RI and all farms that sell goats.

[waitingtoderail]

I may be missunderstanding the problem, But i would do this:

 

I would build the SQL select on the fly based on the submitted info.

 

Ie, only select for stuff that has been submitted.

 

So if i would search for age sex location, but only filled in age location.

 

id only do a select * from foo where age = bar and location = bar;

 

if i filled all in , then my statement would be

select * from foo where age = bar and sex = bar and location = bar;

 

if that made sense?

 

Probably not the best way to do it, but thats how i would

 

This sounds like what I need but is so far over my newbie head that I don't get it. What does the * represent? Foo is the database name?

[.josh]

okay then you are going to have to go back to using AND instead of OR but only querying for the ones selected (what lynxus said) for example:

 

$sql="select farm_name, farm_address, farm_city, farm_state, farm_zip,
farm_description from farm_farm, farm_type, farm_product, farm_farmproduct
where farm_product.product_id=farm_farmproduct.product_id and
farm_farm.farm_id=farm_farmproduct.farm_id and
farm_farm.type_id=farm_type.type_id";

$sql .= (trim($_POST['city']) != "")? " and (farm_city like \"$_POST[city]\")" : "";
$sql .= (trim($_POST['state']) != "")? " and (farm_state like \"$_POST[state]\")" : "";
$sql .= (trim($_POST['farmtype']) != "")? " and (farm_farm.type_id like \"$_POST[farmtype]\")" : ""; 
$sql .= (trim($_POST['product']) != "")? " and (farm_product.product_name like \"$_POST[product]\")" : "";

[waitingtoderail]

okay then you are going to have to go back to using AND instead of OR but only querying for the ones selected (what lynxus said) for example:

 

$sql="select farm_name, farm_address, farm_city, farm_state, farm_zip,
farm_description from farm_farm, farm_type, farm_product, farm_farmproduct
where farm_product.product_id=farm_farmproduct.product_id and
farm_farm.farm_id=farm_farmproduct.farm_id and
farm_farm.type_id=farm_type.type_id";

$sql .= (trim($_POST['city']) != "")? " and (farm_city like \"$_POST[city]\")" : "";
$sql .= (trim($_POST['state']) != "")? " and (farm_state like \"$_POST[state]\")" : "";
$sql .= (trim($_POST['farmtype']) != "")? " and (farm_farm.type_id like \"$_POST[farmtype]\")" : ""; 
$sql .= (trim($_POST['product']) != "")? " and (farm_product.product_name like \"$_POST[product]\")" : "";

 

Thanks so much for your help here, this worked, but is again giving me the same results multiple times. It's strange though because it's not giving me nearly as many repetitions and not all of them repeat.

[.josh]

maybe you have multiple rows with the same data? You are pulling stuff from a lot of diff tables...same column/value criteria match up for more than 1 table?

[waitingtoderail]

maybe you have multiple rows with the same data? You are pulling stuff from a lot of diff tables...same column/value criteria match up for more than 1 table?

 

No, the data is clean. Could it be due to a linking table? The farm_farmproduct is one.

[waitingtoderail]

OK, it's definitely the linking table. The results are showing multiple times because each time the linking table comes up with a product on a particular farm, it lists the whole thing again. Is there a way to stop this? I'll start poring over the books again....

[.josh]

hmm i think you might want to look into mysql's distinct()

[waitingtoderail]

hmm i think you might want to look into mysql's distinct()

 

You, my friend, have saved my life. Thanks so much!