DataTables not loading server side data from MYSQL

jmyrtle

I'm trying to make a web database application of a list of names and addresses. I used DataTables to make this list in the main application. I believe I set it up correctly, but the data is not being fed into the table. I can tell my PHP is working as I can see the data being pulled in when I view the file in my browser.

datatables.js:

$(document).ready(function() {
    $('#dataTable').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": "../api/server.php" // your php file
    });
});

PHP:

<?php
 
/*
 * DataTables example server-side processing script.
 *
 * Please note that this script is intentionally extremely simple to show how
 * server-side processing can be implemented, and probably shouldn't be used as
 * the basis for a large complex system. It is suitable for simple use cases as
 * for learning.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */
 
// DB table to use
$table = 'members';
 
// Table's primary key
$primaryKey = 'id';
 
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array( 'db' => 'id', 'dt' => 0 ),
    array( 'db' => 'name',  'dt' => 1 ),
    array( 'db' => 'residential_address', 'dt' => 2 ),
    array( 'db' => 'mailing_address', 'dt' => 3 ),
    array( 'db' => 'precinct', 'dt' => 4),
    array( 'db' => 'age', 'dt' => 5 ),
    array( 'db' => 'ethnicity',  'dt' => 6 ),
    array( 'db' => 'gender', 'dt' => 7 ),
    array( 'db' => 'party', 'dt' => 8 ),
    array( 'db' => 'race', 'dt' => 9 ),
    array( 'db' => 'phone', 'dt' => 10 )        
);
 
// SQL server connection information
$sql_details = array(
    'user' => 'root',
    'pass' => '',
    'db'   => 'ccrp_db',
    'host' => 'localhost'
);
 
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */
 
require( 'ssp.class.php' );
 
echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);
?>

colin

Hi @jmyrtle ,

Could you paste the returned data from the server, please.

Cheers,

Colin

jmyrtle

@colin I cannot post the data as it contains personal information. However, I can tell you how the code starts.

{"draw":0,"recordsTotal":46893,"recordsFiltered":46893,"data":[[data-begins-here]]}

colin

The draw value looks wrong - it should match the value sent by the client - generally this starts at 0. If that doesn't help - it would be something in the data, so could you copy an element from the returned data (it doesn't need the exact data, just the 'shape' of it).

Cheers,

Colin

jmyrtle

When you say the draw value, are you talking about the ID in the data? Because the ID value in my data starts at 1, not 0. I don't understand what you mean by copying the shape of the returned data.

colin

When you say the draw value, are you talking about the ID in the data?

The data you pasted has "draw":0 - see the protocol here.

I don't understand what you mean by copying the shape of the returned data.

I was referring to the structure of the data - whether you've got nested structures, that kind of thing.

Cheers,

Colin

jmyrtle

None of my data is nested. It covers my entire screen when I view the file. Do I have to add a draw parameter to my php file? If so, what would it be?

My data structure is this:

{"draw":0,"recordsTotal":46893,"recordsFiltered":46893,"data":[["id","name","residential_address","mailing_address","precinct","age","ethnicity","gender","party","race","telephone"]

allan

Could you use the debugger to upload a trace from your page (unless you can give me a link to it which would be even better)?

The draw parameter should be populated by the PHP file automatically.

Allan

jmyrtle

I ran the debugger in my browser and got this code when I tried to upload configuration: uliguj

I wish I could provide a link, but it is currently run via localhost.

allan

Thank you - unfortunately it doesn't show much information. There isn't even information about the DataTables' configuration there, so either something is going wrong with the debugger, or the JS on your page is going funny.

I'm afraid I really would need a link to the page - are you able to port forward to your localhost server, or put the page up on the web for a little while?

Allan

jmyrtle

These 3 links are screenshots of the debugger tool. Can you tell me if it found an issue?

https://snipboard.io/7ONw15.jpg
https://snipboard.io/9cEeXN.jpg
https://snipboard.io/jQLJST.jpg

If everything looks normal, I'll do my best to send a link to you.

allan

There is nothing obvious there. If you click the "Console" tab in your Chrome inspector, does it show any errors?

Allan

jmyrtle

This is the only error I can see, but I don't think it has anything to do with my script:

A cookie associated with a cross-site resource at http://unsplash.com/ was set without the SameSite attribute. A future release of Chrome will only deliver cookies with cross-site requests if they are set with SameSite=None and Secure. You can review cookies in developer tools under Application>Storage>Cookies and see more details at https://www.chromestatus.com/feature/5088147346030592 and https://www.chromestatus.com/feature/5633521622188032.

allan

That wouldn't cause what you are seeing.

I'm afraid I'm at a bit of a loss without being able to access the page. You can PM it to me by clicking my user name above and then the Send message button if you are able to put it online somewhere.

Thanks,
Allan

jmyrtle

I think I found something:

I went back and double checked my js file to make sure it was loading correctly. When I checked the code in my browser to see what it was calling, it looked like this:

$(document).ready(function() {
    $('#dataTable').DataTable();
});

I refreshed the page and the code changed to this:

$(document).ready(function() {
    $('#dataTable').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": "../api/server.php" // your php file
    });
});

Now I see this:

According to the browser console, it’s showing me that my PHP file is returning a 404. Why would that be happening?

jmyrtle

The solution has been resolved on a different forum. Thank you.

colin

Are you able to say what the issue was? It may be useful for other who come across this thread.

Cheers,

Colin

jmyrtle

If others are doing this, please make sure that your are routing your ajax data file properly.

You can read more about my problem here and the steps I took to solve it:

https://www.sitepoint.com/community/t/populate-a-datatable-with-data-from-a-mysql-database/343331

colin

Thanks for the update.