EXT JS Passing Live Data

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-