tiny contribution --> Server-side processing change into class | PHP with PDO

darkstars

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]

allan

Hi darkstars,

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

darkstars

Thanks to you datable is very awesome ! I will watch for convert MySQL query but i begin with pdo, so wait and see ^^

PixelP

Hey there, I'm just curious, but what's the point of using PDO, if you're using
[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.

darkstars

I do it quickly there are still many thing to change but you are free to contribute ;-)

PixelP

I have in fact converted the included php file to use PDO with prepared statements, and the file produces valid JSON, but for some reason (which is totally beyond me), jquery.dataTables.min.js does not accept the response.

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.

PixelP

Ok guys, here's something you can pull out your last hair over, if you have any that is :P

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]

PixelP

This form is not allowing me to paste the full code in one bit sooooo... part one
[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

PixelP

and part two
[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]

PixelP

And the HTML
[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]

PixelP

Ok, this really doesn't make any sense.
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 ?

PixelP

What a nightmare, but it's fixed using proper PDO with prepared statements !

Drako

hi!

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

PixelP

That still needs some modifications, however I was tired of having to split up my posts each time, and apart from that nobody seemed interested.

PixelP

Here it is anyway...
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]

PixelP

and part 2
[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]

Drako

Thanks PixelP, i feel like im very close to the solution, but im still having problems, when i run the page it says that there is a formating error on JSON, and really is, the column names are missing from the json, but the aColumns[i] is fine, all columns are there, i think there is something wrong with the $rResult, can you help me out?

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]

Drako

[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 ".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]

Drako

it works (dunno why)

but after a few hours without doing anything i just tried to run again and it worked!!

thanks a lot PixelP!!!

PixelP

Probably you had some older version cached, and as for your previous post, I guess you didn't define the same column names in your HTML that you did in the PHP

jimv

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

jimv

I think that I have fixed the issue with filtering not working.
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.

PixelP

Hi jimv, sorry for taking so long to respond. Your modification would work, but I never had any issues with filtering so far. It should not be necessary to create multiple variables for the search term though, because it's the same search term, just used in multiple locations in the query.

sanchow

PixelP,

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

@sanchow - almost certainly not, as Oracle doesn't support the LIMIT keyword. It isn't PDO, but this script might be of interest (or help you get the script above working with Oracle): http://datatables.net/development/server-side/php_oracle .

Allan

vere

Can we see a working version of the final code?