BlueSun3k1 BlueSun3k1 - 25 days ago 16
PHP Question

PHP-AJAX: How to populate jquery datatables from a query through php / json array

I've just started to look into Datatables jQuery Plugin and I'm having a it of a hard time getting it to work with my current work.

I normally populate tables using an AJAX callback by getting the values I want from an array, querying the database through a PHP script. For what I've read on the datatables website, something similar is possible but I keep getting errors so I'll just post what I've done so far in hopes someone can help me out.

This is how I call the databale with some ajax parameters.

<script>
$(document).ready( function () {
$('#test_table').DataTable({
"processing": true,
"serverSide": true,
"ajax": {
"url": "test.php",
"type": "POST"
},
"columns": [
{ "data": "id" },
{ "data": "name" },
{ "data": "email" }
]
});
} );
</script>


This is what the php side looks like.

$sql = "SELECT * FROM test_table";
$res = mysqli_query($conn, $sql) or die("Error: ".mysqli_error($conn));

$columns = array(
array('db' => $row['id'], 'dt' => 'id'),
array('db' => $row['name'], 'dt' => 'name'),
array('db' => $row['email'], 'dt' => 'email'),
);

echo json_encode($columns);


however, I get an error saying that "data is not defined". (notice. I read the documentation on the datatables website but I did not exactly followed it step by step. I used this as a reference for what I'm trying to accomplish. Datatables Server Side POST

I am probably going all wrong about this but I didn't want to change my code too much, so I tried an approach I thought would work. If anyone could enlighten me on how to populate datatables by querying the database from a php > json array call, I'd be very grateful.

Thanks in advance,

Answer

Thank you all for your inputs. I figured out a way to make it work.

I wanted to be table to send the data to the datatables within a jquery callback because this would allow me to create my own search outside the datatables. The way I've done it is by running an ajax call that performs the query to the database and then I pass the results of that query to the datatable but the issue was how to format the data in a way that the datatable would accept and how to make it read that data to display on the table.

Simple ajax call and populating the datatable

This code can be further modified (which I will do in my case) but it should give you an idea of how to accomplish what I've been wanting to do. (it works btw).

<script>
$('document').ready(function()
{
    $.ajax({
    type : 'POST',
    url  : 'test.php',
    dataType: 'json',
    cache: false,
    success :  function(result)
        {
            //pass data to datatable
            console.log(result); // just to see I'm getting the correct data.
            $('#test_table').DataTable({
                "searching": false, //this is disabled because I have a custom search.
                "aaData": [result], //here we get the array data from the ajax call.
                "aoColumns": [
                  { "sTitle": "ID" },
                  { "sTitle": "Name" },
                  { "sTitle": "Email" }
                ] //this isn't necessary unless you want modify the header
                  //names without changing it in your html code. 
                  //I find it useful tho' to setup the headers this way.
            });
        }
    });
});
</script>

test.php

This is the simple version I used for testing. My actual code is much more larger as it has several parts for querying and searching.

<?php

$columns = array( 
// datatable column index  => database column name
    0 => 'id',
    1 => 'name',
    2 => 'email',
);

$sql = "SELECT * FROM test_table";
$res = mysqli_query($conn, $sql) or die("Error: ".mysqli_error($conn));

while( $row = mysqli_fetch_array($res) ) {
    $dataArray = array();

    $dataArray[] = $row["id"];
    $dataArray[] = $row["name"];
    $dataArray[] = $row["email"];

}

echo json_encode($dataArray);

?>

This simplifies things a lot for, at least for me. I did not want to have to include additional libraries 'ssp.class.php'. I also did not want to get into PDO. So this has made it a lot more flexible and i hope it helps others that are trying to accomplish the same thing or similar.

Comments