Retrieve data with a form using WHERE for multiple checkboxes

Question

I need a php script that will use the values of the form's checkboxes as the MySQL query to retrieve data. I'm thinking I'll need to use WHERE along with the OR operator in the query.

My PHP and MySql level is beginner.

<form id="form1" name="form1" method="post" action="">

    <p>
        <h3>story</h3>
        <label><input type="checkbox" name="story" value="1" id="story_1" />one story</label>
        <label><input type="checkbox" name="story" value="2" id="story_2" />two story</label>
    </p>

    <p>
        <h3>bedrooms</h3>
        <label><input type="checkbox" name="bedroom" value="2" id="bed_2" />two beds</label>
        <label><input type="checkbox" name="bedroom" value="3" id="bed_3" />three beds</label>
        <label><input type="checkbox" name="bedroom" value="4" id="bed_4" />four beds</label>
    </p>

    <p>
        <h3>baths</h3>
        <label><input type="checkbox" name="bath" value="1" id="bath_1" />one bath</label>
        <label><input type="checkbox" name="bath" value="2" id="bath_2" />two baths</label>
        <label><input type="checkbox" name="bath" value="3" id="bath_3" />three baths</label>
    </p>

    <input type="submit" name="search" value="search" />

</form>

My query so far

SELECT * FROM homes WHERE story='1' OR story='2' OR bed='3' OR baths='3'

So if no checkboxes are checked no WHERE clause will be added at all and it will default to retrieve all records.

@joeframbach: Yeah, I want a 1 and 2 story house, with two and three baths. — Madara's Ghost
– Madara's Ghost, Commented Apr 14, 2012 at 15:05
Yes. I am searching real estate listings, and I wan to find either a 1 or 2 story house with 2 or 3 baths. — 000
– 000, Commented Apr 14, 2012 at 15:06
If multiple selections are valid, should be using arrays like name="story[]". Use arrays or radio buttons. — Marcus Adams
– Marcus Adams, Commented Apr 14, 2012 at 15:12

Steve Stedman · Accepted Answer · 2012-04-14 15:08:21Z

2

Using the select statement as you have it with so many or's will cause problems.

SELECT * FROM homes WHERE story='1' OR story='2' OR bed='3' OR baths='3'

This will produce all homes where story = 1 with any number of bathrooms or bedrooms. where story = 2 with any number of bathrooms or bedrooms. where bed = 3 with any number of stories or baths where baths = 3 with any number of beds or baths

so you will probably end up getting a result with all homes

what you should do is separate out the different types with parens, and use AND's between them.

SELECT * FROM homes WHERE (story='1' OR story='2') and (bed='3') and (baths='3')

or for more complex queries, something like this.

SELECT * FROM homes WHERE (story='1' OR story='2') 
                      and (bed='3' OR bed = '4') 
                      and (baths='3' OR baths='4')

which will produce the results that have a story of 1 or 2 and bedrooms of 3 or 4 and baths of 3 or 4. You won't get any 3 story houses, you won't get any 2 bedroom houses, and you won't get an 2 bathroom houses.

I think this will do it.

answered Apr 14, 2012 at 15:08

Steve Stedman

2,6723 gold badges22 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marcus Adams Over a year ago

Could also use IN like WHERE (story IN (1,2)) AND (bed IN (3,4)).

grossvogel · Accepted Answer · 2012-04-14 15:20:34Z

You can get all of the selected values in an array by naming the inputs with the same thing, followed by square brackets:

HTML:
<input type="checkbox" name="bed[]" value="1" /> One Bedroom
<input type="checkbox" name="bed[]" value="2" /> Two Bedrooms

You can then use the mysql IN() function in your WHERE clause. PHP:

$aBeds = $_POST['bed'];  // array (1,2) if both are checked
$aBeds = array_map ('intval', $aBeds); // filter with intval for sql injection
$sBeds = implode (',', $aBeds); // "1,2" if both are checked
$query = "SELECT  * FROM homes WHERE bed IN($sBeds) and ...";

NOTE: I did some quick and dirty input filtering there, but be aware that this probably isn't the best way to avoid SQL injection. Better to use a data access layer, prepared statements, or some other universal scheme where every single query doesn't require its own filtering code.

000 · Accepted Answer · 2012-04-14 15:13:10Z

1

I added some extras for injection attack avoidance.

$query = 'SELECT * FROM homes WHERE 1';

// Protect against injection attacks
$valid_responses = array(
    'story' => array(
        '1','2'),
    'bedroom' => array(
        '2','3','4'),
    'bath_1' => array(
        '1','2','3'),
);
foreach ($valid_responses as $field=>$values) {
    $selection = array_intersect($_POST[$field],$values);
    if (!empty($selection)) {
        $query .= ' AND ' . $field . ' IN (' . implode(',',$selection) . ')';
    }
}

answered Apr 14, 2012 at 15:13

000

27.3k10 gold badges74 silver badges103 bronze badges

3 Comments

Joel Z. Over a year ago

looks like it's just what I needed. I won't have time to test today but I'm sure you're way better then me and correct. Thank you.

Joel Z. Over a year ago

It's been a while but could you please tell me how to avoid getting this error when using the code as you provide? Warning: array_intersect(): Argument #1 is not an array

Joel Z. Over a year ago

any further help would be much appreciated

Reactgular · Accepted Answer · 2012-04-14 15:11:37Z

You need to have unique "name" attributes on each input in the HTML. When the form is posted, it will be sent to the URL address of the "action" attribute. Which you have blank, so you might want to send it to "something.php" and have a PHP file on the server with the same name.

You will know if the form was posted to the PHP file if empty($_POST) returns True. This variable holds the posted data from the form.

Each input tag's name attribute will create an array entry in the $_POST global variable. So $_POST["bath_1"] will be 1 if the check box is enabled.

There are many different ways to convert the $_POST data into a SQL statement for what you want. I would recommend getting your handling of posted data working first, and come back with a more specific question related to processing arrays and generating SQL where statements.

Collectives™ on Stack Overflow

Retrieve data with a form using WHERE for multiple checkboxes

4 Answers 4

1 Comment

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related