CODEWITHSUNDEEP
phpmysql
Joel Z.
Joel Z.

Reputation: 573

Retrieve data with a form using WHERE for multiple checkboxes

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.

Upvotes: 2

Views: 1757

Answers (4)

grossvogel
grossvogel

Reputation: 6782

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.

Upvotes: 2

000
000

Reputation: 27227

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) . ')';
    }
}

Upvotes: 1

Reactgular
Reactgular

Reputation: 54761

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.

Upvotes: 1

Steve Stedman
Steve Stedman

Reputation: 2672

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.

Upvotes: 2

Related Questions