AJAX filter problem

chungmx5 · June 2, 2014, 11:07am

Hi, I am a newbie in SitePoint. I followed the samples in SitePoint then made my test.

I checked one brand and one price, everything are OK. However, when I checked one more brand (checked two brands), this is no result appear.

Below is my test sample.

CREATE TABLE IF NOT EXISTS shoetest (
id int(11) NOT NULL AUTO_INCREMENT,
brand varchar(20) NOT NULL,
pPrice int(4) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

–
– Dumping data for table `shoetest`

INSERT INTO shoetest (id, brand, pPrice) VALUES
(1, ‘Adidas’, 105),
(2, ‘Adidas’, 130),
(3, ‘PUMA’, 115),
(4, ‘PUMA’, 108),
(5, ‘New Banlance’, 85),
(6, ‘New Banlance’, 140);

HTML

<!DOCTYPE HTML>
<html>
  <head>
    <meta charset="utf-8">
    <title>AJAX filter demo</title>
  </head>
  <body> 
  <h1>Phones database</h1>
 
    <table id="shoe">
      <thead>
        <tr>
          <th>ID</th>
          <th>Brand</th>
          <th>Price</th>
        </tr>
      </thead>
      <tbody>
      </tbody>
    </table>

 
<div id="filter">
<h2>Filter options</h2>
<div>
  <input type="checkbox" id="Adidas" value="Adidas">
  <label for="Adidas">Adidas</label>
</div>
<div>
  <input type="checkbox" id="NB" value="New Banlance">
  <label for="NB">New Banlance</label>
</div>
<div>
  <input type="checkbox" id="PUMA" value="PUMA">
  <label for="PUMA">PUMA</label>
</div>

<div>
  <input type="checkbox" id="p1" value="1">
  <label for="p1">< 100</label>
</div> 
<div>
  <input type="checkbox" id="p2" value="2">
  <label for="p2">$101 - $150</label>
</div>

</div>

  <script src="http://code.jquery.com/jquery-latest.js"></script> 
  <script>
    function makeTable(data){
      var tbl_body = "";
      $.each(data, function() {
        var tbl_row = "";
        $.each(this, function(k, v) {
          tbl_row += "<td>"+v+"</td>";
        })
        tbl_body += "<tr>"+tbl_row+"</tr>"; 
      })
      return tbl_body;
    }
 
    function getShoeOptions(){
        var opts = [];
        $checkboxes.each(function(){
            if(this.checked){
                opts.push(this.value);
            }
        
        });
        
        return opts;
    }

    function updateShoe(opts){
        $.ajax({
            type: "POST",
            url: "shoe.php",
            dataType : 'json',
            cache: false,
            data: {filterOpts: opts},
            success: function(data){
                $('#shoe tbody').html(makeTable(data));
            }
        });
    }

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


    updateShoe();

    </script> 
  </body> 
</html>

<?php 
    $pdo = new PDO('mysql:host=localhost;dbname=filtertest', 'root', '******');
    $select = 'SELECT id,brand,pPrice';
    $from = ' FROM shoetest';
    $where = ' WHERE TRUE';
  
    $opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
    
    //brand
    if (in_array("Adidas", $opts)){
    $where .= " AND brand ='Adidas'";
    }
    if (in_array("New Banlance", $opts)){
    $where .= " AND brand ='New Banlance'";
    }
    if (in_array("PUMA", $opts)){
    $where .= " AND brand ='PUMA'";
    }
    
    //price
    if (in_array("1", $opts)){
    $where .= " AND pPrice > 0 AND pPrice < 100";
    }
    if (in_array("2", $opts)){
    $where .= " AND pPrice > 101 AND pPrice < 150";
    }

    $sql = $select . $from . $where;
    $statement = $pdo->prepare($sql);
    $statement->execute();
    $results = $statement->fetchAll(PDO::FETCH_ASSOC);
    $json = json_encode($results);
    echo($json);
?>

Thanks

rpkamp · June 2, 2014, 7:03pm

If I select 2 brands your query would become:


SELECT id,
       brand,
       pPrice
FROM shoetest
WHERE TRUE
  AND brand ='Adidas'
  AND brand ='New Banlance'

Which won’t work because a shoo can’t be from the brand ‘Adidas’ and ‘New balance’ at the same time.

I’d do something like this:


$allBrands = array('Adidas', 'New Balance', 'PUMA');
$selectedBrands = array();
foreach ($allBrands as $brand) {
    if (in_array($brand, $opts)) {
        $selectedBrands[] = $brand;
    }
}

if (count($selectedBrands)) {
    $where .= " AND brand IN ('".implode("', '", $selectedBrands)."')";
}

Which for the two brands above will yield


SELECT id,
       brand,
       pPrice
FROM shoetest
WHERE TRUE
  AND brand IN ('Adidas',
                'New Banlance')

which does work

chungmx5 · June 3, 2014, 12:47pm

Thanks ScallioXTX.

How about the price part?

Thanks a lot

rpkamp · June 3, 2014, 6:37pm

Same deal, you should be able to figure it out from the code in my previous post.

chungmx5 · June 7, 2014, 4:06am

Finally, I made the price option to radio button.

I have based on this sample and add more function but no effect. It is using same DB.

Could you please help me again?

html part


<!DOCTYPE HTML>
<html>
  <head>
    <meta charset="utf-8">
    <title>AJAX filter demo</title>
<script src="http://code.jquery.com/jquery-latest.js"></script> 
  </head>  
  <body> 
  <h1>Phones database</h1>
 
    <table id="shoe">
	  <thead>
		<tr>
		  <th>ID</th>
		  <th>Brand</th>
		  <th>Price</th>
		</tr>
	  </thead>
	  <tbody>
	  </tbody>
	</table>

 
<div id="filter">
<h2>Filter options</h2>
<div>
  <input type="checkbox" id="Adidas" value="Adidas">
  <label for="Adidas">Adidas</label>
</div>
<div>
  <input type="checkbox" id="NB" value="New Balance">
  <label for="NB">New Balance</label>
</div>
<div>
  <input type="checkbox" id="PUMA" value="PUMA">
  <label for="PUMA">PUMA</label>
</div>

<div>
  <input type="radio" id="p1" name="price" value="1">
  <label for="p1">< 100</label>
</div> 
<div>
  <input type="radio" id="p2" name="price" value="2">
  <label for="p2">$101 - $150</label>
</div>

<!--new add these -->
		<div class="sort" id="atoz">A to Z</div>
        <div class="sort" id="ztoa">Z to A</div>
        <div class="sort" id="htol">High price to Low price</div>
        <div class="sort" id="ltoh">Low price to High price</div>

</div>


  <script>
	function makeTable(data){
	  var tbl_body = "";
	  $.each(data, function() {
		var tbl_row = "";
		$.each(this, function(k, v) {
		  tbl_row += "<td>"+v+"</td>";
		})
		tbl_body += "<tr>"+tbl_row+"</tr>"; 
	  })
	  return tbl_body;
	}
 
	function getShoeOptions(){
		var opts = [];
		$checkboxes.each(function(){
			if(this.checked){
				opts.push(this.value);
			}
		
		});
		$radioes.each(function(){
			if(this.checked){
				opts.push(this.value);
			}
		});
		return opts;
	}

	function updateShoe(opts){
		$.ajax({
			type: "POST",
			url: "shoe2.php",
			dataType : 'json',
			cache: false,
			data: {filterOpts: opts},
			success: function(data){
				$('#shoe tbody').html(makeTable(data));
			}
		});
	}

	var $checkboxes = $("input:checkbox");
	$checkboxes.on("change", function(){
		var opts = getShoeOptions();
		updateShoe(opts);
	});
	var $radioes = $("input:radio");
	$radioes.on("change", function(){
		var opts = getShoeOptions();
		updateShoe(opts);
	});

	//new add this sort function
	var $sort = $(".sort");

	$sort.on("click", function(){
		var opts = "";
		opts.push(this.id);
		updateShoe(opts);
	});



	updateShoe();

    </script> 
  </body> 
</html>


<?php 
	$pdo = new PDO('mysql:host=localhost;dbname=filtertest', 'root', '******');
	$select = 'SELECT id,brand,pPrice';
	$from = ' FROM shoetest';
	$where = ' WHERE TRUE';
  
	$opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
	
	//brand
	$allBrands = array('Adidas', 'New Balance', 'PUMA');
	$allPrice = array('1','2','3','4','5','6','7');

	$selectedBrands = array();
	foreach ($allBrands as $brand) {
		if (in_array($brand, $opts)) {
			$selectedBrands[] = $brand;
		}
	}

	if (count($selectedBrands)) {
		$where .= " AND brand IN ('".implode("', '", $selectedBrands)."')";
	}
	
	//price
	if (in_array(1, $opts)){
	$where .= " AND pPrice>=0 AND pPrice<=100";
	}
	else if (in_array(2, $opts)){
	$where .= " AND pPrice>=101 AND pPrice<=150";
	}

	if (in_array("atoz", $opts)){
	$order .= " ORDER BY brand ASC";
	}
	else if (in_array("ztoa", $opts)){
	$order .= " ORDER BY brand DESC";
	}
	else if (in_array("htol", $opts)){
	$order .= " ORDER BY pPrice DESC";
	}	
	else if (in_array("ltoh", $opts)){
	$order .= " ORDER BY pPrice ASC";
	}
	else {
	$order .= " ORDER BY pPrice ASC";
	}


	$sql = $select . $from . $where . $order;
	$statement = $pdo->prepare($sql);
	$statement->execute();
	$results = $statement->fetchAll(PDO::FETCH_ASSOC);
	$json = json_encode($results);
	echo($json);
?>

Thanks a lot

James_Hibbard · June 15, 2014, 12:48pm

Hi,

In your JS you have:

var opts = "";
opts.push(this.id);

but push is an method defined on Array, not on String.

Change it to this:

var opts = [B][];[/B]
opts.push(this.id);

and your code will work

chungmx5 · June 16, 2014, 1:10pm

Thanks Pullo.

It’s work, but the function seems separated.

I tried group the sort function to function getShoeOptions, the console has return [Object, Object], but the table has no effect. below is my code.


  <script>
	function makeTable(data){
	  var tbl_body = "";
	  $.each(data, function() {
		var tbl_row = "";
		$.each(this, function(k, v) {
		  tbl_row += "<td>"+v+"</td>";
		})
		tbl_body += "<tr>"+tbl_row+"</tr>"; 
	  })
	  return tbl_body;
	}
 
	function getShoeOptions(){
		var opts = [];
		$checkboxes.each(function(){
			if(this.checked){
				opts.push(this.value);
			}
		
		});
		$radioes.each(function(){
			if(this.checked){
				opts.push(this.value);
			}
		});
		$sort.one(function(){
			if(this.clicked){
				opts.push(this.id);
			}
		});
		return opts;
	}



	function updateShoe(opts){
		$.ajax({
			type: "POST",
			url: "shoe2.php",
			dataType : 'json',
			cache: false,
			data: {filterOpts: opts},
			success: function(data){
				$('#shoe tbody').html(makeTable(data));
console.log(data);
			}
		});
	}

	var $checkboxes = $("input:checkbox");
	$checkboxes.on("change", function(){
		var opts = getShoeOptions();
		updateShoe(opts);
	});
	var $radioes = $("input:radio");
	$radioes.on("change", function(){
		var opts = getShoeOptions();
		updateShoe(opts);
	});

	//new add this sort function
	var $sort = $(".sort");

	$sort.on("click", function(){
		//var opts=[];
		var opts = getShoeOptions();
		updateShoe(opts);
	});




	updateShoe();

    </script>

Thank you for your help!!

James_Hibbard · June 18, 2014, 6:36am

Hey,

I don’t really understand your question, but this should work for you:

function makeTable(data){
  var tbl_body = "";
  $.each(data, function() {
    var tbl_row = "";
    $.each(this, function(k, v) {
      tbl_row += "<td>"+v+"</td>";
    })
    tbl_body += "<tr>"+tbl_row+"</tr>"; 
  })
  return tbl_body;
}

function getShoeOptions(){
  var opts = [];
  $checkboxes.each(function(){
    if(this.checked){
      opts.push(this.value);
    }

  });
  $radioes.each(function(){
    if(this.checked){
      opts.push(this.value);
    }
  });
  return opts;
}

function updateShoe(opts){
  $.ajax({
    type: "POST",
    url: "shoe2.php",
    dataType : 'json',
    cache: false,
    data: {filterOpts: opts},
    success: function(data){
      //console.log(data);
      $('#shoe tbody').html(makeTable(data));
    }
  });
}

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

var $radioes = $("input:radio");
$radioes.on("change", function(){
  var opts = getShoeOptions();
  updateShoe(opts);
});

//new add this sort function
var $sort = $(".sort");
$sort.on("click", function(){
  var opts = [];
  opts.push(this.id);
  updateShoe(opts);
});

updateShoe();

If I have misunderstood what you are asking, please rephrase your question

chungmx5 · June 18, 2014, 1:01pm

Hi Pullo,

I uploaded the test page to server, it’s easy to understand what I said.

If I checked the brand and radio button, it’s run perfect. but when I click the sort area(A to Z), the table will show all records.

http://www.dunya.com.hk/filtertest/shoe3.html

Therefore, I tried made the shoe4.html (http://www.dunya.com.hk/filtertest/shoe4.html), the script is changed as below


    <script>
	function makeTable(data){
	  var tbl_body = "";
	  $.each(data, function() {
		var tbl_row = "";
		$.each(this, function(k, v) {
		  tbl_row += "<td>"+v+"</td>";
		})
		tbl_body += "<tr>"+tbl_row+"</tr>"; 
	  })
	  return tbl_body;
	}
 
	function getShoeOptions(){
		var opts = [];
		$checkboxes.each(function(){
			if(this.checked){
				opts.push(this.value);
			}
		
		});
		$radioes.each(function(){
			if(this.checked){
				opts.push(this.value);
			}
		});

		$sort.one(function(){
			if(this.clicked){
				opts.push(this.id);
			}
		});

		return opts;
	}



	function updateShoe(opts){
		$.ajax({
			type: "POST",
			url: "shoe2.php",
			dataType : 'json',
			cache: false,
			data: {filterOpts: opts},
			success: function(data){
				$('#shoe tbody').html(makeTable(data));
console.log(data);
			}
		});
	}

	var $checkboxes = $("input:checkbox");
	$checkboxes.on("change", function(){
		var opts = getShoeOptions();
		updateShoe(opts);
	});
	var $radioes = $("input:radio");
	$radioes.on("change", function(){
		var opts = getShoeOptions();
		updateShoe(opts);
	});


	var $sort = $(".sort");

	$sort.on("click", function(){
		var opts = getShoeOptions();
		updateShoe(opts);
	});

I want put the sort click to function getShoeOptions, but not effect.

Thanks

James_Hibbard · June 23, 2014, 11:34am

Hi,

I have understood your problem to be the following:

When you filter your data set by brand or by price, then everything works fine.
However, when (in a second step) you try to sort the filtered records (either alphabetically or by price), the filter is ignored and all of the records in your database are displayed.
This behaviour is incorrect. You only want to sort the already filtered records.

Could you let me know if I got that right.

chungmx5 · June 23, 2014, 12:09pm

Hi Pullo,

Yes, you are right.

Thanks,

chungmx5

James_Hibbard · June 23, 2014, 6:51pm

Ok, in that case, I would have thought that you could achieve that in your PHP script using:

ORDER BY column_name(s) ASC|DESC

chungmx5 · June 24, 2014, 12:00pm

Sorry! I don’t understand how should I change the script.

Could you please help me?


<?php 
	$pdo = new PDO('mysql:host=localhost;dbname=filtertest', 'root', '248633');
	$select = 'SELECT id,brand,pPrice';
	$from = ' FROM shoetest';
	$where = ' WHERE TRUE';
  
	$opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
	
	//brand
	$allBrands = array('Adidas', 'New Balance', 'PUMA');
	$allPrice = array('1','2','3','4','5','6','7');

	$selectedBrands = array();
	foreach ($allBrands as $brand) {
		if (in_array($brand, $opts)) {
			$selectedBrands[] = $brand;
		}
	}

	if (count($selectedBrands)) {
		$where .= " AND brand IN ('".implode("', '", $selectedBrands)."')";
	}
	
	//price
	if (in_array(1, $opts)){
	$where .= " AND pPrice>=0 AND pPrice<=100";
	}
	else if (in_array(2, $opts)){
	$where .= " AND pPrice>=101 AND pPrice<=150";
	}

	if (in_array("atoz", $opts)){
	$order .= " ORDER BY brand ASC";
	}
	else if (in_array("ztoa", $opts)){
	$order .= " ORDER BY brand DESC";
	}
	else if (in_array("htol", $opts)){
	$order .= " ORDER BY pPrice DESC";
	}	
	else if (in_array("ltoh", $opts)){
	$order .= " ORDER BY pPrice ASC";
	}
	else {
	$order .= " ORDER BY pPrice ASC";
	}


	$sql = $select . $from . $where . $order;
	$statement = $pdo->prepare($sql);
	$statement->execute();
	$results = $statement->fetchAll(PDO::FETCH_ASSOC);
	$json = json_encode($results);
	echo($json);
?>

James_Hibbard · June 29, 2014, 2:01pm

OK then. It’s relatively simple.
You have:

$sort.on("click", function(){
  var opts = [];
  opts.push(this.id);
  updateShoe(opts);
});

With this code, every time one of the secondary filter options is selected (such as sort A-Z), you are passing your PHP script an array, which contains the id filter option, e.g.:

["atoz"]

or

["ztoa"]

As you are not passing the selected primary filter options to the script (e.g. the brand), it is returning all records sorted accordingly.

To remedy this, instead of initializing your opts variable to an empty array, initialize it to contain the correct filter values:

var $sort = $(".sort");
$sort.on("click", function(){
  var opts = getShoeOptions();
  opts.push(this.id);
  updateShoe(opts);
});

And that should work as expected.

chungmx5 · July 2, 2014, 7:08am

Hi Pullo,

It’s works.

thanks a lot.

chungmx5

chungmx5 · July 12, 2014, 2:55am

Hi Pullo,

one more question. If the data is Chinese characters, the result will echo “???”.

How can I change the data to normal?

Thanks,

Chungmx5

James_Hibbard · July 12, 2014, 5:26am

Hi,

Make sure you store the characters in the database with an appropriate encoding.
This should help: http://www.haidongji.com/2007/10/04/character-set-and-collation-for-simplified-chinese-mysql/

chungmx5 · July 14, 2014, 12:28pm

Hi Pullo,

I added the character code when connect the PDO, the problem is fixed.

Thanks a lot,

chungmx5

Stomme_poes · July 14, 2014, 2:26pm

Hey Pullo (or anyone),
I have kinda an unrelated question:
if the db table is created like this
id int(11) NOT NULL AUTO_INCREMENT,

then does a number actually need to be added to values here:
INSERT INTO shoetest (id, brand, pPrice) VALUES
(1, ‘Adidas’, 105),
(2, ‘Adidas’, 130),
(3, ‘PUMA’, 115),
etc?

With auto-increment the db makes new ids for you, right? Or does that depend on the db, or is it assumed that one should try to set numbers anyway and auto-increment is more of a sort of backup, or…?

Could you do
INSERT INTO shoetest VALUES (‘Adidas’, 105), ( ‘Adidas’, 130), ( ‘PUMA’, 115), etc ;

Also, I thought the table name didn’t have to be a string? Or again is that because of this particular db? (I dunno what the OP used but I thought Sitepoint does tutorials in MySQL).

I’m learning SQL but the lessons are based on sqlite3, not mySQL or anything.

rpkamp · July 14, 2014, 6:44pm

Hi Mallory,

You are right that if you leave out the value of an auto increment field the database will fill it for you. At least this goes for MySQL and sqlite auto increments.
If however you supply a value yourself the database will use that one instead of the next auto increment value. There aren’t a lot of use cases for this (I can’t think of a single one off the top of my head) and I wouldn’t recommend it either.
Table names don’t need to be quoted, but you can do that if you wish to be explicit or if your table name is a reserved word (like “order”) (again, I wouldn’t recommend that).

AJAX filter problem

– – Dumping data for table shoetest

–
– Dumping data for table `shoetest`