How to populate Jquery datatable with data retrieved from mysql?

Question

I am trying to populate datatable on some button click with data from the database.

However the following code is working [using text file]

-----Javascript:-----

$('#tblData').dataTable( {
    "bProcessing": true,
    "sAjaxSource": 'data.txt'
} );

-----data.txt-----

{
"aaData": [
 [
   "row 1 col 1 data",
   "row 1 col 2 data",
   "row 1 col 3 data",
   "row 1 col 4 data"
 ],
 [
   "row 2 col 1 data",
   "row 2 col 2 data",
   "row 2 col 3 data",
   "row 2 col 4 data"
 ],
 [
   "row 3 col 1 data",
   "row 3 col 2 data",
   "row 3 col 3 data",
   "row 3 col 4 data"
 ]
 ]
}

How to pass such data fom php ??
The following code is not working [using php/mysql]

-----Javascript-----

$('#tblData').dataTable( {
    "bProcessing": true,
    "sAjaxSource": 'response.php'
} );  


------response.php------

include_once("config.php");
$dataArr = array();
$query = "SELECT data1,data2,data3,data4 from tbl_data";
$result = $conn->query($query);
$result->data_seek(0);
while($row = $result->fetch_array(MYSQLI_ASSOC)){
$ dataArr [] = $row;   // ?? What to do here?
}
echo json_encode($dataArr);

how to make it working ??

Kamehameha · Accepted Answer · 2014-02-03 05:21:17Z

1

In your response.php, around the while loop part, this should work -

$dataArr['aaData'] = Array();
while($row = $res->fetch_assoc()){
    $r = Array();
    foreach($row as $key=>$value){
        $r[] = "$key $value";
    }
    $dataArr['aaData'][] = $r;
} 
header('Content-Type: application/json');
echo json_encode($dataArr);

/*
The output will be of the form, 
{
"aaData": [
 [
    [
       "colname data"
       ...
    ],
 ]
*/

edited Feb 3, 2014 at 5:21

answered Feb 2, 2014 at 21:46

Kamehameha

5,4781 gold badge25 silver badges31 bronze badges

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

4 Comments

Kamehameha Over a year ago

@Vikram Try this now. Removed the curly brackets, but column name and the data have been joined together like a string, like in your question.

Vikram Over a year ago

I am getting this error now -

DataTables warning (table id = 'tblData'): DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error.

Kamehameha Over a year ago

By using jsonencode(), I am ensuring that the json is parsed properly. Try the output on [jsonlint.com/](JSONLINT), that will help you in finding out where the error takes place.

Vikram Over a year ago

Its working now after having some modifications. one of the modification is like- Use of $r[] = "$value"; instead of $r[] = "$key $value";

Works On Mine · Accepted Answer · 2014-02-02 21:46:01Z

0

The way you are JSON encoding the data is the problem..Simply doing a console.log() would help to get things started. But you could also try this,

 PHP Side
 $result = mysql_query("SELECT data1,data2,data3,data4 from tbl_data");          //query
 $array = $conn->query($result);                          //fetch result    
 echo json_encode($array);

 HTML side,
 $(function () 
{

$.ajax({                                      
  url: 'response.php',                        
  data: "",                        

  dataType: 'json',                     
  success: function(data)          
  {
    var id = data[0];  //first attribute            
    var vname = data[1];           //second attribute
    $('#tblData').html("<b>id: </b>"+id+"<b> name: </b>"+vname); //Set output element html
  } 
});

});

The reason it works from a text file is because its plain text..but the result of a DB query frim PHP is an object

answered Feb 2, 2014 at 21:46

Works On Mine

1,1311 gold badge8 silver badges20 bronze badges

1 Comment

sNok3 Over a year ago

Your method is unsecure! mysql_query was deprecated. Instead of using mysql_query, update to MySQLi or even PDO. $result = $conn->query("SELECT data1,data2,data3,data4 from tbl_data");

Abhik Chakraborty · Accepted Answer · 2014-02-02 21:48:04Z

0

In jquery data table displaying dynamic data from server side could be done as

In the client side

oTable = $('#tblData').dataTable({
                "bProcessing": true,
                "bServerSide": true,
                "sAjaxSource": "response",
                "fnServerParams": function ( aoData ) {
                        aoData.push( { "name": "more_data", "value": "my_value" } );
                }
        });

In the serve side you need to do as

$output = array(
          "sEcho"=>intval($_GET['sEcho']),
          "iTotalRecords"=>$iTotal, // total number of records 
          "iTotalDisplayRecords"=>$iFilteredTotal, // if filtered data used then tot after filter
          "aaData"=>array()
        );     

while($row = $result->fetch_array(MYSQLI_ASSOC)){
  $output["aaData"][] = $row ;
}

echo json_encode( $output );

I am working on building an opensource CRM www.sqcrm.com, you can check the code, for data display I have used Jquery Data table. This may help you to use different functionality of Data Table.

answered Feb 2, 2014 at 21:48

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

2 Comments

Vikram Over a year ago

I try this but no success. It keeps showing loading message.

Vikram Over a year ago

For second try- dataTable shows blank records with this error- DataTables warning (table id = 'tblData'): Requested unknown parameter '0' from the data source for row 0

Collectives™ on Stack Overflow

How to populate Jquery datatable with data retrieved from mysql?

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related