Query building depending checkboxes selection

Question

I want to build a query form my database depending my checkboxes list.

My checkboxes:

<input type="checkbox" id="searchName" checked> Name
<input type="checkbox" id="searchAddress"> Address
<input type="checkbox" id="searchCompany"> Company
<input type="checkbox" id="searchComments"> Comments

My PHP:

$subQuery='';
if($_POST['searchName']=='true')     { $subQuery .= " AND KDX_Name LIKE :KDX_SearchTerm"; }
if($_POST['searchAddress']=='true')  { $subQuery .= " OR KDX_PostalAddress LIKE :KDX_SearchTerm"; }
if($_POST['searchCompany']=='true')  { $subQuery .= " OR KDX_Company LIKE :KDX_SearchTerm"; }
if($_POST['searchComments']=='true') { $subQuery .= " OR KDX_Comments LIKE :KDX_SearchTerm"; }

My problem:

If the first checkbox is not checked, my query is not working cause it works with OR whereas it must start with AND.

Could you please help ?

Thanks.

or [Dynamically Creating mysql select Query](http://stackoverflow.com/questions/22986065/dynamically-creating-mysql-select-query) — Lawrence Cherone, May 26 '14 at 21:24
This is not complete duplicate of http://stackoverflow.com/questions/17459150/building-an-sql-query-based-on-checkboxes It's a bit different :) — DarkSide, May 27 '14 at 16:32

DarkSide · Accepted Answer · 2014-05-26T21:28:36.037

You have many mistakes in your code in HTML and in PHP ... I'll not mention everything here, but this is how I would do this.

<input type="checkbox" name="searchName" checked="checked" />Name
<input type="checkbox" name="searchAddress" />Address
<input type="checkbox" name="searchCompany" />Company
<input type="checkbox" name="searchComments" />Comments

<?php
$fields = array(
    'searchName'    => 'KDX_Name',
    'searchAddress' => 'KDX_PostalAddress',
    'searchCompany' => 'KDX_Company',
    'searchComments'=> 'KDX_Comments',
);

$cond = array();
foreach ($fields as $form_field => $db_field) {
    if (isset($_POST[$form_field])) {
        $cond[] = "$db_field LIKE '%'" . mysql_escape($_POST[$form_field]) . "%'";
    }
}

$subQuery = implode(' OR ', $cond);
?>

More important are these things:

correct HTML form (not id, but name),
easy to extend PHP (use array for field names),
don't check for $_POST[foo]=='true' which is absolutely wrong,
add conditions in array and at the end use implode to easily concatenate everything together,
escape user input variables to avoid SQL injection attacks.

Going back from PDO to the `mysql_*` functions seems like a very bad idea. — jeroen, May 26 '14 at 21:24
Yeah, OK, you can use PDO - that doesn't matter so much in this case. See my edit above. — DarkSide, May 26 '14 at 21:25

Query building depending checkboxes selection

My checkboxes:

My PHP:

My problem:

1 Answers1