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"
}]

Does it have to be in that format or can you first row just be an array of column names? If your first row is the column names, then you aren't repeating data and you still get the values. — bassxzero
– bassxzero, Commented Jul 18, 2016 at 12:59
The PHP mysqli Manual When all else fails you could try reading that — RiggsFolly
– RiggsFolly, Commented Jul 18, 2016 at 13:02
@bassxzero bad idea. That would just push the requirement on the receiver of the json to maintain the association of a fields vector to each value of the results array. If you design APIs , make its use convenient, practical, and dont inject unsound principles/constructs in the semantics. — YvesLeBorg
– YvesLeBorg, Commented Jul 18, 2016 at 13:05

Saurabh · Accepted Answer · 2016-07-18 13:02:21Z

8

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

answered Jul 18, 2016 at 13:02

Saurabh

7841 gold badge5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PockeTiger · Accepted Answer · 2016-07-18 13:00:49Z

4

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.

answered Jul 18, 2016 at 13:00

PockeTiger

1,0707 silver badges8 bronze badges

1 Comment

PockeTiger Over a year ago

I'm glad I could be some help there :3 Have a nice day.

Indrasis Datta · Accepted Answer · 2016-07-18 13:01:50Z

2

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

answered Jul 18, 2016 at 13:01

Indrasis Datta

8,6182 gold badges18 silver badges32 bronze badges

Comments

Jacob Mulquin · Accepted Answer · 2016-07-18 13:15:33Z

1

Just a note, you don't need to perform an additional addslashes(), you can add JSON_UNESCAPED_SLASHES as the second argument of json_encode().

echo json_encode($jsonData, JSON_UNESCAPED_SLASHES);

answered Jul 18, 2016 at 13:15

Jacob Mulquin

3,6081 gold badge22 silver badges24 bronze badges

2 Comments

Patrick Q Over a year ago

This doesn't really address the question and would probably be better as a comment.

Jacob Mulquin Over a year ago

Agreed, however with mobile app limited comment capability I am unable to get correct formatting of code blocks.

Vaibhav J. · Accepted Answer · 2018-01-13 11:46:32Z

0

Just so if anyone else bumps in to this in year 2018, here is the complete example.

<?php

header("Content-Type: application/json; charset=UTF-8");

$conn = new mysqli("host", "user", "password", "db_name");
$result = $conn->query("SELECT * FROM my_table");
$jsonData = array();
if(mysqli_num_rows($result) > 0){
while ($array = mysqli_fetch_assoc($result)) {
    $jsonData[] = $array;
}
$json = '{"my_data":';
$json .= json_encode($jsonData);
$json .= '}';
echo stripslashes($json);
}

?>

...

answered Jan 13, 2018 at 11:46

Vaibhav J.

4021 gold badge5 silver badges16 bronze badges

Collectives™ on Stack Overflow

Return JSON from MySQL with Column Name

5 Answers 5

Comments

1 Comment

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related