Today I want to speak about populating datagrid techniques with PHP. At least in my daily work datagrids and tabular data are very common, because of that I want to show two different techniques when populating datagrids with data from our database. Maybe it’s obvious, but I want to show the differences. Let’s start.
Imagine we need to fetch data from our database and show it in a datagrid. Let’s do the traditional way. I haven’t use any framework for this example. Just old school spaghetti code.
$dbh = new PDO('pgsql:dbname=mydb;host=localhost', 'gonzalo', 'password');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->prepare('SELECT * FROM test.tbl1 limit 10');
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->execute();
$data = $stmt->fetchAll();
$table = "";
$table.= "<table>";
foreach ($data as $ow) {
$table.= "<tr>";
foreach ($ow as $item) {
$table.= "<td>{$item}</td>";
}
$table.= "</tr>";
}
$table.= "</table>";
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>inline grid</title>
</head>
<h1>inline grid</h1>
<body>
<?php echo $table; ?>
<script type="text/javascript" src=" https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
</body>
</html>
And that’s all. The code works, and we’ve got or ugly datagrid with the data from our DB. Where’s the problem? If our “SELECT” statement is fast enougth and our connection to the database is good too, the page load will be good, indeed. But what happens if or query is slow (or we even have more than one)? The whole page load will be penalized due to our slow query. The user won’t see anything until our server finish with all the work. That means bad user experience. The alternative is load the page first (without populated datagrid, of course) and when it’s ready, we load with ajax the data from the server (JSON) and we populate the datagrid with javaScript.
Page without the populated datagrid:
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>grid ajax</title>
</head>
<h1>grid ajax</h1>
<body>
<table id='grid'></table>
<script type="text/javascript" src=" https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
<script type="text/javascript">
$(function(){
$.getJSON("json.php", function(json){
for (var i=0;i<json.length;i++) {
$('#grid').append("<tr><td>" + json[i].id + "</td><td>" + json[i].field1 + "</td></tr>")
}
});
});
</script>
</body>
</html>
JSON data fron the server:
$dbh = new PDO('pgsql:dbname=mydb;host=localhost', 'gonzalo', 'password');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->prepare('SELECT * FROM test.tbl1 limit 10');
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->execute();
$data = $stmt->fetchAll();
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Content-type: application/json');
echo json_encode($data);
The outcome of this second technique is the same than the first one, but now user will see the page faster than the first technique and data will load later. Probably the total time to finish all the work is better in the classical approach, but the UX will be better with the second one. Here you can see the times taken from Chorme’s network window:
Even though the total time is better in the inline grid example: 156ms vs 248ms, 1 http request vs 3 HTTP request. The user will see the page (without data) faster with the grid data example.
What’s better. As always it depends on our needs. We need to balance and choose the one that fits with your requirements.
Probably the JavaScript code that I use to populate the datagrid in the second technique could be written in a more efficient way (there’s also plugins to do it). I only want to show the indirect way to create HTML to improve the user experience of our applications.
Gonzalo Ayuso - Web Architect 
I think that we canuse the best of the two world , I used to use a technique that I called Json Precharge, instead of doing the first ajax call, we write the json data directly on the webpage as a javascript global variable, so we save ourselves an Ajax Call, but if we need to refresh our grid, we just then, cal an Ajax that servers us the data. I agree with you that the data should not be hardcoded, but I think that we can save ourselves from the first ajax call.
Here is the technique explained: http://designbyjeeba.blogspot.com/2008/10/1-way-to-use-json-in-crazy-way-json.html
You should get an eye on datatables.net
That’s the point! The use of those kind of grids with JSON data. Normally those js grids allows JSON data as input and also transforms html table into a fancy datagrid.
Hi, I’ve been working with data grids, more related to Dojo than to JQuery.
I believe that the idea is not only to make the request through/in ajax/json format but also to buffer the results. By that said, the grid must be capable of send a ajax request specifying which results would like to show (similiar to pagination, but with scroll instead of pages).
use datatables.net, there is prebuilt ajax/json functionality