CODEWITHSUNDEEP
phpsqlcheckbox
redcow
redcow

Reputation: 35

Forming a query string from multiple checkboxes

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?

Upvotes: 3

Views: 2490

Answers (2)

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385295

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;

Upvotes: 3

Dave Child
Dave Child

Reputation: 7901

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;

Upvotes: 2

Related Questions