Shekhar Chatterjee Shekhar Chatterjee - 4 months ago 7
MySQL Question

Return JSON from MySQL with Column Name

I use PHP to fetch a row from MySQL and then encode it into JSON using the following code

$jsonData = array();
if(mysqli_num_rows($result) > 0){
while ($array = mysqli_fetch_row($result)) {
$jsonData[] = $array;
}
$json = json_encode($jsonData);
echo stripslashes($json);
}`


However, I only get the the row values. I want rows values along with their column names. Currently it returns the following JSON.

[["shekhar","Shekhar Chatterjee","https://graph.facebook.com/1254850974526564/picture","0"]],[["shek","Shekhar Chatterjee","","0"]]


I would like to have the following output:

[{
"user":"shekhar",
"name":"Shekhar Chatterjee",
"url":"https://graph.facebook.com/1254850974526564/picture",
"stat":"0"
},{
"user":"shekhar",
"name":"Shekhar Chatterjee",
"url":"https://graph.facebook.com/1254850974526564/picture",
"stat":"0"
}]

Answer

Use mysqli_fetch_assoc()

Here you go

$jsonData = array();
if(mysqli_num_rows($result) > 0){
while ($array = mysqli_fetch_assoc($result)) {
    $jsonData[] = $array;
}
$json = json_encode($jsonData);
echo stripslashes($json);
}