Multiple Request Search via Jqgrid & SQL

Question

I am trying to use multiple input fields in a page where a person would select "a" from one input field, then "b" from a 2nd input field, resulting in a database call where the database would grab and output data from those columns.

Example : I have one category called "fruit", and another called "color". User would select "apple", and then "red" - and the database would call up only red apples. Or the user could only select "apple" , leaving the other form field blank, and the database would call up ALL apples (red, green, etc).

Currently I have it as follows that you can search ONE field, but as soon as you try to search multiple, the code is not allowing both searches, only one... at a loss as to why. Any leads in the right direction would be MOST helpful!

Code on page :

<input onkeydown="doSearch(event)" id="a" type="text" class="form-control" placeholder="a">
<input onkeydown="doSearch(event)" id="b" type="text" class="form-control" placeholder="b">
<input onkeydown="doSearch(event)" id="c" type="text" class="form-control" placeholder="c">

Grid reload code :

function gridReload(){
        searchData = {
            a: jQuery("#a").val(),
            b: jQuery("#b").val(),
            c: jQuery("#c").val(),
                 }

var gridParam = { url: "server.php", datatype: "json", postData: searchData, page: 1 };        
        jQuery("#grid").jqGrid('setGridParam', gridParam).trigger("reloadGrid");
}

PHP SQL Code :

$query = array();
if( isset($_REQUEST['data'])){
    $cond = ' ';
    $a = $_REQUEST['a'];
    $b = $_REQUEST['b'];
    $c = $_REQUEST['c'];

    if( !empty( $a ) ) $cond .= "WHERE db.a like '%$a%'";
    if( !empty( $b ) ) $cond .= "WHERE db.b like '%$b%'";
    if( !empty( $c ) ) $cond .= "WHERE db.c like '%$c%'";

Thoughts : looking at my POST data, it IS posting all of the information for the search correctly, I think the issue is inserting "AND" in the SQL code dynamically depending on what is being searched - Not sure how that will be done as of yet....

Answer 1

One possible solution is toy push the conditions in array and use implode like this

$query = array();
if( isset($_REQUEST['data'])){
    $a = $_REQUEST['a'];
    $b = $_REQUEST['b'];
    $c = $_REQUEST['c'];
    $where = array();
    if( !empty( $a ) ) $where[] = "db.a like '%$a%'";
    if( !empty( $b ) ) $where[] = "db.b like '%$b%'";
    if( !empty( $c ) ) $where[] = "db.c like '%$c%'";
    $cond = "";

    if(count($where) > 0 ) { 
        $cond = " WHERE ".implode(" AND ", $where);
    }