search mysql with checkbox array

[dschuett]

Ok I am very new to the php mysql world so please bare with me.

I have set up a database for an apartment complex with a table containing the following:

create table apartments (apt_num int not null, available varchar(5) not null, beds int not null, baths int not null, pets varchar(5) not null, lease_start varchar(25), lease_end VARCHAR(25), descr varchar(25) not null, primary key (apt_num));

I am now creating a form that contains check boxes for users to select what they want to search for, and return the correct results... for example:

<input type=”checkbox” checked="checked" name=”bdrm[]” value=”Any&#8243;>
<input type=”checkbox” name=”bdrm[]” value=”1&#8243;>
<input type=”checkbox” name=”bdrm[]” value=”2&#8243;>
<input type=”checkbox” name=”bdrm[]” value=”3&#8243;>

<input type=”checkbox” checked="checked" name=”btrm[]” value=”Any&#8243;>
<input type=”checkbox” name=”btrm[]” value=”1&#8243;>
<input type=”checkbox” name=”btrm[]” value=”2&#8243;>

<input type=”checkbox” checked="checked" name=”pets[]” value=”Any&#8243;>
<input type=”checkbox” name=”pets[]” value=”Yes&#8243;>
<input type=”checkbox” name=”pets[]” value=”No&#8243;>

my question is: how would i use php to insert it into a query such as this:

SELECT * FROM apartments WHERE beds='bdrmChecked' AND baths='btrmChecked' AND pets='petsChecked';

Another problem is that if a user checked "Any" it wouldn't return any results because the value "Any" wouldn't exist in the table...

Any help is appreciated!

[Krik]

You need to take this back to the drawing board.

First what happens if the user checks both Yes, No and Any on Pets. While logically that would deduce to mean they want "Any" but why have to write code to figure that out. Use radio buttons, this then also eliminated the need for having a multidimensional array in $_POST

<input type="radio" checked="checked" name="bdrm" value="Any">
<input type="radio" name="bdrm" value="1">
<input type="radio" name="bdrm" value="2">
<input type="radio" name="bdrm" value="3">

Next you need to use PHP variables in the query. You may know this but sometimes the obvious eludes people, so I have to mention it.

mysql_query("SELECT * FROM apartments WHERE beds='$bdrmChecked' AND baths='$btrmChecked' AND pets='$petsChecked'");

I am assuming that "$bdrmChecked", "$btrmChecked", and "$petsChecked" are being defined from the $_POST values earlier in your code.

And to your last question, it is actually the most important as it will total change how you must approach your query.

For example if the user checks they want 1 bedroom your query of

beds='$bdrmChecked'

is fine. But if they check "Any" the query must look for all rows with a value greater than "0". So the query would be

beds > '0'

To do this your "$bdrmChecked" should have the name of the column to check the value that must be check and how the check is evaluated.

if ($_POST['bdrm'] == "Any") {
    $bdrmChecked = "beds > '0'";
}
else {
    $bdrmChecked = "beds='". $_POST['bdrm'] . "'";
}

This assumes you are using radio buttons otherwise there is a lot of other code needed to figure out what "$bdrmChecked" must be.

You then do your query like so

mysql_query("SELECT * FROM apartments WHERE $bdrmChecked AND $btrmChecked AND $petsChecked");

Also note I didn't include any checks to validate the values in $_POST. I assume you have that already being done in our code.

Lastly an inconsequential note. You are using 3 different kinds of double quotes in the HTML sample you gave. It is generally a good idea to stick to one.

[dschuett]

Thanks so much for this...I know I could use radio buttons or a drop down select list, but I want users to be able to include more than one option in their search. You made a very good point about what if the user selects Any, yes, and no.... not sure how i would go about handling this. Like I said, I'm VERY new to this, but this will hopefully give me a good starting point. If you think of any other solutions let me know, thanks again!

[dschuett]

One more thing I just thought of... is it possible to make it so IF a user checks "Any" that they CAN'T check anything else, but they CAN check multiple IF "Any" is NOT checked???