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 PHP Cookbook 2ed 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 "<<Prev" link
	$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 .= "<span class='inactive'>".$text."</span>\n";	
	} else {
		$html .= "<span class='active'>\n";	
		$html .= "<a href='#' OnClick=returnPageData(".$offset.",'".$urlParams['orderby']."','".$urlParams['dir']."',".$urlParams['perpage'].");return false'>$text</a></span>\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 .= "<table border='0'  width='90%'>";
	$html .= "<tr bgcolor = '#EFEFEF'>";
	
	//loop over the $columns array to create the columns & headers as well as links to sort the column 
	for($i=0;$i<count($columns);$i++){
		
		$column = $columns[$i];		
		$html .= "<td width='30%'><b>";
		$html .= $column['columnDisplay'];
		$html .= " <a href='#' OnClick=returnPageData(".$offset.",'".$column['columnName']."','ASC',".$per_page.");return false'>+</a>";
		$html .=" / ";
		$html .= "<a href='#' OnClick=returnPageData(".$offset.",'".$column['columnName']."','DESC',".$per_page.");return false'>-</a> ";
		$html .= "</b></td>";
		
	}
	$html .= "</tr>";
	$count = 0;
	foreach($dataobjects as $object){
		if($count % 2) {
				$bgcolor = '#EFEFEF';							
			} else {
				$bgcolor = '#ffffff';
			}
		$html .= "<tr valign=top bgcolor=".$bgcolor.">";
		for($i=0;$i<count($columns);$i++){
			$column = $columns[$i];		
			$html .= "<td>";
			$html .= stripslashes($object[$column['columnName']]);
			$html .= "</td>";
			
		}
		$html .= "</tr>";
		$count++;
	}
	
	$html .= "</table>";
	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