Displaying JSON data from PHP script

What is JSON ??? If you want to know more about JSON, please visit this link. And if you want know using JSON in Java Web, please visit link Adi Sembiring.

Now for this session, I want to share my expirience in using JSON for displaying JSON data from PHP script. For dispaling data ini browser we will use HTML, JavaScript, and framework jQuery. To encode data in to JSON in PHP we use Services_JSON.

For connection to database, I using PDO_MySQL. So you must ensure your PDO extension has been enabled in your PHP library. To make your result can be detected as JSON data you must add content-type: text/json in your return file header.

header("Expires: Mon, 31 Des 2010 23:59:59 GMT" );
header("Last-Modified: " . gmdate( "D, d M Y H:i:s" ) . "GMT" );
header("Cache-Control: no-cache, must-revalidate" );
header("Pragma: no-cache" );
header("Content-type: text/x-json");

This is the important things to make your result would be detected to be JSON data. Now how we want to make the result(array) from database into JSON data.

$json = new Services_JSON();
$json_data = array(
 name => $json_name,
 items => $employees
 );

$json_encode = $json->encode($json_data);

The variable $json_data will contain the result of the data. And we add a name, this just for to make the identity of this JSON data. The variable items will be inserted with the data from database.

In my database I have some tables, such as customers, employess, …. Now I will displaying data from employess table. For the rest you can do it.

/*
   File Name : Connection.php
   Author : Angga Lingga
*/

class Connection extends PDO{
    function Connection(){
        parent::__construct('mysql:host=' .DB_HOST .';dbname=' .DB_NAME, DB_USER, DB_PASS);
    }
}

/*
   File Name : init.php
   Author : Angga Lingga
*/

require_once 'Connection.php';

$conn = new Connection();
$json = new Services_JSON();

function getEmployess() {
	global $conn;
	$sql = "
		SELECT
			employeeNumber,
			lastName,
			firstName,
			extension,
			email,
			officeCode,
			reportsTo,
			jobTitle
		FROM
			employess
		ORDER BY
			employeeNumber DESC
	";

	$sth = $conn->prepare($sql);
	$sth->setFetchMode(PDO::FETCH_ASSOC);
	$sth->execute();
	$result = $sth->fetchAll();

	unset($sth);
	return($result) ? $result : false;
}

/*
   File Name : json_controller.php
   Author : Angga Lingga
*/

require_once 'init.php';

header("Expires: Mon, 31 Des 2010 23:59:59 GMT" );
header("Last-Modified: " . gmdate( "D, d M Y H:i:s" ) . "GMT" );
header("Cache-Control: no-cache, must-revalidate" );
header("Pragma: no-cache" );
header("Content-type: text/x-json");

$json_name = $_GET["name"];

if(isset($json_name))
	processRequest($json_name);

function processRequest($json_name) {
	global $json;

	if($json_name == "employees") {
		$employees = getEmployees();

		$json_data = array(
			name =>	$json_name,
			items => $employees
		);
	}

	print_r($json->encode($json_data));
}

After we get the JSON data, now how to display it in HTML page ??? For displaying we using HTML + JavaScript. We will use jQuery Framework, because if we make the JavaScript from ), it will take much times to develop it.

<script>
    $(document).ready(function() {
    	showEmployees();
    });

    function showEmployees() {
    	$.getJSON("json_controller.php", {name: "employees"},
    		function(data) {
        		if(data.name)
        			name = data.name;

    			data = "";
    			$.each(data.items, function(i, item){
	    			if(item) {
	    				data += '<tr>';
	    					data += '<td>' + item.employeeNumber+ '</td>';
		    				data += '<td>' + item.firstNumber + ' ' + item.lastName + '</td>';
		    				data += '<td>' + item.email + '</td>';
		    				data += '<td>' + item.extension + '</td>';
	    					data += '<td>' + item.officeCode + '</td>';
		    				data += '<td>' + item.reportsTo + '</td>';
		    				data += '<td>' + item.jobTitle + '</td>';
	    				data += '</tr>';

		    		   	$("#employees").html(data);
		    		   	$("#employees").fadeIn("slow");
	    			}
    			});
    	});
    }
</script>

/*
   File Name : index.html
   Author : Angga Lingga
*/

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>Blitz Report</title>
<link rel="stylesheet" type="text/css" href="css/style.css"/>
<script type="text/javascript" src="js/jquery-1.3.2.js"></script>
</head>
<body>
<script>
    <!-- From the above script ... -->
</script>
<table>
 <thead>
 <tr>
 <th>Employee Number</th>
 <th>Name</th>
 <th>Email</th>
 <th>Extension</th>
 <th>Office Code</th>
 <th>Reports To</th>
 <th>Job Title</th>
 </tr>
 </thead>
 <tbody id="employees">
 </tbody>
 </table>
</body>
</html>

Ok. This is the end of this session. I hope you will get more valuable result from my explanation. Thank you.