AJAX JSON load jQuery dataTable

Question

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?

So...it would help if you have the most recent version of Jquery DataTables. Acquiring a newer version helped solved a lot of the problems I was having. — John Beasley
– John Beasley, Commented Oct 22, 2015 at 16:57

Andres Alvites Valladares · Accepted Answer · 2015-10-16 16:43:55Z

1

You need to use the ajax option of Jquery Datatables

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

answered Oct 16, 2015 at 16:43

Andres Alvites Valladares

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

jlocker · Accepted Answer · 2015-10-16 17:05:00Z

1

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

edited Oct 16, 2015 at 17:05

answered Oct 16, 2015 at 16:47

jlocker

1,4981 gold badge12 silver badges23 bronze badges

3 Comments

John Beasley Over a year ago

but now how do I go about setting the column headers and adding the table cells with the data?

John Beasley Over a year ago

But I still do not see how to get the JSON values to populate in the table. What am I missing?

jlocker Over a year ago

@HoodCoderMan did you try my code?..When the JSON returns values in order the table will automatically gets populated.

davidkonrad · Accepted Answer · 2015-10-16 17:19:58Z

1

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.

answered Oct 16, 2015 at 17:19

davidkonrad

85.7k17 gold badges211 silver badges273 bronze badges

3 Comments

John Beasley Over a year ago

Both your response and jlocker's below look promising, but I just cannot get either to work. I will continue to test both answers until I am able to get it to work. Thanks.

davidkonrad Over a year ago

@HoodCoderMan, what does not work - are there any errors in the console? As long as you use fetch_assoc i hardly see what could go wrong :) try to console out `json' in the successhandler - do you ever reach it? Perhaps you have some blanks or other garbage in the response.

John Beasley Over a year ago

I am using fetch_assoc. I inadvertently approved an edit by jlocker and it changed to fetch_array. And the console shows no errors. I am still testing. I will get back to you.

halfer · Accepted Answer · 2018-08-04 19:13:28Z

0

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.

edited Aug 4, 2018 at 19:13

halfer

20.2k20 gold badges111 silver badges208 bronze badges

answered Oct 22, 2015 at 16:58

John Beasley

3,09911 gold badges55 silver badges104 bronze badges

Collectives™ on Stack Overflow

AJAX JSON load jQuery dataTable

4 Answers 4

Comments

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related