EXT JS Passing Live Data

For reference, PHP is not the only server-side language that could be used to accomplish this task, but I’ve chosen it because it’s free, powerful and easy to use. Alternatives include Java and ASP.NET, among others.

Some PHP

In a new file in your text editor, then, add the following code:

<?php

// set content-type header

header('Content-type: application/json');

// connection information

$host = "localhost";

$user = "root";

$password = "yourPasswordHere";

$database = "shop";

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

//query databse

$result = myql_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);

?>

Save this as productSearch.php. The first thing we do in this file is set the content-type header to application/json. Next we define the information needed to access the MySQL database, which consists of the host and usernames, and the password and database name.

Following this we attempt to connect and try to select the specified database. If this is possible, we then select all of the data in the database. So far, all of this is pretty standard stuff, but it soon starts to get interesting.

We want our JSON object to have a specific structure, as it must match the structure of the object that our JsonStore is expecting. We first define the beginning and end of the JSON object as strings. We then use the information contained in our query (which will be a series of rows containing our data, again very much like an associate array). For each row in the query results we add a new item to our JSON array, using text to specify the opening and closing curly braces and the object properties, and using references to our data as the property values.

The next part of the code isn’t strictly necessary, but I always prefer to be as tidy as possible. Because each item within our JSON array will be exactly the same, the last item will be followed by a comma separator, even though it doesn’t actually require one. To tidy things up we can easily remove it using the substr PHP function.

Now that we have all of the data within our JSON object, we can build the object using the start and end strings and the data from the database. This is passed back to the client using a simple echo, but because we set the content type header to JSON it will be recognized and treated as a JSON object instead of a simple string. We then close the database connection, as we have the data we need. You should note that PHP5 has functions for building JSON objects built in to it. I’ve chosen to use strings in this example because it’s probably the quickest and simplest way to get the object in the structure that we want.