DataTables not loading server side data from MYSQL
DataTables not loading server side data from MYSQL
jmyrtle
Posts: 52Questions: 3Answers: 0
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 )
);
This question has an accepted answers - jump to answer
Answers
Hi @jmyrtle ,
Could you paste the returned data from the server, please.
Cheers,
Colin
@colin I cannot post the data as it contains personal information. However, I can tell you how the code starts.
The
drawvalue 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
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.
The data you pasted has
"draw":0- see the protocol here.I was referring to the structure of the data - whether you've got nested structures, that kind of thing.
Cheers,
Colin
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:
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
drawparameter should be populated by the PHP file automatically.Allan
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.
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
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.
There is nothing obvious there. If you click the "Console" tab in your Chrome inspector, does it show any errors?
Allan
This is the only error I can see, but I don't think it has anything to do with my script:
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
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:
I refreshed the page and the code changed to this:
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?
The solution has been resolved on a different forum. Thank you.
Are you able to say what the issue was? It may be useful for other who come across this thread.
Cheers,
Colin
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
Thanks for the update.