EXT JS Passing Live Data - Working More Closely with the Back End
(Page 4 of 4 )
After playing around with the page for a few minutes, one thing should become painfully apparent: the price column doesn’t seem to sort the rows correctly, or rather it does sort them correctly, just not in the way that we expect. There is an easy way to fix this however, through the use of another property of the JsonStore. If we enable remote sorting, we can rely on MySQL to sort that data for us whenever a column is sorted. Change the JsonStore configuration so that it appears as follows:
//create data store
var dataStore = new Ext.data.JsonStore({
url:'productSearch.php',
root:'products',
fields:['title', 'image', 'inStock', 'price', 'category', 'manufacturer'],
remoteSort:true
});
Now, whenever a column is sorted, the page will be requested from the server again (asynchronously of course) with additional parameters passed to the PHP file. The parameters passed are the title of the column that was sorted and either ASC or DESC depending on the type of sort. The JsonStore only provides this client-side functionality, so we’ll need to update the back end PHP code to look for these parameters and return the data in the correct way. Change productSeacrh.php so that it appears as follows:
<?php
//set content-type header
header('Content-type: application/json');
//connection information
$host = "localhost";
$user = "root";
$password = "yourPasswordHere";
$database = "shop";
//get sort paramters if present
$sort = ($_REQUEST["sort"] == null) ? "" : $_REQUEST["sort"]." ".$_REQUEST["dir"];
//make connection
$server = mysql_connect($host, $user, $password) or die("Could not connect");
$connection = mysql_select_db($database, $server) or die("Could not select database");
//build query
$query = "SELECT * FROM products";
//add sort direction if not empty
if ($sort != "") {
$query.= " ORDER BY ".$sort;
}
//query database
$result = mysql_query($query);
//create json data structure
$startJsonObject = "{ products: [";
$endJsonObject = "]}";
//build json object with stuff from db
for ($x = 0; $x < mysql_num_rows($result); $x++) {
$row = mysql_fetch_assoc($result);
$data.= "{title:'".$row['title']."',image:'".$row['image']."',inStock:'".$row['inStock']."',price:'".$row['price']."',category:'".$row['category']."',manufacturer:'".$row['manufacturer']."'},";
}
//remove comma from final object in array
$noFinalComma = substr_replace($data, "", -1);
//output complete json object
echo $startJsonObject . $noFinalComma . $endJsonObject;
//close server connection
mysql_close($server);
?>
The first addition to the file is the $sort variable, which checks whether the $_REQUEST[“sort”] variable is null; if it is, an empty string is set as the value of $sort, but if it isn’t, the values of both $_REQUEST[“sort”] and $_REQUEST[“dir”] (there will either be both of them or neither of them) are set as the value of $sort.
A little further in the file there is a new if statement, which checks whether the $sort variable is empty. If it’s not empty, the MySQL command for sorting (ORDER BY) and the sort field and direction are appended to the $query variable. Save the file again and view it in your browser once more; thus when you sort by price, the data should sort correctly. Such is the power of the MySQL query:
Summary
In this stage of the tutorial we added the backend code required to get the data from the database and display it in the grid. We also learned how to work with additional parameters passed from the grid when remoteSort is enabled. In part three we’ll be looking at adding even more great functionality with the automatic pagination control.
-DOWNLOAD SOURCE-
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
