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.

rob1strob1st Posts: 84Questions: 22Answers: 0

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

  • colincolin Posts: 15,103Questions: 1Answers: 2,582

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

    Colin

  • rob1strob1st Posts: 84Questions: 22Answers: 0

    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
            }
          ]
        }
      ]
    }
    
  • allanallan Posts: 61,322Questions: 1Answers: 10,023 Site admin
    Answer ✓

    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

  • rob1strob1st Posts: 84Questions: 22Answers: 0

    Thanks Allan,

    That was the trick!

Sign In or Register to comment.