DataTables

I've been going through all the discussions on integrating DataTables with Wordpress and I can't seem to get the data to display properly. This is the perfect plugin for my project and I'm determined to get it to work and have spent the whole day troubleshooting. The closest I've come was using the discussion posted here:

http://www.datatables.net/forums/discussion/2317/installation-problem-with-datatables-server-side-processing-and-wordpress-3.0/p1

I followed the discussion and got nearly everything to work.

Here are some of the steps w/code that I've gotten through. If X's are there it's to keep it private.

1. I ran the server connection test (code below) and got back the proper number of rows I'm getting. When I ran the same test in my post, the results didn't print.

<?php
	$sTable = "XXXXXX";
	
	$gaSql['user']       = "XXXXXXXXX";
	$gaSql['password']   = "XXXXXXXXX";
	$gaSql['db']         = "XXXXXXX";
	$gaSql['server']     = "localhost:8888";
	
	
	$gaSql['link'] =  mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) or
		die( 'Could not open connection to server: '.mysql_error() );
	
	mysql_select_db( $gaSql['db'], $gaSql['link'] ) or 
		die( 'Could not select database '. $gaSql['db'].": ".mysql_error() );
	
	$result = mysql_query( "SELECT * FROM ".$sTable, $gaSql['link'] ) 
		or die ("Couldn't select from database: ".mysql_error() );
		
	echo mysql_num_rows( $result )." rows selected";
?>

I'm not sure if this has anything to do with it but if I visit the server_processing.php page it gives a notice but also returns the proper array:

Notice: Undefined index: sEcho in /Users/XXXXXXXXX/Sites/XXXXXXXX/wordpress/dataTables-1.8.2/examples/server_side/scripts/server_processing.php on line 151
{"sEcho":0,"iTotalRecords":"1","iTotalDisplayRecords":"1","aaData":[["20081104 GA 001 - Appling-1A","2008-11-04 20:00:00","0.00","0.00","0.00"]]}

2. Adjusted the server_processing.php file using the PHP MySQL version:

<?php
	/*
	 * Script:    DataTables server-side script for PHP and MySQL
	 * Copyright: 2010 - Allan Jardine
	 * License:   GPL v2 or BSD (3-point)
	 */
	
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
	 * Easy set variables
	 */
	
	/* Array of database columns which should be read and sent back to DataTables. Use a space where
	 * you want to insert a non-database field (for example a counter or static image)
	 */
	$aColumns = array( 'XXXXXXX', 'XXXXXXXXX', 'XXXXXXXXX', 'XXXXXXXXX', 'XXXXXXXXX' );
	
	/* Indexed column (used for fast and accurate table cardinality) */
	$sIndexColumn = "id";
	
	/* DB table to use */
	$sTable = "XXXXXXXXXXX";
	
	/* Database connection information */
	$gaSql['user']       = "XXXXXXXXXXX";
	$gaSql['password']   = "XXXXXXXXXXX";
	$gaSql['db']         = "XXXXXXXXXXX";
	$gaSql['server']     = "localhost:8888";
	
	
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
	 * If you just want to use the basic configuration for DataTables with PHP server-side, there is
	 * no need to edit below this line
	 */
	
	/* 
	 * MySQL connection
	 */
	$gaSql['link'] =  mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) or
		die( 'Could not open connection to server' );
	
	mysql_select_db( $gaSql['db'], $gaSql['link'] ) or 
		die( 'Could not select database '. $gaSql['db'] );
	
	
	/* 
	 * Paging
	 */
	$sLimit = "";
	if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
	{
		$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
			mysql_real_escape_string( $_GET['iDisplayLength'] );
	}
	
	
	/*
	 * Ordering
	 */
	$sOrder = "";
	if ( isset( $_GET['iSortCol_0'] ) )
	{
		$sOrder = "ORDER BY  ";
		for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
		{
			if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
			{
				$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
				 	".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
			}
		}
		
		$sOrder = substr_replace( $sOrder, "", -2 );
		if ( $sOrder == "ORDER BY" )
		{
			$sOrder = "";
		}
	}
	
	
	/* 
	 * Filtering
	 * NOTE this does not match the built-in DataTables filtering which does it
	 * word by word on any field. It's possible to do here, but concerned about efficiency
	 * on very large tables, and MySQL's regex functionality is very limited
	 */
	$sWhere = "";
	if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
	{
		$sWhere = "WHERE (";
		for ( $i=0 ; $i<count($aColumns) ; $i++ )
		{
			$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
		}
		$sWhere = substr_replace( $sWhere, "", -3 );
		$sWhere .= ')';
	}
	
	/* Individual column filtering */
	for ( $i=0 ; $i<count($aColumns) ; $i++ )
	{
		if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
		{
			if ( $sWhere == "" )
			{
				$sWhere = "WHERE ";
			}
			else
			{
				$sWhere .= " AND ";
			}
			$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
		}
	}
	
	
	/*
	 * SQL queries
	 * Get data to display
	 */
	$sQuery = "
		SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
		FROM   $sTable
		$sWhere
		$sOrder
		$sLimit
	";
	$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
	
	/* Data set length after filtering */
	$sQuery = "
		SELECT FOUND_ROWS()
	";
	$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
	$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
	$iFilteredTotal = $aResultFilterTotal[0];
	
	/* Total data set length */
	$sQuery = "
		SELECT COUNT(".$sIndexColumn.")
		FROM   $sTable
	";
	$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
	$aResultTotal = mysql_fetch_array($rResultTotal);
	$iTotal = $aResultTotal[0];
	
	
	/*
	 * Output
	 */
	$output = array(
		"sEcho" => intval($_GET['sEcho']),
		"iTotalRecords" => $iTotal,
		"iTotalDisplayRecords" => $iFilteredTotal,
		"aaData" => array()
	);
	
	while ( $aRow = mysql_fetch_array( $rResult ) )
	{
		$row = array();
		for ( $i=0 ; $i<count($aColumns) ; $i++ )
		{
			if ( $aColumns[$i] == "version" )
			{
				/* Special output formatting for 'version' column */
				$row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
			}
			else if ( $aColumns[$i] != ' ' )
			{
				/* General output */
				$row[] = $aRow[ $aColumns[$i] ];
			}
		}
		$output['aaData'][] = $row;
	}
	
	echo json_encode( $output );
?>

2. Added the header info and table to the HTML on the Wordpress post. Here's the page source code through to the table:

<script type="text/javascript" src="http://localhost:8888/dataTables-1.8.2/media/js/jquery.dataTables.js">				$(document).ready(function() {
					$('#example').dataTable( {
						"bProcessing": true,
						"bServerSide": true,
						"sAjaxSource": "dataTables-1.8.2/examples/server_side/scripts/server_processing.php"
					} );
				} );
</script>

<table id="example" class="display" border="0" cellspacing="0" cellpadding="0">
<thead>
<tr>
<th width="20%">Column 1</th>

<th width="25%">Column 2</th>
<th width="25%">Column 3</th>
<th width="15%">Column 4</th>
<th width="15%">Column 5</th>
</tr>
</thead>
<tbody>
<tr>
<td class="dataTables_empty">Cell 1</td>
<td>Cell 2</td>
<td>Cell 3</td>

<td>Cell 4</td>
<td>Cell 5</td>
</tr>
</tbody>
<tfoot>
<tr>
<th>Name</th>
<th>Election Date</th>
<th>Ballot</th>
<th>Registration</th>
<th>Votes 1</th>

</tr>
</tfoot>
</table>

[/code]

Please help when you can. Your plugin is great and will be an excellent tool for my project.

Thanks!