EXT JS: the Paging Toolbar and Live Data

We’ll need to adjust our PHP file to cater to the new information passed back to it by the paging control. Open productSearch.php and change it so that it appears as follows (I’ve shown the whole PHP file here rather than just the new changes so that you can see exactly where everything should go):

<?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"];

//get start and limit if present

$start = ($_REQUEST["start"] == null)? 0 : $_REQUEST["start"];

$count = ($_REQUEST["limit"] == null)? 20 : $_REQUEST["limit"];

//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;

}

//add start and limit

$query.= " LIMIT ".$start.",".$count;

//query database

$result = mysql_query($query);

//get total

$total = mysql_query("SELECT COUNT(title) FROM products");

$total = mysql_result($total, 0);

//create json data structure

$startJsonObject = "{ total:'".$total."',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 new additions to the code are the $start and $count variables, which are used to either store the parameters received from the toolbar, or to set defaults if no parameters are sent. When the page initially loads, no parameters are sent, so the defaults specified here in the PHP are used instead to send back the first 20 records (the default number of rows shown in the grid with the PagingToolbar enabled).

Whenever the next icon is clicked, the toolbar sends the start and limit parameters, so when moving from page 1 to page 2, the start parameter will equal 20 (and the limit parameter will equal 20 also). When page three is requested, the start parameter will equal 40 and so on and so forth.

In the same way that we concatenated the $sort variable into our query string, we also need to add in the $start and $count variables. As these will be used each time the file is called, we don’t need to worry about checking their contents before adding it to our query; we can just concatenate it on just before the query is used.

Once we have run the query, we then need to create our $total variable so that this can be added to the JSON object and passed back to the store. There are two steps to getting the final result in this variable. First we get all of the records in the database by counting the total number of one of our fields (the title field in this example), which is obtained using a standard mysql_query command. Following this, the actual numerical total is extracted using the mySQL_result command.

You’ll notice also that the string stored in the $startJsonObject variable has also changed; we now need to add our new total property as the first property in our object. This is added using exactly the same concatenation techniques as the rest of the object. This will then map correctly to the totalProperty property of our store. You should notice that when you run the page, the data is divided into two pages, which you can switch between using the UI provided by the toolbar: