Forming a query string from multiple checkboxes

Question

I'm trying to form a query string from multiple checkboxes that will be used to query my database.

I have the following form:

            <fieldset data-role="controlgroup">

            <input type="checkbox" name="wheat" id="checkbox-1a" class="custom" />
            <label for="checkbox-1a">Wheat Allergy</label>

            <input type="checkbox" name="yeast" id="checkbox-2a" class="custom" />
            <label for="checkbox-2a">Yeast Allergy</label>

            <input type="checkbox" name="sugar" id="checkbox-3a" class="custom" />
            <label for="checkbox-3a">Sugar Allergy</label>

            <input type="checkbox" name="dairy" id="checkbox-4a" class="custom" />
            <label for="checkbox-4a">Dairy Allergy</label>

My PHP code is as follows:

        if(isset($_POST['wheat']))
        {
            $str1 = 'wheatfree = 1';
        }

        if(isset($_POST['yeast']))
        {
            $str2 = 'yeastfree = 1';
        }

        if(isset($_POST['sugar']))
        {
            $str3 = 'sugarfree = 1';
        }

        if(isset($_POST['dairy']))
        {
            $str4 = 'dairyfree = 1';
        }

        $fullsearch = $str1.$str2.$str3.$str4;

        $str_SQL = "SELECT * FROM recipes WHERE ".$fullsearch;

        echo $str_SQL;

This is sort of doing what I require, but it's not very graceful.

For one, the sql query looks like this:

SELECT * FROM recipes WHERE sugarfree = 1dairyfree = 1

and if users choose not to select one I of course get an Undefined variable error for the str that hasn't been selected.

Not really sure how to fix this or where to go next. I'd like some logic in here that just amended the string based on what is checked on the form which then forms a nice clean SQL query I can run against my DB. But alas i'm lost :(

Help?

give the check boxes name="allergy[]" then you have a nice array, you can then use implode, to make the query string — user557846
– user557846, Commented Feb 20, 2011 at 19:08

Lightness Races in Orbit · Accepted Answer · 2011-02-20 19:30:52Z

3

Further to Dave's answer:

$options = Array();
$ingredients = Array('wheat', 'yeast', 'sugar', 'dairy');

foreach ($ingredients as $i)
   if (isset($_POST[$i]))
      $options[] = $i . 'free = 1';

$sql = "SELECT * FROM recipes";
if (count($options))
   $sql .= " WHERE " . implode(' AND ', $options);

echo $sql;

But why aren't you using the value property of checkboxes?

<input type="checkbox" name="ingredients[]" value="wheat" />
<input type="checkbox" name="ingredients[]" value="sugar" />

etc.

Then:

$options = Array();
foreach ($_POST['ingredients'] as $i)
   $options[] = $i . 'free = 1'; // don't forget to escape $i somehow!

$sql = "SELECT * FROM recipes";
if (count($options))
   $sql .= " WHERE " . implode(' AND ', $options);

echo $sql;

edited Feb 20, 2011 at 19:30

answered Feb 20, 2011 at 19:15

Lightness Races in Orbit

387k77 gold badges670 silver badges1.1k bronze badges

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

4 Comments

redcow Over a year ago

Brilliant, didn't think of throwing it into an array rollseyes Thanks!

Lightness Races in Orbit Over a year ago

@redcow: I have expanded my answer to include a better alternative.

Felix Kling Over a year ago

This is not necessarily better as it is prone to SQL injections now (I see you mentioned to escape $i). Personally I think a "whitelist" is better. But anyway, just my opinion. It is still an improvement compared to the original ;) (and you already got +1 form me ;))

Lightness Races in Orbit Over a year ago

@Felix: Yea, I wasn't really sure tbh. It's certainly a toss-up between extensibility and sanity. :)

Dave Child · Accepted Answer · 2011-02-20 19:08:25Z

2

How about this:

$options = array();
if(isset($_POST['wheat']))
{
    $options[] = 'wheatfree = 1';
}

if(isset($_POST['yeast']))
{
    $options[] = 'yeastfree = 1';
}

if(isset($_POST['sugar']))
{
    $options[] = 'sugarfree = 1';
}

if(isset($_POST['dairy']))
{
    $options[] = 'dairyfree = 1';
}

$fullsearch = implode(' AND ', $options);

$str_SQL = "SELECT * FROM recipes";
if ($fullsearch <> '') {
    $str_SQL .= " WHERE " . $fullsearch;
}

echo $str_SQL;

answered Feb 20, 2011 at 19:08

Dave Child

7,9542 gold badges28 silver badges37 bronze badges

10 Comments

Dave Child Over a year ago

Heh, was just editing it to do that and change str to options when you commented. Thanks, though, and well spotted. (This was in response to another comment, since deleted.)

Lightness Races in Orbit Over a year ago

Ew, do you REALLY need all of these braces?

Dave Child Over a year ago

I love braces, me. Also, I find it's better to always add them - you never know when someone else will modify your code later and not spot an if with no braces ...

redcow Over a year ago

Thanks Dave, does exactly what I needed. Didn't think of throwing it into an array... :)

Lightness Races in Orbit Over a year ago

@Dave: Please see my answer which shows that there is no need for this verbose and redundant stream of conditionals.

|

Collectives™ on Stack Overflow

Forming a query string from multiple checkboxes

2 Answers 2

4 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related