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.3: Data Grid Navigation

I'll admit right up front that I found the data navigation code in the but I modified the code to allow for the $orderby, $dir and $per_page parameters.

function pc_indexed_links($total,$offset,$per_page,$orderby,$dir) {
        $separator = ' | ';
        $html = '';
        
        //extra parameters
        $urlParams = array("orderby"=>$orderby,"dir"=>$dir,"perpage"=>$per_page);
        
        // print "<
        $html .= pc_print_link($offset == 1, '<< Prev', $offset - $per_page,$urlParams);
        
        // print all groupings except last one
        for ($start = 1, $end = $per_page;
             $end < $total;
             $start += $per_page, $end += $per_page) {
                 $html .= $separator;
                 $html .= pc_print_link($offset == $start, "$start-$end", $start,$urlParams);
        }             
        
        $end = ($total > $start) ? "-$total" : '';
        
        $html .= $separator;
        $html .=pc_print_link($offset == $start, "$start$end", $start,$urlParams);
        
        // print "Next>>" link
        $html .= $separator;
        $html .=pc_print_link($offset == $start, 'Next >>',$offset + $per_page,$urlParams);
 
        return $html;
}
 
function pc_print_link($inactive,$text,$offset='',$urlParams=null){
        $html = '';
        if($inactive) {
                $html .= "".$text."\n";       
        } else {
                $html .= "\n";   
                $html .= ".$offset.",'".$urlParams['orderby']."','".$urlParams['dir']."',".$urlParams['perpage'].");return false'>$text\n";                   
        }
        return $html;
}

Part 2.4: The Data Grid

This is the heart and soul of the whole matter.

For the PHP function phpDataGrid we will pass in 4 parameters: and array of data, an array of columns, the page offset, and the number of records per page.

In the first for loop we are looping over the $columns array to create the columns & headers as well as links to sort the column.

Once we are past that we have a foreach loop which a) loops over the $dataobjects array and then loops over the columns. In the column loop we are using the $column['columnName'] a.k.a the SQL column name to get a value of the $dataobjects array.

function phpDataGrid($dataobjects,$columns,$offset,$per_page){
        
        if($dataobjects == null){
                return "No Data";
        }     
        $html .= "";
        $html .= "";
        
        //loop over the $columns array to create the columns & headers as well as links to sort the column for($i=0;$i($columns);$i++){
                
                $column = $columns[$i];                
                $html .= "";
                
        }$html .= "";
        $count = 0;
        foreach($dataobjectsas$object){if($count % 2){$bgcolor = '#EFEFEF';                                                 
                        }else{$bgcolor = '#ffffff';
                        }$html .= "$bgcolor.">";
                for($i=0;$i($columns);$i++){
                        $column = $columns[$i];                
                        $html .= "";
                        
                }$html .= "";
                $count++;
        }$html .= "

        ";
                $html .= $column['columnDisplay'];
                $html .= " .$offset.",'".$column['columnName']."','ASC',".$per_page.");return false'>+";
                $html .=" / ";
                $html .= ".$offset.",'".$column['columnName']."','DESC',".$per_page.");return false'>- ";
                $html .= "
        
  
                 
                                  
                                
                
";
                        $html .= stripslashes($object[$column['columnName']]);
                        $html .= "
                

        
        
";
        return $html;
}

Once all of the rows have been processed we're done and we return the DataGrid back to the calling function, showDataGrid.

Part 2.5: Very Important Quirk/Hack

I went crazy this week because I had AgaxAgent working on one site but not another. I spent hours trying to get the DataGrid to work. Then I found the answer.

At the very bottom of ajaxDataGrid.php we need to include the AjaxAgent library and initialize it again. I don't know but if it's not there you'll get nothing but errors.

include_once("agent.php");         
$agent->init();

Part 3: We're done!!

<< Previous