CODEWITHSUNDEEP
phpjqueryjsonajaxdatatables
John Beasley
John Beasley

Reputation: 3075

AJAX JSON load jQuery dataTable

I have this php script in a file named qnams.php that returns JSON:

include ("../include/database.php");

include ("../include/sessions.php");

$select = "SELECT  column1,column2,column3,column4
           FROM  qnamtable WHERE  USER = '$username'";

$query = mysqli_query($dbc, $select) or die(mysqli_error());

$out = array();

while ($row = $query->fetch_array()) {
    $out[] = $row;
}
echo json_encode($out);

mysqli_free_result($query);

I found different posts on how to add the JSON directly into the dataTable, but was a little confused on how to proceed.

On my main page, I have this function at the bottom of the page:

$(function() {
    $('#example1').dataTable({
        "bPaginate": false,
        "bLengthChange": true,
        "bFilter": true,
        "bSort": true,
        "bInfo": true,
        "sScrollY": "auto",
        "sScrollCollapse": true,
        "bAutoWidth": true
    });
});

What do I need to add to this function to include the JSON with all the headers and data?

Upvotes: 1

Views: 624

Answers (4)

John Beasley
John Beasley

Reputation: 3075

All of the other answers here were helpful, however in the end, I merely needed to acquire a recent version of jQuery DataTables. I was using newer code with an older template and that was a main issue affecting my results.

Upvotes: 0

davidkonrad
davidkonrad

Reputation: 85518

First I would say you should use fetch_assoc, not fetch_array(). fetch_array() will include both the associated and the indexed array :

[{"0":"x","column1":"x","1":"y","column2":"y"}]

we just need 

[{"column1":"x","column2":"y"}]

Then here is a fully automated script for populating a 

<table id="example1"></table>

with any JSON as long as it is wellformed (as your PHP script seems to do) :

$.ajax({
    url : 'qnams.php',
    dataType : 'json',
    success : function(json) {
        //build the column definitions for titles and data-indexes 
        var columns = [];
        Object.keys(json[0]).forEach(function(column) {
            columns.push({ title: column, data: column })
        })
        $('#example1').dataTable({      
            data : json,
            columns : columns,

            "bPaginate": false,
            "bLengthChange": true,
            "bFilter": true,
            "bSort": true,
            "bInfo": true,
            "sScrollY": "auto",
            "sScrollCollapse": true,
            "bAutoWidth": true
        })
    }
});

I assume you are using 1.10.x of dataTatables.

Upvotes: 1

jlocker
jlocker

Reputation: 1488

You can use like below,

$(function() {
    $('#example1').dataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "qnams.php" //set your php file location
        "bPaginate": false,
        "bLengthChange": true,
        "bFilter": true,
        "bSort": true,
        "bInfo": true,
        "sScrollY": "auto",
        "sScrollCollapse": true,
        "bAutoWidth": true
    });
});

The html should be like below, Make sure the id should be example1

Set all the headers as below easily.

<table id="example1" class="display" cellspacing="0" width="100%">
  <thead>
    <tr>
      <th>column1</th>
      <th>column2</th>
      <th>column3</th>
      <th>column4</th>
      <th>column5</th>
      <th>column6</th>
    </tr>
  </thead>

  <tfoot>
    <tr>
      <th>column1</th>
      <th>column2</th>
      <th>column3</th>
      <th>column4</th>
      <th>column5</th>
      <th>column6</th>
    </tr>
  </tfoot>
</table>

Check more information from here

Upvotes: 1

Andres Alvites Valladares
Andres Alvites Valladares

Reputation: 11

You need to use the ajax option of Jquery Datatables

ajax : { url: 'the/path/to/php/file/with/jsondata' }

Upvotes: 1

Related Questions