PHP/MySQL/Ajax - Multiple search results from different inputs and variables

Question

I have had a look around and have not found a solution to this yet. Presumably due to my amateur abilities I am having problems creating dynamic search results that have more than 1 search option. I want to be able to have the search results populate and update dynamically as each new option is selected.

I have been trying to get it to work just on 2 separate options to begin with until I get it working. I have a dropdown menu, which passes variable 'q' via ajax, and a checkbox (acting as a button) which passes variable 'wblack', both jQuery UI. I can get them to work separately but they just change the search results to their own sql query without updating it to include both options (I hope that makes sense!). I would like to be able to stack up these options in a working MySQL query so that when both are selected the search results display based on both options.

Any advice is appreciated, thanks.

My PHP is:

require_once 'mysql_login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());

mysql_select_db($db_database)
    or die("Unable to select database: " . msql_error());

$sql="SELECT * FROM `TABLE 1` WHERE ";

if (isset($_GET['q'])) {
    $q = $_GET['q'];
    $sql .= "ProductDescription LIKE \"%".$q."%\" AND ";
}
if (isset($_GET['wblack'])) {
    $wblack = $_GET['wblack'];
    $sql .= "ProductDescription LIKE \"%".$wblack."%\" AND ";
}
if (substr($sql, strlen($sql) - strlen('WHERE ')) == 'WHERE ') {
$sql = substr($sql, 0, strlen($sql) - strlen('WHERE '));
}
if (substr($sql, strlen($sql) - strlen('AND ')) == 'AND ') {
$sql = substr($sql, 0, strlen($sql) - strlen('AND '));
}

$result = mysql_query($sql);

    while ($row = mysql_fetch_array($result))
{
    echo "<div class='width'><div class='parent'><div class='searchimage'>";
    echo "<img src='{$row['ImageURL']}' /></div></div>";
    echo "<br><br>".$row['ProductName']."<br>"; 
    echo "&pound".$row['ProductPrice'];
    echo "</div>";
}

if (!$result) die ("Database access failed: " . mysql_error());

mysql_close($db_server);

jQuery is:

    var q = $('#selectmenu').val();
    $("#selectmenu" ).selectmenu ({     
        change:function( event,ui ) { 
        q = $(this).val();

        $.ajax({
            url: 'searchtestphp.php',
            data: 'q='+q,
            success: function (data) {
                $('#searchresults').html(data);
            }
        }).error(function() {
            alert ('An error occured');
        });
        }
    });
});
        $(function() {
            $( "#checkblack" ).button();
            $( "#checkblack" ).click(function(){
                if($(this).is(':checked')) {
                var wblack = $(this).val();

                    $.ajax({
                        url: 'searchtestphp.php',
                        data: 'wblack='+wblack,
                        success: function (data) {
                        $('#searchresults').html(data);
                        }
                    });     
                }
            });
        });

HTML:

<select id="selectmenu" name="selectmenu">
    <option value="">--Select--</option>
    <option value="dress">Dresses</option>
    <option value="tshirt">T-Shirts</option>
    <option value="skirt">Skirts</option>
    <option value="shoes">Shoes</option>
    <option value="top">Tops</option>
    <option value="floral">Floral</option>
    <option value="trousers">Trousers</option>
</select>

<input name="colour[]" type="checkbox" class="checksize" id="checkblack" value="black"/> <label for="checkblack">Black</label>

Answer 1

PHP:

Here is better way to build your SQL query with or without 'where' clause, depending on $_GET

$where = array ();
if (isset($_GET['q'])) {
    $where[] = 'ProductDescription LIKE "%' . addslashes  ($_GET['q']) . '%"';
}

if (isset($_GET['wblack'])) {
    $where[] = 'ProductDescription LIKE "%' . addslashes  ($_GET['wblack']) . '%"';
}

$sql = 'SELECT * FROM `TABLE 1` ' . (!empty ($where) ? 'WHERE (' . implode (') AND (', $where) . ')' : '');

addslashes increases security a little bit, it still isn't best possible solution but is much better than what you have in your version (there are many topics on stackoverflow when you can read more about addslashes vulnerabilities).

JS: Suggested changes: one ajax request, both params passed if value != false:

$(function() {
    function search () {
        var ajax_data = {};

        var q = $("#selectmenu").val();
        if (q) {
            ajax_data.q = q;
        }

        var wblack = $("#checkblack").val();
        if (wblack) {
            ajax_data.wblack = wblack;
        }

        $.ajax({
            url: 'searchtestphp.php',
            data: ajax_data,
            success: function (data) {
                $('#searchresults').html(data);
            }
        }).error(function() {
            alert ('An error occured');
        });
    }

    $("#selectmenu").selectmenu ({
        change:function( event, ui ) { 
            search ();
        }
    });

    $("#checkblack").button();
    $("#checkblack").on("click", function () {
        search ();
    });
});