Building a Pageable and Sortable Ajax Enabled DataGrid with PHP5 and PDO

Written By: Jay

- 20 Dec 2006 -
















Description: This tutorial explains how to build a dynamic data grid for browsing data. This is not a perfect data grid solution that can be just dropped onto a page, it requires a bit of hands on tweaking, but the end result is pretty amazing.

  1. Setting Up AjaxAgent
  2. Building the Data Grid
  3. Data Grid Navigation

Part 2: Building the Data Grid

This is where things get a little harder so stay sharp. The ajaxDataGrid.php contains 5 functions that make up the datagrid.

The only function called by us is showDataGrid which we will pass some parametes the JavaScript function returnPageData. All of the other functions are called by showDataGrid so we do not need to worry about them just yet.

Part 2.1: showDataGrid

This is the main function that does all of the work for us:

function showDataGrid($setoffset,$setorderby,$setdir){
        //create an array of columns
        //columnName is the SQL column name
        //columnDisplay is the friendly column display name
        $columns[0] = array("columnID"=>"0","columnName"=>"ProjName","columnDisplay"=>"Name");
        $columns[1] = array("columnID"=>"1","columnName"=>"ProjDesc","columnDisplay"=>"Description");
        
        //set some defaults
        $offset = 0;
        $per_page = 5;
        $column = $columns[0]; 
        $orderby = $column['columnName'];      
        $dir = 'ASC'; 
        
        if ($setoffset > 0) {
                $offset = $setoffset;                                                         
        }             
        if ($setorderby != '') {
                $orderby = $setorderby;                                                               
        }             
        if ($setdir != '') {
                $dir = $setdir;                                                               
        }                             
        //get some data
        $projects = getProjects($offset,$per_page,$orderby,$dir);
        //display the data navigation
        $html .= pc_indexed_links(count(getProjects()),$offset,$per_page,$orderby,$dir);
        //display the datagrid
        $html .= phpDataGrid($projects,$columns,$offset,$per_page);
        //echo the results
        echo $html;           
}

Part 2.2: getProjects & SQL

For this tutorial I am using my Projects DataBase from the gotfoo labs:

Here is a cut-down version of the Projects Table with some default data.

CREATE TABLE 'tblProjects' (
  'ProjID' int(11) NOT NULL AUTO_INCREMENT,
  'ProjName' varchar(100) NOT NULL DEFAULT '',
  'ProjDesc' text,
  'DateUpdated' datetime DEFAULT NULL,
  PRIMARY KEY  ('ProjID')
) TYPE=InnoDB AUTO_INCREMENT=13;
 
INSERT INTO 'tblProjects' VALUES (1, 'Project One', 'Project One Description', '2006-11-10 10:04:36');
INSERT INTO 'tblProjects' VALUES (2, 'Project Two', 'Project Two Description', '2006-11-10 10:04:36');
INSERT INTO 'tblProjects' VALUES (3, 'Project Three', 'Project Three Description', '2006-11-10 10:04:36');
INSERT INTO 'tblProjects' VALUES (4, 'Project Four', 'Project Four Description', '2006-11-10 10:04:36');
INSERT INTO 'tblProjects' VALUES (5, 'Project Five', 'Project Five Description', '2006-11-10 10:04:36');
INSERT INTO 'tblProjects' VALUES (6, 'Project Six', 'Project Six Description', '2006-11-10 10:04:36');
INSERT INTO 'tblProjects' VALUES (7, 'Project Seven', 'Project Seven Description', '2006-11-10 10:04:36');
INSERT INTO 'tblProjects' VALUES (8, 'Project Eight', 'Project Eight Description', '2006-11-10 10:04:36');
INSERT INTO 'tblProjects' VALUES (9, 'Project Nine', 'Project Nine Description', '2006-11-10 10:04:36');
INSERT INTO 'tblProjects' VALUES (10, 'Project Ten', 'Project Ten Description', '2006-11-10 10:04:36');
INSERT INTO 'tblProjects' VALUES (11, 'Project Eleven', 'Project Eleven Description', '2006-11-10 10:04:36');
INSERT INTO 'tblProjects' VALUES (12, 'Project Twelve', 'Project Twelve Description', '2006-11-10 10:04:36');
INSERT INTO 'tblProjects' VALUES (13, 'Project Thirteen', 'Project Thirteen Description', '2006-11-10 10:04:36');

And here is the PHP code that gets the Project Data:

function getProjects($offset='',$per_page='',$orderby='',$dir='') {
        //get a connection    
        $mysql = new PDO('mysql:host=[HOST];dbname=[DATABASE]','[USERNAME]','[PASSWORD]');
        
        //set the ORDER BY clause with a direction
        if($orderby != ''){
                $strSQLExtra .="  ORDER BY ".$orderby." ".$dir;
        }
        //set the LIMIT and OFFSET
        if($offset > 0) {
                $strSQLExtra .= " LIMIT ".$per_page." OFFSET ".($offset-1);
        }else if($per_page > 0){
                $strSQLExtra .= " LIMIT ".$per_page;
        }else{
                $strSQLExtra = '';
        }
                        
        //the SQL
        $strSQL = "SELECT * FROM tblProjects ".$strSQLExtra;
        //echo $strSQL;
        
        $projects = array();
        
        //execute the sql and bind it to an array
        foreach( $mysql->query( $strSQL ) as $row ){
                $projects[$row['ProjID']] = $row;
        }
        
        $mysql = null;
        
        //return the data
        return $projects;                     
}

The most important parameters are the $per_page which sets the LIMIT and the $offset which sets the position to start gathering data at.

<< Previous

Next >>