*need help* multiple table select based on optional variables

[lhale]

Hello everyone.

I have a form with 5 optional inputs, you can select anywhere from 1 of them up to all 5 of them. The more you select the narrower and more specific the SQL select statement will become.

The data will be pulled from 1 to 3 tables depending on what inputs are returned from the form.

I am not sure how to go about this. I have tried a few ways but I feel I am walking blind. Can I build up the SQL statement as I go (based on what input fields are returned)? Can I run multiple SELECT statements and post process the data somehow?

EXAMPLE:

Table (People)
id
name

Table (Fruit)
id
f_people_id
name

Table (Pet)
id
f_people_id
name

If multiple select box 1 returns ('Apple','Orange')
and
If multiple select box 2 returns ('Cat','Dog')
and
If multiple select box 3 does not return anything.

I need to find all People who have all items ('Apple','Orange','Cat','Dog')

However, the multiple select inputs are optional, you only need at least 1 but can have 2 or all 3 returning data.

How should I proceed?

[Wynder]

I'll try to do this as simply as I can..

Assuming you're using <selects>, have them setup like this:

<!--From Table 1-->
<select name=Table1-A>
   <option></option>
   <option value=Apples>Apples</option>
   <option value=Oranges>Oranges</option>
</select>

<select name=Table1-B>
   <option></option>
   <option value=Nuts>Nuts</option>
   <option value=Cake>Cake</option>
</select>

<!--From Table 2-->
<select name=Table2-A>
   <option></option>
   <option value=Pears>Pears</option>
   <option value=Grapes>Grapes</option>
</select>

<select name=Table2-A>
   <option></option>
   <option value=Candy>Candy</option>
   <option value=Meat>Meat</option>
</select>

The blank <option> has no value, so when you submit your form, you can do:

$query = "SELECT * FROM "
$check = 0;

if( $_POST['Table1-A'] != "" || $_POST['Table1-B'] != ""  )
{
   $query .= "Table1";
   $check = 1; //So we know something exists already.
}
if( $_POST['Table2-A'] != "" || $_POST['Table2-B'] != ""  )
{
   if( $check )
   {
      $query .= ", Table2";
   }
   else
   {
      $query .= "Table2";
   }
}

$query .= " WHERE ";
$check = 0;

if( $_POST['Table1-A'] != "" )
{
   $query .= "Field = '" . $_POST['Table1-A'] . "'";
   $check = 1;   

}
....

And continue on from there, using the $check variable to see if you need to add a leading comma to the query because there's something already there.

It's tedious, but that's how you 'build' a query from a search page. They're a pain in the ass.

[lhale]

Thanks Wynder,

I'll try it.