Query works but table shows no data - Suspect SQL issue, but can't see it.

rob1st

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();

<?php
>
```
?>


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.

colin

Please can you post your DataTable initialisation code, and the data that is sent from the server,

Colin

rob1st

Hi Colin;

Here is the initialisation code:

<script type="text/javascript" language="javascript" class="init">

$(document).ready(function() {
  var table = $('#qfm').DataTable( {
    dom: "lBfrtip",
    orderCellsTop: true,
    ajax: "../ajax/at/qfm.php",
    responsive: true,
        columns: [
      { data: "L.LocationName" },
            { data: "A.MATPTag" },
            { data: "A.Room" },
            { data: "T.assetType" },
      { data: "subsystem" },
      { data: "D.discipline" }
        ],
    select: true,
    stateSave: false,
    "autoWidth" : false,
    buttons: [],
    "pageLength": 10,
    "lengthMenu": [10, 25, 50, 100, 250]
  } );

    table.button().add(
      null, { extend: "colvis", collectionLayout: 'fixed three-column' },
    );

    table.button().add(
      null, { extend: "excel" },
    );
} );
</script>

and here is what the server gives:

{
  "data": [
    
  ],
  "options": [
    
  ],
  "files": [
    
  ],
  "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
        }
      ]
    }
  ]
}

allan

Change:

$q->where('A.lastUpdated', 'DATE_SUB(NOW(), INTERVAL '.$t.' day)', '<');

to be:

$q->where('A.lastUpdated', 'DATE_SUB(NOW(), INTERVAL '.$t.' day)', '<', false);

(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

rob1st

Thanks Allan,

That was the trick!