Datatables warning table id= lorTable - requested unknown parameter id for row 0, column 0 help

ianhaney

I am displaying all the records on a page and it's got all the data showing but the id number is missing and I get the error Datatables warning table id= lorTable - requested unknown parameter id for row 0, column 0 showing on the page and I click OK and it shows the page with the data but the id column value is missing but the rest of the data is displayed

The code I have is below

lorries.php page

id	Lorry Name	Lorry Area	Lorry Day	action

assets/js/custom.js file
$("#lorTable").DataTable({
processing: !0,
serverSide: !0,
serverMethod: "post",
ajax: { url: "app/ajax/lorry_data.php" },
columns: [{ data: "id" }, { data: "lorryname" }, { data: "lorryarea" }, { data: "lorryday" }, { data: "action" }],
}),

I'm new to dataTables, if I need to provide more info or debug, let me know please but I'm not sure how to debug dataTables etc

kthorngren

You are fetching the table data from the server via ajax. If the id is missing then that suggests the server script's data query does not include the id. You can verify the data received by using the browser's network inspector and looking at the XHR response tab.

You will need to debug the server script. Are you using one of the Datatables supplied server scripts?

Kevin

ianhaney

Thank you for the reply, appreciate it

I'm using cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css and the js file cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js

kthorngren

Tha's a pretty old version, I think it was released in 2019. You might consider upgrading to the latest version 2 using the Download Builder. However that is not the issue. It's not a client side issue. The issue is with the data returned from the server. My question is about the server script you are using. Is it supplied by Datatables or your own custom sccript.

Kevin

ianhaney

I'm not 100% sure what the server script is, is that what I posted in the code in the original post at the top or something different?

I did look in the request tab for the network on the page that is having the issue and it says the following, not sure if it helps

{
"draw": "1",
"columns[0][data]": "id",
"columns[0][name]": "",
"columns[0][searchable]": "true",
"columns[0][orderable]": "true",
"columns[0][search][value]": "",
"columns[0][search][regex]": "false",
"columns[1][data]": "lorryname",
"columns[1][name]": "",
"columns[1][searchable]": "true",
"columns[1][orderable]": "true",
"columns[1][search][value]": "",
"columns[1][search][regex]": "false",
"columns[2][data]": "lorryarea",
"columns[2][name]": "",
"columns[2][searchable]": "true",
"columns[2][orderable]": "true",
"columns[2][search][value]": "",
"columns[2][search][regex]": "false",
"columns[3][data]": "lorryday",
"columns[3][name]": "",
"columns[3][searchable]": "true",
"columns[3][orderable]": "true",
"columns[3][search][value]": "",
"columns[3][search][regex]": "false",
"columns[4][data]": "action",
"columns[4][name]": "",
"columns[4][searchable]": "true",
"columns[4][orderable]": "true",
"columns[4][search][value]": "",
"columns[4][search][regex]": "false",
"order[0][column]": "0",
"order[0][dir]": "asc",
"start": "0",
"length": "10",
"search[value]": "",
"search[regex]": "false"
}

ianhaney

I have the following code in my custom.js file

$("#lorTable").DataTable({
processing: !0,
serverSide: !0,
serverMethod: "post",
ajax: { url: "app/ajax/lorry_data.php" },
columns: [{ data: "id" }, { data: "lorryname" }, { data: "lorryarea" }, { data: "lorryday" }, { data: "action" }],
}),

It's got serverSide: !0,

Is that what you needed to know

I got the files/code online but the developer is not replying, does !0 mean it's not using the dataTables server script?

kthorngren

does !0 mean it's not using the dataTables server script?

Its an unclear way to configure boolean values but !0 = true. It would be more clear to use serverSide: true. The client side datatables has server side processing enabled. The request tab shows the server side parameters being sent. You will want to look at the XHR response to see what is returned.

I'm not 100% sure what the server script is

The server script is "app/ajax/lorry_data.php. This is the script to look at for troubleshooting why the id object is not in the JSON response.

Kevin

ianhaney

Ahh ok, how do I find the XHR Response in Firefox browser?

UPDATE: I think I found the XHR response in firefox browser, is it the following?

{
"draw": 1,
"iTotalRecords": "1",
"iTotalDisplayRecords": "1",
"aaData": [
{
"lorryname": "Basildon Lorry",
"lorryarea": "Basildon",
"lorryday": "Monday",
"action": "\n

\n \n

\n \n "
}
]
}

I tried posting the code from the file app/ajax/lorry_data.php but wern't coming out properly

ianhaney

The code for app/ajax/lorry_data.php is below

<?php
require_once '../init.php';

## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value

$searchArray = array();

## Search 
$searchQuery = " ";
if($searchValue != ''){
   $searchQuery = " AND (lorryname LIKE :lorryname) ";
   $searchArray = array(
        'lorryname'=>"%$searchValue%"
   );
}

## Total number of records without filtering
$stmt = $pdo->prepare("SELECT COUNT(*) AS allcount FROM lorries ");
$stmt->execute();
$records = $stmt->fetch();
$totalRecords = $records['allcount'];

## Total number of records with filtering
$stmt = $pdo->prepare("SELECT COUNT(*) AS allcount FROM lorries WHERE 1 ".$searchQuery);
$stmt->execute($searchArray);
$records = $stmt->fetch();
$totalRecordwithFilter = $records['allcount'];

## Fetch records
$stmt = $pdo->prepare("SELECT * FROM lorries WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");

// Bind values
foreach($searchArray as $key=>$search){
   $stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);
}

$stmt->bindValue(':limit', (int)$row, PDO::PARAM_INT);
$stmt->bindValue(':offset', (int)$rowperpage, PDO::PARAM_INT);
$stmt->execute();
$lorRecords = $stmt->fetchAll();

$data = array();

foreach($lorRecords as $row){
   $data[] = array(
      "lorryname"=>$row['lorryname'],
      "lorryarea"=>$row['lorryarea'],
      "lorryday"=>$row['lorryday'],
      "action"=>'
          <div class="btn-group" >
          <a href="index.php?page=lorry_edit&&edit_id='.$row['id'].'" class="btn btn-secondary btn-sm rounded-0" id="lorryEdit_btn" type="button"><i class="fas fa-edit"></i></a>
          </div>
      
      ',
   );
}

## Response
$response = array(
   "draw" => intval($draw),
   "iTotalRecords" => $totalRecords,
   "iTotalDisplayRecords" => $totalRecordwithFilter,
   "aaData" => $data
);

echo json_encode($response);

kthorngren

That shows the server script is not providing the id object.

See the instructions below the Post Comment button to see how to format code snippets.

Posts are formatted using Markdown. To highlight code, please use triple back ticks (```) on new lines before and after the code block.

Kevin

ianhaney

Think I just done it, I added the following line to the file and it's showing the id now with no error

"id"=>$row['id'],

Thank you so much for all your help, time and replies, really appreciate it, is it ok to stick with using the dataTable version I'm using for now until the whole system is built and then look to use the latest cdn version for dataTables? I know I'll need to update the code in the custom.js file but can worry about that when the whole system is done

kthorngren

is it ok to stick with using the dataTable version I'm using for now until the whole system is built and then look to use the latest cdn version for dataTables?

There won't be any fixes applied to Datatables 1.x if you run into issues. If you are building a system I think it would be better to upgrade now rather than fix you code, if problems occur, when upgrading in the future. Plus there have been some changes in 2 that change behaviors from 1.

Kevin

ianhaney

I'll look into upgrade to dataTables 2, it didn't look too much changes to the code, it looked more of things like from

serverSide: !0,

to

serverSide: true

could that mostly be the changes using true instead of !0 or is there quite a bit more to update in the custom,js file I have?

kthorngren

I don't know what all is in the custom.js file but this code is fairly basic:

$("#lorTable").DataTable({
    processing: !0,
    serverSide: !0,
    serverMethod: "post",
    ajax: { url: "app/ajax/lorry_data.php" },
    columns: [{ data: "id" }, { data: "lorryname" }, { data: "lorryarea" }, { data: "lorryday" }, { data: "action" }],
}),

The serverMethod: "post", is a legacy option from Datatables 1.9 (very very old) and is not used anymore. It will be ignored since its not a supported option. If you want to send an ajax POST request instead of GET then use the type option in the ajax option. Like this example.

You shouldn't notice any differences with the rest of the Datatables config with the latest DT 2 code.

Kevin

ianhaney

Cool thank you, I'll use the latest cdn version of dataTables and update the code in the custom.js file

If I get stuck on anything, I'll create a new post topic

Thank you for all your help, really appreciate it