Filtering issue in mysql query triggered by ajax form

php
mysql

#1

Hi,
New to javascript and php. Perl/MySql is my usual.

Submitting via ajax, to a php script, I can’t seem to get the filtering to work, partly because I can’t access the values being received by php. $_POST[filterOpts]

Here is the html page that submits to the php script

<style>
      body {
        padding: 10px;
      }
 
      h1 {
          margin: 0 0 0.5em 0;
          color: #343434;
          font-weight: normal;
          font-family: 'Ultra', sans-serif;   
          font-size: 36px;
          line-height: 42px;
          text-transform: uppercase;
          text-shadow: 0 2px white, 0 3px #777;
      }
 
      h2 {
          margin: 1em 0 0.3em 0;
          color: #343434;
          font-weight: normal;
          font-size: 30px;
          line-height: 40px;
          font-family: 'Orienta', sans-serif;
      }
 
      #phones {
        font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
        font-size: 12px;
        background: #fff;
        margin: 15px 25px 0 0;
        border-collapse: collapse;
        text-align: center;
        float: left;
      }
 
      #phones th {
        font-size: 14px;
        font-weight: normal;
        color: #039;
        padding: 10px 8px;
        border-bottom: 2px solid #6678b1;
      }
 
      #phones td {
        border-bottom: 1px solid #ccc;
        color: #669;
        padding: 8px 10px;
      }
 
      #phones tbody tr:hover td {
        color: #009;
      }
 
      #filter {
        float:left;
      }

      fieldset{
        margin-top: 15px;
      }

      fieldset div{
        padding:0 0 5px 0;
      }

      .amount{
        width:50px;
      }
pre{
  margin:0 0 20px 0;
  padding:10px;
  color:red;
  background: gray;
}
    </style>
  </head>
  <body> 

   


    <h1>Filters table</h1>
 
    <table id="phones">
      <thead>
        <tr>
          <th width="15">ID</th>
          <th>Brand</th>
        </tr>
      </thead>
      <tbody>
      </tbody>
    </table>


<div id="filter">

  <h2>Filter options</h2>
  <div>
    <input type="checkbox" id="Kenwood">
    <label for="Kenwood">Kenwood</label>
  </div>
  <div>
    <input type="checkbox" id="Oxo">
    <label for="Oxo">Oxo</label>
  </div>
 <div>
    <input type="checkbox" id="Brabantia">
    <label for="Brabantia">Brabantia</label>
  </div>

</div>




  <div id="debug"></div>
    <script src="http://code.jquery.com/jquery-latest.js"></script> 
    <script>

    //debug = true;
    //  function handleDebug(debugInfo){
    //    $("#debug").html("<pre>" + debugInfo + "</pre>");
    //  }


      function makeTable(data){
        var tbl_body = "";

        $.each(data, function(k, v) {
          var tbl_row = "",
              currRecord = this;


//if(k==="debug"){
//      if(debug === true){
//        handleDebug(v);
//      }
//      return;
//    }


          $.each(this, function(k , v) {
            if(k==='brand'){
              v = "<a href='content.php?id=" + currRecord['id'] +"'>" + v + "</a>";
            } else if (k==='size'){
              v = "<span class='price'>" + v + "</span>";
            }
            tbl_row += "<td>"+v+"</td>";
          })
          tbl_body += "<tr>"+tbl_row+"</tr>";
        })

        return tbl_body;
      }
 
      function getPhoneFilterOptions(){
        var opts = [];
        $checkboxes.each(function(){
          if(this.checked){
            opts.push(this.id);
          }
        });
 
        return opts;
      }
 
      function updatePhones(opts){
        $.ajax({
          type: "POST",
          url: "/php-queries/product-catalogue-query.php",
          dataType : 'json',
          cache: false,
          data: {filterOpts: opts},
          success: function(records){
            $('#phones tbody').html(makeTable(records));
            updatePrices();
          }
        });
      }
      
      function subsidyIsValid(){
        var amount1 = $("#amount1").val(),
            amount2 = $("#amount2").val(),
            regex = /^\d+$/,
            inputValid = false;

        if(regex.test(amount1) && regex.test(amount2)){
          var newTotal = Number(amount1) + Number(amount2)
          $("#total").text(newTotal);
          inputValid = true;
        }

        return inputValid
      }

      function updatePrices(){
        var subsidyTotal = Number($("#total").text());

        $(".price").each(function(){
          var origVal = Number($(this).text())
          $(this).text(origVal - subsidyTotal)
        })
      }

      var $checkboxes = $("input:checkbox");
      $checkboxes.on("change", function(){
        var opts = getPhoneFilterOptions();
        updatePhones(opts);
      });
      
      $("#apply").on("click", function(){
        if(subsidyIsValid()){
          $(this).prop("disabled", true);
          $(this).next().prop("disabled", false);
          updatePrices();
        } else {
          alert("Subsidy invalid!")
        }
      });

      $("#remove").on("click", function(){
        $("#amount1").val("");
        $("#amount2").val("");
        $("#total").text("0");
        $(this).prop("disabled", true);
        $(this).prev().prop("disabled", false);

        $checkboxes.trigger("change");
      });

      $checkboxes.trigger("change");
      updatePrices();
    </script> 

Here is the php code of the receiving php page

$conn = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $select = 'SELECT id, brand';
    $from = ' FROM retailers_product_catalogue';
    $where = ' WHERE ';


    $opts = $_POST['filterOpts'];


   if (empty($opts))
   {
   // 0 checkboxes checked
   $where .= 'TRUE';
   }
   else 
   {
       if(count($opts) == 1)
       {
       // 1 checkbox checked
       $where .= $opts . " = $opts[0]";
       } 
   }
 //   else 
 //   {
 //   // 2+ checkboxes checked
 //   $where .= implode(' = 1 OR ', $opts) . ' = 1';
 //   }
  

    $sql = $select . $from . $where;
    $statement = $conn->prepare($sql);
    $statement->execute();
    $results=$statement->fetchAll(PDO::FETCH_ASSOC);

  //$debugInfo = array('debug' => vsprintf(str_replace("?", "%s", $sql->queryString), $opts));


  //$results = array_merge($debugInfo, $results);

    $json=json_encode($results);
    echo($json);



   

    }
    catch(PDOException $e)
    {
    echo "Error: " . $e->getMessage();
    }


$conn = null;

The checkboxes that will be used to filter the resultset will be submitting text values (not boolean).
Those values could be Kenwood, Brabantia Oxo or any other brand of Kitchen equipment. I need the filtering portion to bring back from the Db, just those brands selected.

Also, I’ve used the debug and it shows no output unless I force it to do something wrong. So how would I force the output of the $_POST[filterOpts] as they are received into this script? Dump doesn’t display because the triggering script only outputs whats returned in the resultset.

Any assistance would be much appreciated.

Bazz


#2

This bit doesn’t look correct to me:

if(count($opts) == 1)
       {
       // 1 checkbox checked
       $where .= $opts . " = $opts[0]";
       } 

You have an array called $opts, so you can’t just append it to your $where string as it’ll just give you a syntax error at best, or maybe just add the word “array” to the query, which in turn will give a syntax error when you run the query.

Instead of $opts in that line, you need to put the column name that you’re comparing it with.

For multiple check-boxes, you could use the IN construction:

where brand in (kenwood, Panasonic, whatever)

and just implode the array.

What’s your data table layout like? In a normal database layout, you’d store these brands in a separate table with their own ID, have each item reference the brand ID and pass that through as the value from your checkboxes. Doesn’t make any difference to this specific issue, but makes it less prone to spelling, quotation marks and case-sensitive issues.

For debugging, I’d just alter the calling code to alert(records) on return from the PHP script, and display debug information in there until you have it all working. That probably shows how little real-world experience I have of this stuff, but it does allow you to see what’s happening. Or console.log it if you prefer, or even have the PHP write into a log file.


#3

Thank you droopsnoot.

I need to read up on PHP arrays some more. In my familiar language, Perl, an array is @array and not $array, which is a variable.

The Db is normalised.

Once I get this working, I’ll use whatever I’ve learned to make more progress though, I expect I’l be back for a couple more functions. :thumbsup:


#4

Progress :slight_smile:
This gets me the results I wanted to begin with. Before I go further, should I use mysql_escape_string or is the prepare statement sufficient/adequate?

$conn = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
    // set the PDO error mode to exception
    //$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $select = 'SELECT id, brand';
    $from = ' FROM retailers_product_catalogue';
    $where = ' WHERE ';


    $opts = $_POST['filterOpts'];


   if (empty($opts))
   {
   // 0 checkboxes checked
   $where .= 'TRUE';
   }
   else 
   { 
   // one or more checkboxes are checked
   $where .= 'brand in ' . "('" . implode( "','" , $opts ) . "')"; 
   }



  

    $sql = $select . $from . $where;
    $statement = $conn->prepare($sql); 
    $statement->execute();
    $results=$statement->fetchAll(PDO::FETCH_ASSOC);


  //$debugInfo = array('debug' => vsprintf(str_replace("?", "%s", $statement->queryString), $opts));
  //$results = array_merge($debugInfo, $results);



    $json=json_encode($results);
    echo($json);

Now, I want to be able to differentiate between groups of params being passed into this query. There will be the selected brands being passed and also, product_type eg ‘food processors’, ‘kettles’, ‘saucepans’ and then, also, the size (eg saucepan volume) etc.

Is it possible to submit the params, grouped by category, product_type etc or should I just run all the $opts through the several conditions?

 $select = 'SELECT rpc.id, rpc.brand, rpc2.category, rpd.size, rpd.size_units';
    $from = ' FROM retailers_product_catalogue as rpc inner join retailers_product_category as rpc2 on rpc2.catalogue_id = rpc.id inner join retailers_product_data as rpd on rpd.catalogue_id = rpc.id ';
    $where = ' WHERE ';

    $opts = $_POST['filterOpts'];

   if (empty($opts))
   {
   // 0 checkboxes checked
   $where .= 'TRUE';
   }
   else 
   { 
   // one or more checkboxes are checked
     // get the brand data
     $where .= 'brand in ' . "('" . implode( "','" , $opts ) . "')"; 

    // get the product_type data
   $where .= ' or product_type in ' . "('" . implode( "','" , $opts ) . "')";

    //get the size data 
   $where .= 'size in ' . "('" . implode( "','" , $opts ) . "')"; 
   }

I’d welcome your assistance.
Bazz


#5

I believe there is no need to use that function if you’re using prepared statements. That’s more of a question for the PHP section of the board, but I believe you can ignore it.

Even though the brand names (and, it seems product types) are stored as text within each row in the product catalogue? OK, if it works for you - I haven’t read up on normalisation for a long time.

I don’t think there’s any distinction of that sort in PHP, in that something preceded by a dollar-sign could be a flat variable, an array of one or more dimensions, or an object.

In your code for trapping the checkbox being clicked, you call getPhoneFilterOptions() which gathers the selections into an array and returns it in opts array. You could have another function to recover the products types into a separate array, and another to grab the sizes into a third array - I don’t know enough about JS to know if you could return the three separate arrays from a single function. You could then modify your updatePhones() function call and definition to accept those three arrays, and add them as separate arrays in your {data} definition when you call the PHP code.

data: {brandOpts: opts, 
          typeOpts: topts,
          sizeOpts: sopts},

You’d probably have to modify the function to get the checkbox values for a specific div to make it work with separate arrays, or add a class or something, as the current function just seems to get all checkbox values.


#6

Thanks. That should help me to push on a bit more. :slight_smile:

Yes, the current function just gets all the results from the checkbox values in one dataset.

For info: The finished code will get me all the Brands, product_type data, size data, colour data, price data for a consistent nav menu down the left (on desktop) and the checkboxes checked as per the selection made. Then, also, the filtered data shown in the main body area of the page.

I want to be able to make/change any selection without re-loading the page and for the nav to adjust its various options for selection accordingly. eg. for televisions I’d want to show screen size and dimensions but for shoes I’d want only to show the shoe size.

Bazz


#7

In that case it probably wouldn’t be the best thing to have an individual function in the JS to gather the various options, you’ll need to have a way to gather the selections and also gather some information on what column they apply to - a data- parameter perhaps?


#8

Several issues I’m struggling with…

I can’t seem to get a handle on arrays and, in particular, the checking if a variable exists in the array
I’d appreciate any tips or assistance you can give me.

the ajax code is sent like this

<table id="product_catalogue">
      <thead>
        <tr>
          <th width="15">ID</th>
          <th>Brand</th>
        </tr>
      </thead>
      <tbody>
      </tbody>
    </table>


  <div id="debug"></div>
    <script src="http://code.jquery.com/jquery-latest.js"></script> 
    <script>

    debug = true;
    function handleDebug(debugInfo)
    {
    $("#debug").html("<pre>" + debugInfo + "</pre>");
    }


    function makeTable(data)
    {
    var tbl_body = "";



        $.each(data, function(k, v) 
        {
        var tbl_row = "",
        currRecord = this;


            if(k==="debug")
            {

                if(debug === true)
                {
                handleDebug(v);
                }
            return;
            }


            $.each(this, function(k , v) 
            {
  
                if( k==='brand' )
                {
                v = "<a href='content.php?id=" + currRecord['id'] +"'>" + v + "</a>";
                }
                else if ( k==='size' )
                {
                v = "<span class='price'>" + v + "</span>";
                }
                else
                {
                v= "<span class='something_else'>" + v + "</span>";
                }

            tbl_row += "<td>"+v+"</td>";
            })

            tbl_body += "<tr>"+tbl_row+"</tr>";
        })

    return tbl_body;
    }
 



    function getCatalogueFilterOptions()
    {
    var opts = [];
   

        $checkboxes.each(function()
        {
            if(this.checked)
            {
            opts.push(this.id);
            }
        });
 
    return opts;
    }
 



    function updateCatalogue(opts)
    {
        $.ajax(
        {
        type: "POST",
        url: "/php-queries/product-catalogue-query.php",
        dataType : 'json',
        cache: false,
        data: {filterOpts: opts},
        
          success: function(records)
          {
          $('#product_catalogue tbody').html(makeTable(records));
          }
        });
    }
    

  
    function subsidyIsValid()
    {
    var amount1 = $("#amount1").val(),
    amount2 = $("#amount2").val(),
    regex = /^\d+$/,
    inputValid = false;

        if(regex.test(amount1) && regex.test(amount2))
        {
        var newTotal = Number(amount1) + Number(amount2)
        
        $("#total").text(newTotal);
        inputValid = true;
        }

    return inputValid
    }





    function updatePrices()
    {
    var subsidyTotal = Number($("#total").text());

        $(".price").each(function()
        {
        var origVal = Number($(this).text())
        $(this).text(origVal - subsidyTotal)
        })
    }



//# this seems to be where the data is pushed to the catalogue output
//# we need to get the data for pushing to the nav menu 

    var $checkboxes = $("input:checkbox");
    $checkboxes.on("change", function()
    {
    var opts = getCatalogueFilterOptions();
    updateCatalogue(opts);
    });
    

// end //




  
    $("#apply").on("click", function()
    {
    
         if(subsidyIsValid())
         {
         $(this).prop("disabled", true);
         $(this).next().prop("disabled", false);
         updatePrices();
         }
         else 
         {
         alert("Subsidy invalid!")
         }
    });




    $("#remove").on("click", function()
    {
    $("#amount1").val("");
    $("#amount2").val("");
    $("#total").text("0");
    $(this).prop("disabled", true);
    $(this).prev().prop("disabled", false);
    $checkboxes.trigger("change");
    });



    $checkboxes.trigger("change");
    updatePrices();
    </script> 

The receiving php query code is this:

try {
    $conn = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
    // set the PDO error mode to exception
    //$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $select = 'select rpc.id as catalogue_id
                    , rpc.brand as brand
                    , rpc2.category as category
                    , rpd.size as size
                    , rpd.size_units as size_units
                    , rpd.colour as colour
                    , rpd.colour_code as colour_code
                    , rpd.dimensions as dimensions
                    , rpd.dimensions_units as dimensions_units
                    , rpd.capacity as capacity
                    , rpd.capacity_units as capacity_units 
                    , rpcp.price_type as price_type
                    , rpcp.price as price
              ';
    $from = ' FROM retailers_product_catalogue as rpc 
           left outer 
              join retailers_product_category as rpc2 
                on rpc2.catalogue_id = rpc.id 
           left outer  
              join retailers_product_colours as rpd 
                on rpd.catalogue_id = rpc.id 
           left outer 
              join retailers_product_catalogue_prices as rpcp
                on rpcp.catalogue_id = rpc.id  
              '; 

    $where = ' WHERE TRUE';


   $opts = $_POST['filterOpts'];




  

    $sql = $select . $from . $where;
    $statement = $conn->prepare($sql); 
    $statement->execute();
    $results=$statement->fetchAll(PDO::FETCH_ASSOC);



//  OK AS FAR AS THIS POINT.
// results being returned correctly and displayed in the main (trigger) page.


// LOOP THROUGH THE RESULTS ARRAY 
// and if the submitted variables match with the returned values, PUSH $results array into $filtered_results array
   
  foreach ( $results as $key => $value) 
  {
  echo "key=$key v=$value";
  echo "<pre>opts=";
  var_dump($opts);
  echo "</pre>";

      foreach ( $value as $k => $v )
      {
      echo"<pre>";
      echo "k=$k => v=$v";
      echo "</pre>";
        

// STRUGGLING HERE
          if ( in_array( $v , implode( ' ' , $opts ) ) ) 
          { 
          echo "THIS IS IT $v";
          array_push($filtered_results, $results);
          }
      }
  }



  $debugInfo = array('debug' => vsprintf(str_replace("?", "%s", $filtered_results->queryString), $opts));
  $results = array_merge( $debugInfo, $filtered_results );


   
    
    $json=json_encode($results);
    echo($json);
    


   

    }
    catch(PDOException $e)
    {
    echo "Error: " . $e->getMessage();
    }


$conn = null;

So the ajax submits an array of items selected by checkbox. my query will bring back everything that could be required (I want to build a nav menu with all options in it so only the query data changes with no page reloads).
And I want to build another resultset which contains only that data that matches with the submitted options.

How should I match a value returned from the query with the array $opts … this bit

// STRUGGLING HERE
          if ( in_array( $v , implode( ' ' , $opts ) ) ) 
          { 
          echo "THIS IS IT $v";
          array_push($filtered_results, $results);
          }

And is my foreach loop within a foreach loop, the most efficient way to break down the array $results?
I want all data in both of the arrays below, to be pushed into $filtered_results array, if the make ‘Kenwood’ was submitted.

(lots more to do in terms of size, colours etc but if you can help me with this bit, I should be able to complete the building of the $filtered_results array).

Below is the $results array as returned by the db query.

[158]=>
  array(13) {
    ["catalogue_id"]=>
    string(3) "449"
    ["brand"]=>
    string(7) "kenwood"
    ["category"]=>
    string(17) "kitchen equipment"
    ["size"]=>
    string(0) ""
    ["size_units"]=>
    string(0) ""
    ["colour"]=>
    string(15) "Stainless steel"
    ["colour_code"]=>
    string(0) ""
    ["dimensions"]=>
    string(0) ""
    ["dimensions_units"]=>
    string(0) ""
    ["capacity"]=>
    string(3) "1.7"
    ["capacity_units"]=>
    string(1) "l"
    ["price_type"]=>
    NULL
    ["price"]=>
    NULL
  }
  [159]=>
  array(13) {
    ["catalogue_id"]=>
    string(3) "450"
    ["brand"]=>
    string(7) "kenwood"
    ["category"]=>
    string(17) "kitchen equipment"
    ["size"]=>
    string(0) ""
    ["size_units"]=>
    string(0) ""
    ["colour"]=>
    string(23) "stainless steel & black"
    ["colour_code"]=>
    string(0) ""
    ["dimensions"]=>
    string(0) ""
    ["dimensions_units"]=>
    string(0) ""
    ["capacity"]=>
    string(1) "1"
    ["capacity_units"]=>
    string(1) "l"
    ["price_type"]=>
    NULL
    ["price"]=>
    NULL
  }

#9

O-o-o-h… progress??

The following seem to be true even if no params have been submitted.

if ( is_array($opts))
{

}

and the following always runs even with an empty array

if ( !empty($opts) ){}

becoming frustrated.

bazz


#10

Resolved. for a while. Next to bring back two datasets into the calling script (instead of the one), and then format the html. :slight_smile: