select statement with muliple choices

[OwenTheSamoan]

I'm using php with mysql. I have a checkbox searchform. There are about ten different catagories. Each catagory has at least five different selections. Just for illustration purposes this is the select form I am using. I cut it down to two catagories. I have numbered the checkbox selections according the their catagory as you can see in the select statement. When I query the db with only one group it works fine, but when I add another group to the select statement I get undesired results.

$query = "SELECT * FROM table WHERE age = '$age1' or '$age2'
or '$age3' or '$age4' and state =
'$state1' or '$state2' or '$state3' or '$state4'
or '$state5' or '$state6' or '$state7'";

It seems that using this select statement the db does a search for the first group (age), then does a seperate search for the second group (state). The problem is that this produces undesired results. Anyone know how I can correct this?

[siwis]

Use parentheses to correctly group your clauses.

$query = "SELECT * FROM table WHERE (age = '$age1' or age = '$age2' or age = '$age3' or age = '$age4') and (state = '$state1' or state = '$state2' or state = '$state3' or state = '$state4' or state = '$state5' or state = '$state6' or state = '$state7')";

This is of course very likely to keel over because if you are saying that your $state etc variables are created from check boxes, you could easily end up with a state = '' which would be disliked by mysql.

Whether your original syntax, but with the parentheses added, would work, I dunno, I've not seen it done that way before.

[cgraz]

There's a better way (in my opinion to do this). Have two arrays for your checkboxes, age and state.

15<input type="checkbox" name="age[]" value="15"><br>
16<input type="checkbox" name="age[]" value="15"><br>
17<input type="checkbox" name="age[]" value="17"><br>

Alabama<input type="checkbox" name="state[]" value="Alabama"><br>
Alaska<input type="checkbox" name="state[]" value="Alaska"><br>
Arizona<input type="checkbox" name="state[]" value="Arizona"><br>

Then in your PHP page, instead of using $age1 or $age2 etc, you use implode to find all the recors where the values have an age of any of the ages selected AND the state is any of the states selected. the IN clause is like saying OR (everything inside parenthesis). So your PHP for the above form would look something like this:

$query = mysql_query("SELECT * FROM your_table WHERE age IN ('" . implode("','", $_POST['age']) . "') AND 
state IN ('" . implode("','", $_POST['state']) . "')") or die(mysql_error());

You can obviously add more checkboxes, but make sure they're all named either age[] or state[]

Cgraz

[OwenTheSamoan]

I agree, using arrays with implode() is a much easier way to go, but when I use a pagination script in conjunction with this there is a problem. The pagination script has a problem with arrays, that' why I'm going about it this way. I apprecciate you getting back to me with a reply. Thanks.