PDO version of the server side php example?

mihomes

Has anyone converted this to PDO? I found something on github, but the connection method was a little strange and the searching did not work. If anyone has anything please message me - thanks!

allan

The scripts have been updated for v1.10 (nearly beta) to use PDO: https://github.com/DataTables/DataTablesSrc/tree/1_10_wip/examples/server_side/scripts . Note thought that these scripts are not compatible with 1.9-...

Allan

mihomes

Thanks Allan, but I am using 1.9...

mihomes

I came across this and everything works fine, BUT the searchbox. I am using PDO::ATTR_EMULATE_PREPARES => false on my db connection which causes the error. Removing that setting and it works fine. Note that I modified the original to inject my db connection variables into the class.

[code]
<?php

/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2012 - John Becker, Beckersoft, Inc.
* Copyright: 2010 - Allan Jardine
* License: GPL v2 or BSD (3-point)
*/

define('INCLUDE_CHECK',true);

// These files can be included only if INCLUDE_CHECK is defined
require '/home/stephen/public_html/assets/functions/connect.php';

//inject bd connection into class
class TableData {
/** @var \PDO */
protected $_db;

public function __construct(\PDO $_db) {
$this->_db = $_db;
}

public function get($table, $index_column, $columns) {

// Paging
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".intval( $_GET['iDisplayLength'] );
}

// Ordering
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) ) {
$sOrder = "ORDER BY ";
for ( $i=0 ; $ibindValue(':search', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
}
for ( $i=0 ; $ibindValue(':search'.$i, '%'.$_GET['sSearch_'.$i].'%', PDO::PARAM_STR);
}
}

$statement->execute();
$rResult = $statement->fetchAll();
//close cursor
$statement->closeCursor();

$iFilteredTotal = current($this->_db->query('SELECT FOUND_ROWS()')->fetch());

// Get total number of rows in table
$sQuery = "SELECT COUNT(`".$index_column."`) FROM `".$table."`";
$iTotal = current($this->_db->query($sQuery)->fetch());

// Output
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);

// Return array of values
foreach($rResult as $aRow) {
$row = array();
for ( $i = 0; $i < count($columns); $i++ ) {
if ( $columns[$i] == "version" ) {
// Special output formatting for 'version' column
$row[] = ($aRow[ $columns[$i] ]=="0") ? '-' : $aRow[ $columns[$i] ];
}
else if ( $columns[$i] != ' ' ) {
$row[] = $aRow[ $columns[$i] ];
}
}
$output['aaData'][] = $row;
}

echo json_encode( $output );
}

}

header('Pragma: no-cache');
header('Cache-Control: no-store, no-cache, must-revalidate');

// Create instance of TableData class
//$table_data = new TableData();
$table_data = new TableData($db);

// Get the data
//$table_data->get('table_name', 'index_column', array('column1', 'column2', 'columnN'));
$table_data->get('accounts', 'account_id', array('account_id', 'account_username', 'account_password', 'account_email'));

?>

[/code]