Help with PHP Query in JSON

christh

Hi

I hope somebody can help me.

I have been through the forum looking at examples trying to get my JSON to return in the correct format for Datatables (wrapped in a aaData array and turned into an element?) I just seemed to be going round in circles and am starting to question my intelligence.

Would somebody be kind enough to tell me what changes I should make to the JSON output to my current PHP code below?

[code]
<?php
header("Content-type: application/json");
$data = array();
$json = array();

include 'connect.php';

$result = mysql_query("SELECT * FROM test");
while($row = mysql_fetch_array($result)){
$json['col1'] = $row['col1'];
$json['col2'] = $row['col2'];
$json['col3'] = $row['col3'];
}

$data[] = $json;
echo json_encode($data);
?>
[/code]

I've looked at the Server side (PHP) code example and can't believe I need all that code to return a correctly formatted JSON string from a PHP query and am guessing the above just needs a little tweak?

Any help greatly appreciated as I have honestly spent the last 24 hrs searching reading and trying to get it to work.

Thanks

Chris

christh

just noticed that I have the $data[] = $json; outside the while loop but my question still stands :)

terry_b

This will work better:

[code]
<?php

header("Content-type: application/json");

$data = array();

include 'connect.php';

$result = mysql_query("SELECT * FROM test");

while($row = mysql_fetch_array($result)){
$data['aaData'][] = array(
'col1' => $row['col1'],
'col2' => $row['col2'],
'col3' => $row['col3'],
);
}

echo json_encode($data);

?>
[/code]

allan

> (wrapped in a aaData array and turned into an element?)

That's an option, but it isn't mandatory. You can use sServerDataProp and mDataProp ( http://datatables.net/blog/Extended_data_source_options_with_DataTables ) to parse about any JSON format there is into a DataTable.

Allan

christh

Allan

Just what I needed - Many thanks for that.

Chris