Datatables Ajax and Laravel 4 - Tables Not Displaying, Ajax Error
I beat my head all the time last night and today. I have a Laravel 4.2 project where I have large tables (2000+) for some of my users. I want to use ajax functions to display data dynamically. I used the PHP example from datatables, but I am getting "no response" in the Network tab of the Firefox inspector
Here is my jquery on the page:
jQuery(document).ready(function() {
jQuery('#table_large').dataTable({
"processing": true,
"serverSide": true,
"ajax": {
"url": "/pets/records/data/",
"type": "POST"
}
});
});
Here is my route:
Route::post('/pets/records/data/', 'PetsController@ajax');
And here is my repository:
public function ajax(){
$aColumns = array( 'custom_id', 'pet_name', 'pet_type', 'age', 'breed','gender','status','intake_date' );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "pet_id";
/* DB table to use */
$sTable = "pets";
/* Database connection information */
$gaSql['user'] = \Config::get('database.connections.mysql.username');
$gaSql['password'] = \Config::get('database.connections.mysql.password');
$gaSql['db'] = \Config::get('database.connections.mysql.database');
$gaSql['server'] = \Config::get('database.connections.mysql.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
*/
/*
* Local functions
*/
function fatal_error ( $sErrorMessage = '' )
{
header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
die( $sErrorMessage );
}
/*
* MySQL connection
*/
if ( ! $gaSql['link'] = @mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) )
{
fatal_error( 'Could not open connection to server' );die();
}
if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
{
fatal_error( 'Could not select database ' );die();
}
/*
* Paging
*/
$sLimit = "";
if ( isset( $_POST['iDisplayStart'] ) && $_POST['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".intval( $_POST['iDisplayStart'] ).", ".
intval( $_POST['iDisplayLength'] );
}
/*
* Ordering
*/
$sOrder = "";
if ( isset( $_POST['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<intval( $_POST['iSortingCols'] ) ; $i++ )
{
if ( $_POST[ 'bSortable_'.intval($_POST['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= $aColumns[ intval( $_POST['iSortCol_'.$i] ) ]."
".($_POST['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
}
}
$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}
$sWhere = "";
if ( isset($_POST['sSearch']) && $_POST['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_POST['bSearchable_'.$i]) && $_POST['bSearchable_'.$i] == "true" )
{
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_POST['sSearch'] )."%' OR ";
}
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_POST['bSearchable_'.$i]) && $_POST['bSearchable_'.$i] == "true" && $_POST['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_POST['sSearch_'.$i])."%' ";
}
}
/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = @mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
/* Data set length after filtering */
$sQuery = "SELECT FOUND_ROWS()";
$rResultFilterTotal = @mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$rResultTotal = @mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
//$rResultTotal = \DB::select(\DB::raw($sQuery));
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
/*
* Output
*/
if(isset($_POST['sEcho'])){
$sEcho = intval($_POST['sEcho']);
}else{
$sEcho = 0;
}
$output = array(
"sEcho" => $sEcho,
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
try {
while ($aRow = mysql_fetch_array($rResult)) {
$row = array();
for ($i = 0; $i < count($aColumns); $i++) {
switch($aColumns[$i]){
case 'version':
/* Special output formatting for 'version' column */
$row[] = ($aRow[$aColumns[$i]] == "0") ? '-' : $aRow[$aColumns[$i]];
break;
default:
$row[] = $aRow[$aColumns[$i]];
break;
}
}
$sanitized_row = [];
foreach($row as $cell){
$encoding = mb_detect_encoding($cell, mb_detect_order(), false);
if($encoding == "UTF-8") {
$cell = mb_convert_encoding($cell, 'UTF-8', 'UTF-8');
}
$cell = iconv(mb_detect_encoding($cell, mb_detect_order(), false), "UTF-8//IGNORE", $cell);
$sanitized_row[] = $cell;
}
$output['aaData'][] = $sanitized_row;
}
}catch (Exception $e){
echo 'Error: ';
var_dump($row);
}
$json = json_encode( $output );
if(json_last_error()) {
print "json last error: ".json_last_error().'\n';
print "json last error msg: ".json_last_error_msg().'\n';
}
return $json;
}
I was getting the encoding error, but I added UT8 filtering which no longer throws the error, but not sure if its the solution to the problem. Does anyone see anything egregious with this code or does anyone have a better solution for Laravel 4.2? Thanks and let me know!
source to share
Based on your DataTables initialization code and question tag datatables-1.10
, you are using DataTables 1.10. However, your server-side code is using the POST variables used in DataTables 1.9. DataTables 1.10 sends variables with different names (see manual ).
Also in the DataTables 1.10 distribution there is a sample class ( /examples/server_side/scripts/ssp.class.php
) that simplifies server side processing (see example ). However, this class does not support unions and subqueries. This should be fine for you, since you are only using one table pets
, use the method SSP::simple
as shown in the example above.
There is also an extended version of this class on GitHub ( github.com/emran/ssp ) that should support connections and subrequests.
source to share