tiny contribution --> Server-side processing change into class | PHP with PDO
tiny contribution --> Server-side processing change into class | PHP with PDO
tiny contribution
Server-side script convert to class with pdo free to use
Example PDO
[code]
try{
$PDO = new PDO('mysql:host=localhost;dbname=mydbname','mylogin','mypassword');
$PDO->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
$PDO->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_OBJ);
}catch(PDOException $e){
echo 'Connexion impossible';
}
[/code]
The class
[code]
class Datable{
/*
* This will work with datable.js
* getdata(array(list of colomn into your database),'id',$_GET,'table name');
*
* example :
* $Datable = new Datable();
* echo $Datable->getdata(array('id','value'),'id',$_GET,'countries');
*/
function getdata($attributes = array(), $index=null, $get=array(), $table){
global $PDO;
$aColumns = $attributes;
if($index!=null){$sIndexColumn=$index;}else{$sIndexColumn = "ID";}
$sTable = $table;
/*
* Paging
*/
$sLimit = "";
if ( isset( $get['iDisplayStart'] ) && $get['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ". $get['iDisplayStart'] .", ".
$get['iDisplayLength'];
}
/*
* Ordering
*/
if ( isset( $get['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $ifetchAll();
$iFilteredTotal = $aResultFilterTotal[0];
/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$rResultTotal = $PDO->query( $sQuery );
$aResultTotal = $rResultTotal->fetchAll();
$iTotal = $aResultTotal[0];
/*
* Output
*/
foreach($iTotal as $k){}
foreach($iFilteredTotal as $j){}
$output = array(
"sEcho" => intval($get['sEcho']),
"iTotalRecords" => $k,
"iTotalDisplayRecords" => $j,
"aaData" => array()
);
/*
* Prepare data
*/
while ( $aRow = $rResult->fetch())
{
$b=0;
$row=array();
foreach ($aRow as $land) {
$b++;
$row[]=$land;
}
$output['aaData'][] = $row;
}
/*
* json encode reponse
*/
return json_encode( $output );
}
}[/code]
Server-side script convert to class with pdo free to use
Example PDO
[code]
try{
$PDO = new PDO('mysql:host=localhost;dbname=mydbname','mylogin','mypassword');
$PDO->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
$PDO->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_OBJ);
}catch(PDOException $e){
echo 'Connexion impossible';
}
[/code]
The class
[code]
class Datable{
/*
* This will work with datable.js
* getdata(array(list of colomn into your database),'id',$_GET,'table name');
*
* example :
* $Datable = new Datable();
* echo $Datable->getdata(array('id','value'),'id',$_GET,'countries');
*/
function getdata($attributes = array(), $index=null, $get=array(), $table){
global $PDO;
$aColumns = $attributes;
if($index!=null){$sIndexColumn=$index;}else{$sIndexColumn = "ID";}
$sTable = $table;
/*
* Paging
*/
$sLimit = "";
if ( isset( $get['iDisplayStart'] ) && $get['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ". $get['iDisplayStart'] .", ".
$get['iDisplayLength'];
}
/*
* Ordering
*/
if ( isset( $get['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $ifetchAll();
$iFilteredTotal = $aResultFilterTotal[0];
/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$rResultTotal = $PDO->query( $sQuery );
$aResultTotal = $rResultTotal->fetchAll();
$iTotal = $aResultTotal[0];
/*
* Output
*/
foreach($iTotal as $k){}
foreach($iFilteredTotal as $j){}
$output = array(
"sEcho" => intval($get['sEcho']),
"iTotalRecords" => $k,
"iTotalDisplayRecords" => $j,
"aaData" => array()
);
/*
* Prepare data
*/
while ( $aRow = $rResult->fetch())
{
$b=0;
$row=array();
foreach ($aRow as $land) {
$b++;
$row[]=$land;
}
$output['aaData'][] = $row;
}
/*
* json encode reponse
*/
return json_encode( $output );
}
}[/code]
This discussion has been closed.
Replies
Nice one - thanks for that :-). I do like PDO! I must have a look something and try to generalise the SQL being used in the script (SQL_CALC_FOUND_ROWS for example is MySQL specific), so that with PDO the database engine can be changed with a trivial initialisation change.
Allan
[code]
mysql_real_escape_string( $get['sSearch'] )
[/code] ?
One of the key reasons of using PDO (apart from flexible handling of various databases) is the ability to bind params which makes PDO quite secure against injection.
I have tested the output with JSONLint by simply calling the php file in my browser, and copying that output. According to JSONLint, it's valid JSON. I have even made some simple functions using getJSON to fetch data from that *exact* pdo.php, and suddenly no errors !
I will show you the pdo.php later on.
The database credentials :
[code]
<?php
$dbhost = 'localhost'; // MySQL Hostname
$database = 'test'; // MySQL Database
$db_username = ''; // MySQL Username
$db_pass = ''; // MySQL Password
$db_port = '3306';
define('dbHost', $dbhost);
define('db', $database);
define('dbUser', $db_username);
define('dbPass', $db_pass);
define('dbPort', $db_port);
?>
[/code]
and a sql dump to use
[code]
CREATE TABLE `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`integer_1` int(10) NOT NULL DEFAULT '0',
`string_1` varchar(50) NOT NULL DEFAULT 'nothing',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
/*Data for the table `test` */
insert into `test`(`id`,`integer_1`,`string_1`) values (1,1,'AAA'),(2,2,'BBB'),(3,3,'CCC'),(4,4,'DDD'),(5,5,'EEE'),(6,6,'FFF'),(7,7,'GGG'),(8,8,'HHH'),(9,9,'III'),(10,10,'JJJ'),(11,11,'KKK'),(12,12,'LLL'),(13,13,'MMM'),(14,14,'NNN'),(15,15,'OOO'),(16,16,'PPP'),(17,17,'QQQ'),(18,18,'RRR'),(19,19,'SSS'),(20,20,'TTT');
[/code]
[code]
<?php
error_reporting(E_ALL ^ E_NOTICE);
//header('Content-type: application/json'); //No success
//header('Content-type: text/html; charset=UTF-8'); /No success
//header('Content-type: text/html'); /No success
define(DEBUG,false); //set to true to see debug messages
function DebugOut($data)
{
if (DEBUG)
echo '',nl2br($data),'';
}
$aColumns = array( 'id', 'integer_1', 'string_1' );
$sIndexColumn = "id";
$sTable = "test";
include 'dbconf.inc.php';
$bindparams = array(); //This is where we store our values that will later on be used in the prepared statement
try
{
$db = new PDO('mysql:dbname='.db.';host='.dbHost,dbUser, dbPass,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $ex)
{
die('Error while attempting to connect to database:
' . $ex->getMessage());
}
/*
* Paging
*/
$sLimit = '';
if ( isset( $_GET['iDisplayStart'] ) && isset($_GET['iDisplayLength']) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT :iDisplayStart, :iDisplayLength";
$bindparams[] = array(':iDisplayStart',intval($_GET['iDisplayStart']),PDO::PARAM_INT);
$bindparams[] = array(':iDisplayLength',intval($_GET['iDisplayLength']),PDO::PARAM_INT);
//The actual values HERE need to be converted with intval, otherwise PDO does something weird to them, and raises an exception
}
/*
* Ordering
*/
$colsortval = intval( $_GET['iSortingCols'] );
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i < $colsortval; $i++ )
{
if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]." :sorder".$i.", ";
$bindparams[] = array(':sorder'.$i, $_GET['sSortDir_'.$i],PDO::PARAM_STR);
}
}
$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
*/
$colnum = count($aColumns);
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i
[code]
/* Individual column filtering */
$colnum = count($aColumns);
for ( $i=0 ; $i<$colnum ; $i++ )
{
if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE :searchcol".$i." OR ";
$bindparams[] = array(':searchcol'.$i, '%'.$_GET['sSearch'.$i].'%',PDO::PARAM_STR);
//We need to generate a unique
}
}
/*
* SQL queries
* Get data to display
*/
$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sWhere
$sOrder
$sLimit
";
DebugOut($sQuery);
//Finally, we can prepare the statement
$rResult = $db->prepare($sQuery);
$len = count($bindparams);
//and bind the values. Note that I am using bindValue and not bindparam because bindparam only accepts variables, not values.
//This will become clear when you need to capsulate values between %, example LIKE %myvalue%. This does not work with bindparam.
for($i = 0; $i < $len; ++$i)
{
DebugOut($bindparams[$i][0].", ".$bindparams[$i][1].", ".$bindparams[$i][2]);
$rResult->bindValue($bindparams[$i][0], $bindparams[$i][1],$bindparams[$i][2]);
}
$rResult->execute();//Execute our prepared statement
$iFilteredTotal = $db->query('SELECT FOUND_ROWS()')->fetchColumn(0); //Get the number of rows regardless while ignoring the LIMIT part
/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$iTotal = $db->query($sQuery)->fetchColumn(0); //Get the total amount of rows in the table
/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
//Switch between output methods just for playing around, default is true.
//The interesting thing is, output from both methods is valid according to http://www.jsonlint.com/
//and my test javascripts also accept output from both methods !
if (true)
{
/*This method produces output using the original style */
foreach($rResult as $aRow)
{
$row = array();
for ( $i=0 ; $ifetchAll(PDO::FETCH_ASSOC);
$output['aaData'][] = $rows;
}
$jsondata = json_encode( $output );
echo $jsondata;
?>
[/code]
[code]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
TwoRivers Visitor Logger
@import "css/demo_page.css";
@import "css/demo_table.css";
@import "css/themes/smoothness/jquery-ui-1.8.4.custom.css";
//<![CDATA[
$(document).ready(function() {
$('#example').dataTable( {
"bJQueryUI": true,
"aLengthMenu": [[5, 10, 25, 50, -1], [5, 10, 25, 50, "All"]],
"aaSorting": [[ 0, "desc" ]],
//"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "pdo.php"
} );
} );
function getJSONtest()
{
$.getJSON("pdo.php", function(data) {
$("#debug1").html(JSON.stringify(data));
})
}
function ajaxGet()
{
$.ajax({
url: "pdo.php",
dataType: "json",
success: function(data) {
$("#debug2").html(JSON.stringify(data));
//if you do not specify dataType: "json" in this method, you can even output data as plain text
//like so $("#debug2").html(data); and you will see the same output
}
});
}
function InitFromTXT()
{
$('#example').dataTable( {
"bDestroy": true,
"bJQueryUI": true,
"aLengthMenu": [[5, 10, 25, 50, -1], [5, 10, 25, 50, "All"]],
"bProcessing": true,
"sAjaxSource": 'json.txt'
} );
}
//]]>
id
integer_1
string_1
Loading data from server
id
integer_1
string_1
This fetches data from same source using $.getJSON()
This fetches data from same source using $.ajax()
Copy the output from either of the above tests, or view pdo.php on your server directly and copy that into a text file called json.txt and click below
[/code]
After completely muting all error output in pdo.php using error_reporting(0), dataTables.js does not show a Datatables warning pop-up an error message anymore, but instead nothing happens AND there's a javascript error in the non-minified version of dataTables.js in line 3330, namely json is null.
In this case json is the data returned from the server, which is passed to the function in line 3330 (Yes when the request was successful) from $.ajax in line 1277.
If the request in $.ajax would fail, you'd be seeing that Datatables warning pop-up telling you that JSON data from server could not be parsed bla bla.
Of course I went and checked the parameter called json in line 3330 and it actually is null. The question is: Why ?
And why do the 2 functions I added for testing purposes do not fail, and display valid JSON ?
And why does the apparently broken JSON output work super when pasted into the text file and data is read from that text file ?
It seems this has something to do with ajax in dataTables.js because my own requests (see javascript function in HTML section above) are not effected.
BUT,
Strangely enough everything works fine when using mysql, and even mysqli, but as soon as PDO is involved, it produces [argh] so what gives ???
I am using the latest PHP and MySQL Server version by the way, including the latest Firefox.
What on earth could I be missing ?
i have a table that is being filled using SQLITE + PDO, but i never found a way to fill the tables getting data server side, after seeing this post i decided to try again, but it didnt work.. what am i missing?
this is my server code
thanks!
[code]
$aColumns = array( 'LOTE_EXTERNO_ID_ASSOCIADO', 'Numero_Placa', 'Defeito','Observacao','Posicao','Reparo','Nota_Fiscal','Data','Responsavel','PROBLEMA_ID' );
$sIndexColumn = "PROBLEMA_ID";
$sTable = "PROBLEMA";
$bindparams = array(); //This is where we store our values that will later on be used in the prepared statement
$db = new PDO('sqlite:/Inetpub/wwwroot/mysqlitedb.db');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*
* Paging
*/
$sLimit = '';
if ( isset( $_GET['iDisplayStart'] ) && isset($_GET['iDisplayLength']) && $_GET['iDisplayLength'] != '-1' )
{
$bindparams[] = array(':iDisplayStart',intval($_GET['iDisplayStart']),PDO::PARAM_INT);
$bindparams[] = array(':iDisplayLength',intval($_GET['iDisplayLength']),PDO::PARAM_INT);
//The actual values HERE need to be converted with intval, otherwise PDO does something weird to them, and raises an exception
}
/*
* Ordering
*/
$colsortval = intval( $_GET['iSortingCols'] );
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i < $colsortval; $i++ )
{
if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]." :sorder".$i.", ";
$bindparams[] = array(':sorder'.$i, $_GET['sSortDir_'.$i],PDO::PARAM_STR);
}
}
$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
*/
$colnum = count($aColumns);
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $iprepare($sQuery);
$len = count($bindparams);
//and bind the values. Note that I am using bindValue and not bindparam because bindparam only accepts variables, not values.
//This will become clear when you need to capsulate values between %, example LIKE %myvalue%. This does not work with bindparam.
for($i = 0; $i < $len; ++$i)
{
$rResult->bindValue($bindparams[$i][0], $bindparams[$i][1],$bindparams[$i][2]);
}
$rResult->execute();//Execute our prepared statement
$iFilteredTotal = $db->query('SELECT FOUND_ROWS()')->fetchColumn(0); //Get the number of rows regardless while ignoring the LIMIT part
/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$iTotal = $db->query($sQuery)->fetchColumn(0); //Get the total amount of rows in the table
/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
foreach($rResult as $aRow)
{
$row = array();
for ( $i=0 ; $i
Part 1:
[code]
<?php
/*
* Datatables datasource Singleton class using PDO prepared statements by PixelProphet Lane
*/
define("DEBUG",false);
require '../data.config.inc.php';
Class PDO_Datatable
{
private static $instance = null;
private $db;
private $aColumns = array();
private $sIndexColumn;
private $sTable;
private $get;
private function __construct($cols, $indexcol, $table, $get)
{
if ($cols === FALSE || $indexcol === FALSE || $table === FALSE || $get === FALSE)
{
die("One or more parameters are missing.");
}
else
{
$this->aColumns = $cols;
$this->sIndexColumn = $indexcol;
$this->sTable = $table;
$this->get = $get;
}
try
{
$this->db = new PDO('mysql:dbname='.db.';host='.dbHost,dbUser, dbPass,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_OBJ);
}
catch (PDOException $ex)
{
echo $ex->getMessage();exit;
}
$this->returnData();
}
private function __clone() {}
public static function getInstance($cols = FALSE, $indexcol = FALSE, $table = FALSE, $get = FALSE)
{
if(self::$instance === null)
{
$c = __CLASS__;
self::$instance = new $c($cols, $indexcol, $table, $get);
}
return self::$instance;
}
private function GET($var) //Experimental
{
return array_key_exists($var, $this->getvars) ? (!empty($this->getvars[$var]) ? (is_numeric($this->getvars[$var]) ? intval($this->getvars[$var]) : $this->getvars[$var]) : '') : '';
}
private function returnData()
{
$bindvalues = array();
/* Paging */
$sLimit = '';
if ( isset( $this->get['iDisplayStart'] ) && isset($this->get['iDisplayLength']) && $this->get['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT :iDisplayStart, :iDisplayLength";
$bindvalues[] = array(':iDisplayStart',intval($this->get['iDisplayStart']),PDO::PARAM_INT);
$bindvalues[] = array(':iDisplayLength',intval($this->get['iDisplayLength']),PDO::PARAM_INT);
//The actual values HERE need to be converted with intval, otherwise PDO puts them in quotes and the query raises an exception
}
/* Ordering */
$sOrder = "";
$colsortval = isset($this->get['iSortingCols']) ? intval( $this->get['iSortingCols'] ) : 0;
if ( isset( $this->get['iSortCol_0']) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i < $colsortval; $i++ )
{
if ( isset($this->get[ 'bSortable_'.intval($this->get['iSortCol_'.$i]) ]) && $this->get[ 'bSortable_'.intval($this->get['iSortCol_'.$i]) ] == "true" )
{
if (isset($this->get['sSortDir_'.$i]))
{
if (isset($this->aColumns[ intval( $this->get['iSortCol_'.$i] ) ]))
{
$sOrder .= $this->aColumns[ intval( $this->get['iSortCol_'.$i] ) ].(strtoupper($this->get['sSortDir_'.$i]) == "ASC" ? " ASC" : " DESC").", ";
//PDO always wants to quote everything unless it's an integer, so we're not going to bindvalues here
}
}
}
}
$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}
[/code]
[code]
/* 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
*/
$colnum = count($this->aColumns);
$sWhere = "";
if ( !empty($this->get['sSearch']) )
{
$sWhere = "WHERE (";
for ( $i=0 ; $iaColumns) ; $i++ )
{
$sWhere .= $this->aColumns[$i]." LIKE :searchterm OR ";
}
$bindvalues[] = array(':searchterm', '%'.$this->get['sSearch'].'%',PDO::PARAM_STR);
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
/* Individual column filtering */
$colnum = count($this->aColumns);
for ( $i=0 ; $i<$colnum ; $i++ )
{
if ( isset($this->get['bSearchable_'.$i]) && $this->get['bSearchable_'.$i] == "true" && isset($this->get['sSearch_'.$i]) && $this->get['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $this->aColumns[$i]." LIKE :searchcol".$i." OR ";
$bindvalues[] = array(':searchcol'.$i, '%'.$this->get['sSearch'.$i].'%',PDO::PARAM_STR);
}
}
/* SQL queries and get data to display */
$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $this->aColumns))." FROM ".$this->sTable." $sWhere $sOrder $sLimit";
$this->DebugOut($sQuery);
//Finally, prepare the statement
$rResult = $this->db->prepare($sQuery);
$len = count($bindvalues);
//and bind the values. Note that I am using bindValue and not bindparam because bindparam only accepts variables, not values.
//This will become clear when you need to capsulate values between %, example LIKE %myvalue%. This does not work with bindparam.
for($i = 0; $i < $len; ++$i)
{
$this->DebugOut($bindvalues[$i][0].", ".$bindvalues[$i][1].", ".$bindvalues[$i][2]);
$rResult->bindparam($bindvalues[$i][0], $bindvalues[$i][1],$bindvalues[$i][2]);
}
$rResult->execute();//Execute our prepared statement
$iFilteredTotal = $this->db->query('SELECT FOUND_ROWS()')->fetchColumn(0); //Get the number of rows while ignoring the LIMIT
/* Total data set length */
$sQuery = "
SELECT COUNT(".$this->sIndexColumn.")
FROM ".$this->sTable;
$iTotal = $this->db->query($sQuery)->fetchColumn(0); //Get the total amount of rows in the table
/* Output */
$output = array(
"sEcho" => isset($this->get['sEcho']) ? intval($this->get['sEcho']) : 0,
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while($aRow = $rResult->fetch())
{
$row = array();
foreach ($aRow as $rowdata)
{
$row[] = $rowdata;
}
$output['aaData'][] = $row;
}
$jsondata = json_encode($output);
echo $jsondata;
}
private function DebugOut($data)
{
if (DEBUG)
echo '',nl2br($data),'';
}
}
header('Content-type: text/html; charset=UTF-8');
//Usage: PDO_Datatable::getInstance(array('column','names','comma','seperated'), 'the index column', 'the table name', $_GET);
PDO_Datatable::getInstance(array('id','integer_1','string_1'),'id','test',$_GET);
?>
[/code]
thanks a lot!!!
[code]<?php
/*
* Datatables datasource Singleton class using PDO prepared statements by PixelProphet Lane
*/
define("DEBUG",false);
Class PDO_Datatable
{
private static $instance = null;
private $db;
private $aColumns = array();
private $sIndexColumn;
private $sTable;
private $get;
private function __construct($cols, $indexcol, $table, $get)
{
if ($cols === FALSE || $indexcol === FALSE || $table === FALSE || $get === FALSE)
{
die("One or more parameters are missing.");
}
else
{
$this->aColumns = $cols;
$this->sIndexColumn = $indexcol;
$this->sTable = $table;
$this->get = $get;
}
try
{
$this->db = new PDO('sqlite:/Inetpub/wwwroot/mysqlitedb.db');
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_OBJ);
}
catch (PDOException $ex)
{
echo $ex->getMessage();exit;
}
$this->returnData();
}
private function __clone() {}
public static function getInstance($cols = FALSE, $indexcol = FALSE, $table = FALSE, $get = FALSE)
{
if(self::$instance === null)
{
$c = __CLASS__;
self::$instance = new $c($cols, $indexcol, $table, $get);
}
return self::$instance;
}
private function GET($var) //Experimental
{
return array_key_exists($var, $this->getvars) ? (!empty($this->getvars[$var]) ? (is_numeric($this->getvars[$var]) ? intval($this->getvars[$var]) : $this->getvars[$var]) : '') : '';
}
private function returnData()
{
$bindvalues = array();
/* Paging */
$sLimit = '';
if ( isset( $this->get['iDisplayStart'] ) && isset($this->get['iDisplayLength']) && $this->get['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT :iDisplayStart, :iDisplayLength";
$bindvalues[] = array(':iDisplayStart',intval($this->get['iDisplayStart']),PDO::PARAM_INT);
$bindvalues[] = array(':iDisplayLength',intval($this->get['iDisplayLength']),PDO::PARAM_INT);
//The actual values HERE need to be converted with intval, otherwise PDO puts them in quotes and the query raises an exception
}
/* Ordering */
$sOrder = "";
$colsortval = isset($this->get['iSortingCols']) ? intval( $this->get['iSortingCols'] ) : 0;
if ( isset( $this->get['iSortCol_0']) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i < $colsortval; $i++ )
{
if ( isset($this->get[ 'bSortable_'.intval($this->get['iSortCol_'.$i]) ]) && $this->get[ 'bSortable_'.intval($this->get['iSortCol_'.$i]) ] == "true" )
{
if (isset($this->get['sSortDir_'.$i]))
{
if (isset($this->aColumns[ intval( $this->get['iSortCol_'.$i] ) ]))
{
$sOrder .= $this->aColumns[ intval( $this->get['iSortCol_'.$i] ) ].(strtoupper($this->get['sSortDir_'.$i]) == "ASC" ? " ASC" : " DESC").", ";
//PDO always wants to quote everything unless it's an integer, so we're not going to bindvalues here
}
}
}
}
$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}
[/code]
* 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
*/
$colnum = count($this->aColumns);
$sWhere = "";
if ( !empty($this->get['sSearch']) )
{
$sWhere = "WHERE (";
for ( $i=0 ; $iaColumns) ; $i++ )
{
$sWhere .= $this->aColumns[$i]." LIKE :searchterm OR ";
}
$bindvalues[] = array(':searchterm', '%'.$this->get['sSearch'].'%',PDO::PARAM_STR);
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
/* Individual column filtering */
$colnum = count($this->aColumns);
for ( $i=0 ; $i<$colnum ; $i++ )
{
if ( isset($this->get['bSearchable_'.$i]) && $this->get['bSearchable_'.$i] == "true" && isset($this->get['sSearch_'.$i]) && $this->get['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $this->aColumns[$i]." LIKE :searchcol".$i." OR ";
$bindvalues[] = array(':searchcol'.$i, '%'.$this->get['sSearch'.$i].'%',PDO::PARAM_STR);
}
}
/* SQL queries and get data to display */
$sQuery = "SELECT ".str_replace(" , ", " ", implode(", ", $this->aColumns))." FROM ".$this->sTable." $sWhere $sOrder $sLimit";
// $this->DebugOut($sQuery);
//Finally, prepare the statement
$rResult = $this->db->prepare($sQuery);
$len = count($bindvalues);
//and bind the values. Note that I am using bindValue and not bindparam because bindparam only accepts variables, not values.
//This will become clear when you need to capsulate values between %, example LIKE %myvalue%. This does not work with bindparam.
for($i = 0; $i < $len; ++$i)
{
$this->DebugOut($bindvalues[$i][0].", ".$bindvalues[$i][1].", ".$bindvalues[$i][2]);
$rResult->bindparam($bindvalues[$i][0], $bindvalues[$i][1],$bindvalues[$i][2]);
}
$rResult->execute();//Execute our prepared statement
$iFilteredTotal = $this->db->query('SELECT COUNT(*) FROM PROBLEMA')->fetchColumn(); //Get the number of rows while ignoring the LIMIT
/* Total data set length */
$sQuery = "
SELECT COUNT(".$this->sIndexColumn.")
FROM ".$this->sTable;
$iTotal = $this->db->query($sQuery)->fetchColumn(0); //Get the total amount of rows in the table
/* Output */
$output = array(
"sEcho" => isset($this->get['sEcho']) ? intval($this->get['sEcho']) : 0,
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while($aRow = $rResult->fetch())
{
$row = array();
foreach ($aRow as $rowdata)
{
$row[] = $rowdata;
}
$output['aaData'][] = $row;
}
$jsondata = json_encode($output);
echo $jsondata;
}
private function DebugOut($data)
{
if (DEBUG)
echo '',nl2br($data),'';
}
}
header('Content-type: text/html; charset=UTF-8');
//Usage: PDO_Datatable::getInstance(array('column','names','comma','seperated'), 'the index column', 'the table name', $_GET);
PDO_Datatable::getInstance(array('LOTE_EXTERNO_ID_ASSOCIADO','PROBLEMA_ID' ),'PROBLEMA_ID','PROBLEMA',$_GET);
?>[/code]
but after a few hours without doing anything i just tried to run again and it worked!!
thanks a lot PixelP!!!
Thanks for taking time to create a PDO class for DataTables!
I'm trying to use your example, but am running into some problems with filtering. Whenever I type anything into the search box, no results are returned.
After much troubleshooting, I believe it is related to the bindPArams statement:
[code]$rResult->bindParam($bindvalues[$i][0], $bindvalues[$i][1],$bindvalues[$i][2]);[/code]
I noticed you have a note in the code above that that states
[quote] Note that I am using bindValue and not bindparam because bindparam only accepts variables, not values.
//This will become clear when you need to capsulate values between %, example LIKE %myvalue%. This does not work with bindparam. [/quote]
Unfortuantely, even when I replace bindparam with bindValue, filtering still does not work.
The SQL string that gets dumped is
[quote]SELECT SQL_CALC_FOUND_ROWS id, integer_1, string_1 FROM test WHERE (id LIKE :searchterm OR integer_1 LIKE :searchterm OR string_1 LIKE :searchterm ) ORDER BY id DESC LIMIT :iDisplayStart, :iDisplayLength[/quote]
And the bindparameters are
[quote]
:iDisplayStart, 0, 1
:iDisplayLength, 10, 1
:searchterm, %TT%, 2
[/quote]
I also dumped the bindvalues using var_export
[quote]array (
0 =>
array (
0 => ':iDisplayStart',
1 => 0,
2 => 1,
),
1 =>
array (
0 => ':iDisplayLength',
1 => 10,
2 => 1,
),
2 =>
array (
0 => ':searchterm',
1 => '%TT%',
2 => 2,
),
)
[/quote]
Any suggestions greatly appreciated!!
Thanks
Jim
I changed the code that builds the Where clause to create different search terms for each column (searchterm1, searchterm2, etc) by move the bindvalues[] into the for clause and adding .$i for each search term.
[code]
$sWhere = "";
if ( !empty($this->get['sSearch']) )
{
$sWhere = "WHERE (";
for ( $i=0 ; $iaColumns) ; $i++ )
{
$sWhere .= $this->aColumns[$i]." LIKE :searchterm".$i." OR ";
$bindvalues[] = array(':searchterm'.$i, '%'.$this->get['sSearch'].'%',PDO::PARAM_STR);
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
[/code]
What I believe was happening was once one of the OR clauses failed, it would not test any more of the OR clauses, since they used the same bindParam. By creating new bind parameters for each OR clause, all OR clauses get tested. I only found this be testing, have not been able to find any documentation to verify it.
Note that I also am using bindValue instead of bindParam, but that did not appear to make any difference.
Thank you for creating a PDO class for DataTables. Can I use the same class for Oracle database too? If not, what changes are required?
Thank you for your help,
San
Allan