How to populate Jquery datatable with data retrieved from mysql?
10,500
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"
...
],
]
*/
Author by
Vikram
Updated on June 04, 2022Comments
-
Vikram almost 2 years
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 ??
-
Vikram about 10 yearsI try this but no success. It keeps showing loading message.
-
Vikram about 10 yearsFor 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
-
Kamehameha about 10 years@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 about 10 yearsI 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 about 10 yearsBy 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 about 10 yearsIts working now after having some modifications. one of the modification is like- Use of
$r[] = "$value";
instead of$r[] = "$key $value";
-
snakeing about 4 yearsYour 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");