Query works but table shows no data - Suspect SQL issue, but can't see it.
Query works but table shows no data - Suspect SQL issue, but can't see it.
As it says on the tin.
server side script is this:
<?php
//SESSION START
if(!isset($_SESSION)) {
session_start();
}
if(isset($_SESSION['t'])) {
$t = $_SESSION['t'];
} else {
$t = null;
}
include("../lib/DataTables.php");
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;
Editor::inst( $db, 'asset A', 'A.id' )
->field(
//Static Fields - No Validation
Field::inst( 'T.assetType' ),
Field::inst( 'A.MATPTag' ),
Field::inst( 'A.Room' ),
Field::inst( 'D.discipline' ),
Field::inst( 'L.LocationName' ),
Field::inst( 'SY.systemName AS subsystem' ),
)
->leftJoin( 'assettype T', 'T.assetTypeID', '=', 'A.assetType' )
->leftJoin( 'loc L', 'L.id', '=', 'A.loc' )
->leftJoin( 'system SY', 'SY.systemID', '=', 'T.subsystem' )
->leftJoin( 'discipline D', 'D.disciplineID', '=', 'SY.discipline' )
->where( function ( $q ) use ( $t ) {
if(isset($t)) {
$q->where('A.lastUpdated', 'DATE_SUB(NOW(), INTERVAL '.$t.' day)', '<');
}
} )
->debug(true)
->process( $_POST )
->json();
?>
Debug SQL is:
"debug": [
{
"query": "SELECT `A`.`id` as 'A.id', `T`.`assetType` as 'T.assetType', `A`.`MATPTag` as 'A.MATPTag', `A`.`Room` as 'A.Room', `D`.`discipline` as 'D.discipline', `L`.`LocationName` as 'L.LocationName', `SY`.`systemName` as 'SY.systemName' FROM asset A LEFT JOIN assettype T ON `T`.`assetTypeID` = `A`.`assetType` LEFT JOIN loc L ON `L`.`id` = `A`.`loc` LEFT JOIN system SY ON `SY`.`systemID` = `T`.`subsystem` LEFT JOIN discipline D ON `D`.`disciplineID` = `SY`.`discipline` WHERE `A`.`lastUpdated` < :where_0 ",
"bindings": [
{
"name": ":where_0",
"value": "DATE_SUB(NOW(), INTERVAL 7 day)",
"type": null
}
]
}
]
Which I believe equates to:
SELECT `A`.`id` as 'A.id', `T`.`assetType` as 'T.assetType', `A`.`MATPTag` as 'A.MATPTag', `A`.`Room` as 'A.Room', `D`.`discipline` as 'D.discipline', `L`.`LocationName` as 'L.LocationName', `SY`.`systemName` as 'SY.systemName' FROM asset A LEFT JOIN assettype T ON `T`.`assetTypeID` = `A`.`assetType` LEFT JOIN loc L ON `L`.`id` = `A`.`loc` LEFT JOIN system SY ON `SY`.`systemID` = `T`.`subsystem` LEFT JOIN discipline D ON `D`.`disciplineID` = `SY`.`discipline` WHERE `A`.`lastUpdated` < DATE_SUB(NOW(), INTERVAL 7 day)
That query returns rows in phpmyadmin, but I get no rows in datatables.
Ran the debug script and shows all upto date bar a nightly update available for datatables.
Probable something stupid, but I can't see it. I know it is around the WHERE clause, but I can't see it.
This question has an accepted answers - jump to answer
Answers
Please can you post your DataTable initialisation code, and the data that is sent from the server,
Colin
Hi Colin;
Here is the initialisation code:
and here is what the server gives:
Change:
to be:
(i.e. add the
false
as the fourth parameter.Without that the second parameter is treated as a value and is bound (i.e. escaped). With it, it won't be allowing the SQL engine to actually execute it.
Allan
Thanks Allan,
That was the trick!