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>

MagyaDEV · Accepted Answer · 2014-09-16 23:35:56Z

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 ();
    });
});

answered Sep 16, 2014 at 23:35

MagyaDEV

3753 silver badges8 bronze badges

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

4 Comments

Simmo Over a year ago

Thanks for your response, much appreciated, that's certainly improved the functionality. However when I select an item from the dropdown it passes both the 'q' variable and the 'wblack' variable instead of just 'q'. With the button alone it only passes 'wblack'. It is as if the button is always checked. What would be the best way to get the dropdown to pass just its own variable?

Simmo Over a year ago

I got it. I changed var wblack = $("#checkblack").val(); to var wblack = $("#checkblack:checked").val(); and it works. Thanks again!

Simmo Over a year ago

What is the purpose of the colon (:) in the sql statement above?

MagyaDEV Over a year ago

There's no ':' in the SQL itself, only ternary operator is used in this line - it's kind of simple inline if/else statement: php.net/manual/en/…. It's same as: $sql = 'SELECT * FROM TABLE 1; if ($where) { $sql .= 'WHERE (' . implode (') AND (', $where) . ')'; }

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related