Return JSON from MySQL with Column Name

Question

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 1

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);
}

Answer 2

You should try while($row = mysqli_fetch_assoc($result)).

It should return the result with the respective fieldnames.

You can find the manual page here.

Answer 3

You should use mysqli_fetch_assoc here instead so that it returns the key as the column names. mysqli_fetch_row returns numeric array keys instead.

Try this:

if (mysqli_num_rows($result) > 0) {
   $jsonData[] =  mysqli_fetch_assoc($result);
}
$json = json_encode($jsonData, JSON_PRETTY_PRINT);