Alternative Server-Side PHP Script

rewen

Hi everyone,

I just wanted to share a script that I use in my server-side enabled datatables. It is a tweaked copy of the one used in the examples here. Since I have many data tables in my projects and it was very time consuming to modify the example script to work with each one I came up with this tweaked version that is simpler to setup.

Pros:
- Define all of the columns to use in one place (and only one place). No index association / fnColumnToField function required.
- Uses PHPs json_encode function which ensures proper escaping and allows for simpler code.
- Works with JOINS and CONCAT, etc
- Allows for custom processing of data.
- Allows for 'virtual' rows.

Cons
- Filtering is done on all defined columns regardless of which are set to "bSearchable": false. I think the dataTables examples are like this as well.
- Filtering is done the same way on each column (WHERE column LIKE '%search_term%'). In the dataTables example script you have more control over that.

All you need to set up is:
- your mysql (or other) connection
- the $columns array
- $table

Optional settings:
- $joins

Simple Example
[code]
<?php

// include or define your mysql config here
require_once "mysql_config.php";

// the columns to be filtered, ordered and returned
// must be in the same order as displayed in the table
$columns = array
(
"id",
"first_name",
"last_name",
"phone_number",
"email_address"
);

// the table being queried
$table = "users";

// any JOIN operations that you need to do
$joins = "";

// filtering
$sql_where = "";
if ($_GET['sSearch'] != "")
{
$sql_where = "WHERE ";
foreach ($columns as $column)
{
$sql_where .= $column . " LIKE '%" . mysql_real_escape_string( $_GET['sSearch'] ) . "%' OR ";
}
$sql_where = substr($sql_where, 0, -3);
}

// ordering
$sql_order = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sql_order = "ORDER BY ";
for ( $i = 0; $i < mysql_real_escape_string( $_GET['iSortingCols'] ); $i++ )
{
$sql_order .= $columns[$_GET['iSortCol_' . $i]] . " " . mysql_real_escape_string( $_GET['sSortDir_' . $i] ) . ", ";
}
$sql_order = substr_replace( $sql_order, "", -2 );
}

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

$main_query = mysql_query("SELECT SQL_CALC_FOUND_ROWS " . implode(", ", $columns) . " FROM {$table} {$joins} {$sql_where} {$sql_order} {$sql_limit}")
or die(mysql_error());

// get the number of filtered rows
$filtered_rows_query = mysql_query("SELECT FOUND_ROWS()")
or die(mysql_error());
$row = mysql_fetch_array($filtered_rows_query);
$response['iTotalDisplayRecords'] = $row[0];

// get the number of rows in total
$total_query = mysql_query("SELECT COUNT(id) FROM {$table}")
or die(mysql_error());
$row = mysql_fetch_array($total_query);
$response['iTotalRecords'] = $row[0];

// send back the sEcho number requested
$response['sEcho'] = intval($_GET['sEcho']);

// this line is important in case there are no results
$response['aaData'] = array();

// finish getting rows from the main query
while ($row = mysql_fetch_row($main_query))
{
// put all of the data from the current row into aaData
$response['aaData'][] = $row;
}

// prevent caching and echo the associative array as json
header('Cache-Control: no-cache');
header('Pragma: no-cache');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Content-type: application/json');
echo json_encode($response);

?>
[/code]

rewen

More complex example using CONCAT, JOINS, and a virtual row (defined in the while loop near the end):
[code]

<?php

require_once "../libs/config.php";

// the columns to be filtered, ordered and returned
// must be in the same order as displayed in the table
$columns = array
(
"i.id",
"i.date_and_time",
"s.name", //store name
"CONCAT( e.first_name, ' ', e.last_name )", //employee name
"CONCAT( c.first_name, ' ', c.last_name )", //customer name
"i.total"
);

$table = "invoices i";

$joins = "LEFT JOIN customers c ON i.customer_id = c.id LEFT JOIN employees e ON i.employee_id = e.id LEFT JOIN stores s ON i.store_id = s.id";

// filtering
$sql_where = "";
if ($_GET['sSearch'] != "")
{
$sql_where = "WHERE ";
foreach ($columns as $column)
{
$sql_where .= $column . " LIKE '%" . mysql_real_escape_string( $_GET['sSearch'] ) . "%' OR ";
}
$sql_where = substr($sql_where, 0, -3);
}

// ordering
$sql_order = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sql_order = "ORDER BY ";
for ( $i = 0; $i < mysql_real_escape_string( $_GET['iSortingCols'] ); $i++ )
{
$sql_order .= $columns[$_GET['iSortCol_' . $i]] . " " . mysql_real_escape_string( $_GET['sSortDir_' . $i] ) . ", ";
}
$sql_order = substr_replace( $sql_order, "", -2 );
}

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

$main_query = mysql_query("SELECT SQL_CALC_FOUND_ROWS " . implode(", ", $columns) . " FROM {$table} {$joins} {$sql_where} {$sql_order} {$sql_limit}")
or die(mysql_error());

// get the number of filtered rows
$filtered_rows_query = mysql_query("SELECT FOUND_ROWS()")
or die(mysql_error());
$row = mysql_fetch_array($filtered_rows_query);
$response['iTotalDisplayRecords'] = $row[0];

// get the number of rows in total
$total_query = mysql_query("SELECT COUNT(id) FROM {$table}")
or die(mysql_error());
$row = mysql_fetch_array($total_query);
$response['iTotalRecords'] = $row[0];

// send back the number requested
$response['sEcho'] = intval($_GET['sEcho']);

$response['aaData'] = array();

// finish getting rows from the main query
while ($row = mysql_fetch_row($main_query))
{
// add an extra column that has a print link
$row[] = "";
$response['aaData'][] = $row;
}

header('Cache-Control: no-cache');
header('Pragma: no-cache');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Content-type: application/json');
echo json_encode($response);

?>
[/code]

allan

Very nice! Thanks for sharing this with us :-)

Regards,
Allan

rewen

Oops! I just noticed that I am using a custom function in both of the examples above.

The mysql_clean function is something I use to apply mysql_real_escape_string to both strings and multi-dimensional arrays alike. I'll replace it with mysql_real_escape_string in those examples.. there's no advantage to using the custom one in them anyway.

Edit: Done.

Sof

Hi,
Is there a way to make it working in all case without having to set the columns before ?

Thx for ur work.

Ups sry i found... :) http://www.datatables.net/development/server-side/

rewen

I don't know of any safe or simple way to do it without specifying the columns, especially since the number of tags need to match up with the number of columns being fetched. And even if you know that number then your code still needs to know the column names for the SQL queries for filtering/ordering the table.

I think there's a way to send the column names to datatables and then datatables would be able to send them back when you order/filter - but then you'd be revealing the column names to anyone who viewed the source of your page or ajax request data with firebug, etc, which could be a security risk.

rewen

In case anyone is wanting to do column specific filtering, here's how I've been doing it. Put this under the global filtering code and optionally change it from a LIKE to an = if you want to make it more strict:

[code]// column specific filtering
foreach ($columns as $key => $column)
{
if ($_GET['sSearch_' . $key] != "")
{
$sql_where .= !$sql_where ? "WHERE " : "AND ";
$sql_where .= $column . " LIKE '%" . mysql_clean($_GET['sSearch_' . $key]) . "%' ";
}
}[/code]

rewen

Ah I see that the Allan has updated the example php/mysql script so that it has most of the advantages of mine, primarily a single spot to define the columns, and json_encode. I would recommend that anyone reading this use his instead as it's maintained with dataTables:

http://www.datatables.net/development/server-side/php_mysql

EmanSJ

hi Rewen,

Thanks for your codes, I was so stuck on how to add a href and img src in datatables, especially in server side.
Many thanks.