For json_encode
to return a JSON array of array you need a numerically indexed array.
This can be obtained with the following, generic function:
function SqlSelectToJsonTable($result,$withNames=false){
// result a mysqli::query object,
// withNames: boolean, should the column names be returned as first row
$return_arr = array();
echo $result->num_rows;
if ($result->num_rows > 0){
if($withNames == true){
$return_arr[0] = array();
$fields = $result->fetch_fields();
foreach ($fields as $field){
echo "column: ".$field->name." ";
array_push($return_arr[0],$field->name);
}
}
while($row = $result->fetch_assoc()) {
$return_arr[]= array_values($row);
}
} else {
echo "0 results";
}
return json_encode($return_arr);
}
Used as in the following:
<?php
$servername = "localhost";
$username = "user";
$password = "password";
$dbname = "test";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, title FROM Posts";
$result = $conn->query($sql);
$jsonTable = SqlSelectToJsonTable($result);
echo '<br/>'.$jsonTable;
echo "<script type=\"text/javascript\">
var jsTable = JSON.parse('".$jsonTable."');
</script>";
$conn->close();
?>
Where jsTable will effectively be an array of arrays. Note the option withNames
allowing to add the column names as the first row, usefull if you try to pass this array to javascript for google visualization.