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

Question

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 1

After 24 hours' trial & error, this code has worked for me.

$(document).ready(function () {
    $('#example').DataTable({
        processing: true,
        serverSide: false, // if true, causes Showing **0 to 0 of 0 entries (filtered from NaN total entries)** error in datatable
        ajax: {
            url: 'fetchCustomers.php',
            type: 'POST',
            dataSrc: ''
        },
        columns: [
            { data: 'Name' },
            { data: 'Position' },
            { data: 'Office' },
            { data: 'Age' },
            { data: 'Start_Date' },
            { data: 'Salary' }
        ],
    });
}); // end of $(document).ready(function (){);

Here is code for PHP File:

<?php

$host = "localhost";
$user = "root";
$password = "********";
$dbname = "data_table";

$con = mysqli_connect($host, $user, $password,$dbname);

// Check connection
if (!$con) {  die("Connection failed: " . mysqli_connect_error()); }

$return_array = array();

$query = "SELECT * FROM customer";

$result = mysqli_query($con,$query);

while($row = mysqli_fetch_array($result)){
    $Name = $row['Name'];
    $Position = $row['Position'];
    $Office = $row['Office'];
    $Age = $row['Age'];
    $Start_Date = $row['Start_Date'];
    $Salary = $row['Salary'];

    $return_array[] = array (
                    "Name" => $Name,
                    "Position" => $Position,
                    "Office" => $Office,
                    "Age" => $Age,
                    "Start_Date" => $Start_Date,
                    "Salary" => $Salary
                );
}

// Encoding array in JSON format
echo json_encode($return_array);
?>

Answer 2

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

I wanted to be able 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));
$dataArray = array();
while( $row = mysqli_fetch_array($res) ) {


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

}

echo json_encode($dataArray);

?>

This simplifies things a lot, at least for me. I did not want 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 who are trying to accomplish something similar.

Answer 3

If you want to use server-side processing, you need to include server-side helper class ssp.class.php and use it as shown in this example.

For example:

// DB table to use
$table = 'test_table';

// 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 object
// parameter names
$columns = array(
    array( 'db' => 'id',    'dt' => 'id' ),
    array( 'db' => 'name',  'dt' => 'name' ),
    array( 'db' => 'email', 'dt' => 'email' )
);

// SQL server connection information
$sql_details = array(
    'user' => '',
    'pass' => '',
    'db'   => '',
    'host' => ''
);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * 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( $_POST, $sql_details, $table, $primaryKey, $columns )
);

Answer 4

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

is the correct format

UPDATE: you dont loop the result