使用PHP的DataTable
Is it possible to code a DataTable using PHP?
When searching online all tutorials etc are using ajax, which I'm not to comfortable with so just wondering if there is a way of just using PHP to code a datatable & if so does anyone have a link for me to look through?
Thanks
Even though I totally agree with comments below your question! as a quick workaround (with no learning curve!) if your tables contain less than 10.000 rows you may simply generate a simple HTML table in a for / while loop as you would for a simple table. Then pass your table's ID to datatable like this:
$(document).ready(function() {
$('#example').DataTable();
});
But after all, AJAX is created to make world a better place for us. :-)
A simple DataTable-Output:
A bit of AJAX is required, but it's easy to understand:
ajax:
$(document).ready(function() {
$('#example').DataTable( {
"processing": true,
"serverSide": true,
"ajax": "../server_side/scripts/server_processing.php"
} );
} );
html:
<table id="example" class="display" width="100%" cellspacing="0">
<thead>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Position</th>
<th>Office</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
<tfoot>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Position</th>
<th>Office</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</tfoot>
</table>
css: here is the external datatable.css file for this example:
https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css
php:
<?php
/*
* DataTables example server-side processing script.
*
* Please note that this script is intentionally extremely simply to show how
* server-side processing can be implemented, and probably shouldn't be used as
* the basis for a large complex system. It is suitable for simple use cases as
* for learning.
*
* See http://datatables.net/usage/server-side for full details on the server-
* side processing requirements of DataTables.
*
* @license MIT - http://datatables.net/license_mit
*/
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
// DB table to use
$table = 'datatables_demo';
// Table's primary key
$primaryKey = 'id';
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 'first_name', 'dt' => 0 ),
array( 'db' => 'last_name', 'dt' => 1 ),
array( 'db' => 'position', 'dt' => 2 ),
array( 'db' => 'office', 'dt' => 3 ),
array(
'db' => 'start_date',
'dt' => 4,
'formatter' => function( $d, $row ) {
return date( 'jS M y', strtotime($d));
}
),
array(
'db' => 'salary',
'dt' => 5,
'formatter' => function( $d, $row ) {
return '$'.number_format($d);
}
)
);
// SQL server connection information
$sql_details = array(
'user' => '',
'pass' => '',
'db' => '',
'host' => ''
);
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP
* server-side, there is no need to edit below this line.
*/
require( 'ssp.class.php' );
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);
the ajax output that the php code uses for the table, will look like this:
{
"draw": 3,
"recordsTotal": 57,
"recordsFiltered": 57,
"data": [
[
"Airi",
"Satou",
"Accountant",
"Tokyo",
"28th Nov 08",
"$162,700"
],
[
"Angelica",
"Ramos",
"Chief Executive Officer (CEO)",
"London",
"9th Oct 09",
"$1,200,000"
],
[
"Ashton",
"Cox",
"Junior Technical Author",
"San Francisco",
"12th Jan 09",
"$86,000"
],
[
"Bradley",
"Greer",
"Software Engineer",
"London",
"13th Oct 12",
"$132,000"
],
[
"Brenden",
"Wagner",
"Software Engineer",
"San Francisco",
"7th Jun 11",
"$206,850"
],
[
"Brielle",
"Williamson",
"Integration Specialist",
"New York",
"2nd Dec 12",
"$372,000"
],
[
"Bruno",
"Nash",
"Software Engineer",
"London",
"3rd May 11",
"$163,500"
],
[
"Caesar",
"Vance",
"Pre-Sales Support",
"New York",
"12th Dec 11",
"$106,450"
],
[
"Cara",
"Stevens",
"Sales Assistant",
"New York",
"6th Dec 11",
"$145,600"
],
[
"Cedric",
"Kelly",
"Senior Javascript Developer",
"Edinburgh",
"29th Mar 12",
"$433,060"
]
]
}
hope this helped you. You can see the example working here
1)Include Css and JS files:
<link href="css/datatables/dataTables.bootstrap.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" charset="utf-8" src="js/jquery.js"></script>
<script src="js/datatables/jquery.dataTables.js" type="text/javascript"></script>
<script src="js/datatables/dataTables.bootstrap.js" type="text/javascript"></script>
2)Create function in controller file:
function MyData()
{
$aColumns = array('user_id','username','address');
$sTable = 'user';
$iDisplayStart = $this->input->get_post('iDisplayStart', true);
$iDisplayLength = $this->input->get_post('iDisplayLength', true);
$iSortCol_0 = $this->input->get_post('iSortCol_0', true);
$iSortingCols = $this->input->get_post('iSortingCols', true);
$sSearch = $this->input->get_post('sSearch', true);
$sEcho = $this->input->get_post('sEcho', true);
// Paging
if(isset($iDisplayStart) && $iDisplayLength != '-1')
{
$this->db->limit($this->db->escape_str($iDisplayLength), $this->db->escape_str($iDisplayStart));
}
// Ordering
if(isset($iSortCol_0))
{
for($i=0; $i<intval($iSortingCols); $i++)
{
$iSortCol = $this->input->get_post('iSortCol_'.$i, true);
$bSortable = $this->input->get_post('bSortable_'.intval($iSortCol), true);
$sSortDir = $this->input->get_post('sSortDir_'.$i, true);
if($bSortable == 'true')
{
$this->db->order_by($aColumns[intval($this->db->escape_str($iSortCol))], $this->db->escape_str($sSortDir));
}
}
}
/*
* 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
*/
if(isset($sSearch) && !empty($sSearch))
{
for($i=0; $i<count($aColumns); $i++)
{
$bSearchable = $this->input->get_post('bSearchable_'.$i, true);
// Individual column filtering
if(isset($bSearchable) && $bSearchable == 'true')
{
$this->db->or_like($aColumns[$i], $this->db->escape_like_str($sSearch));
}
}
}
// Select Data
$this->db->select('SQL_CALC_FOUND_ROWS '.str_replace(' , ', ' ', implode(', ', $aColumns)), false);
$rResult = $this->db->get($sTable);
// Data set length after filtering
$this->db->select('FOUND_ROWS() AS found_rows');
$iFilteredTotal = $this->db->get()->row()->found_rows;
// Total data set length
$iTotal = $this->db->count_all($sTable);
// Output
$output = array(
'sEcho' => intval($sEcho),
'iTotalRecords' => $iTotal,
'iTotalDisplayRecords' => $iFilteredTotal,
'aaData' => array()
);
foreach($rResult->result_array() as $aRow)
{
$row = array();
foreach($aColumns as $col)
{
$row[] = $aRow['user_id'];
$row[] = $aRow['username'];
$row[] = $aRow['address'];
}
$output['aaData'][] = $row;
}
echo json_encode($output);
}
3)Create a table in view file:
<table class="table table-bordered display" cellspacing="0" width="100%" id="UserTable">
<thead>
<tr>
<th>User Id</th>
<th>Username</th>
<th>Address</th>
<th>Edit / Delete</th>
</tr>
</thead>
</table>
4)Write AJAX:
<script>
var ETable = $('#UserTable').dataTable({
"infoEmpty": "No records available",
"sProcessing": "DataTables is currently busy",
"processing": true,
// "sorting" : true,
"order": [ [1, 'asc'] ],
"serverSide": true,
"sAjaxSource": "user/MyData",
"aLengthMenu": [[10, 25, 50,100], [10, 25, 50,100]],
// "aaSorting": [[0, 'desc']],
// { "sExtends": "editor_create", "editor": "NoteEditor" },
"sSearch":true,
"iDisplayLength": 10,
// "dom": 'T<"clear">lfrtip',
"sdom": 'zrtSpi',
"bDeferRender": true,
"oLanguage": {
"sInfoFiltered": "",
"sProcessing": "<img style='position:absolute;' src=''>"
},
"tableTools": {
"sSwfPath": "assets/swf/copy_csv_xls_pdf.swf"
},
"aColumns": [
{
"data": null,
"defaultContent": '',
"className": 'select-checkbox',
"orderable": false
},
{ "data": "user_id" },
{ "data": "username" },
{ "data": "address" }
]
});
</script>