Convert Mysql Database into Json

Question

I have a mysql database, I want to change it to json format with results like the one below

["aaa","bbb","ccc"]

but when I made the code using PHP as below

<?php
require_once("../con.php");

$list = mysqli_query($con,"SELECT * FROM user WHERE reff='admin'");
$r = array();
while($data = mysqli_fetch_array($list)) {
    $r[] = array($data['username']);
}
echo json_encode($r, JSON_PRETTY_PRINT);
?>

the results are different from what I want, the following results

[ [ "aaa" ], [ "bbb" ], [ "ccc" ] ]

is there any suggestion how to fix it?

Just replace this line $r[] = array($data['username']); with this one $r[] = $data['username']; — Accountant م
– Accountant م, Commented Aug 23, 2019 at 1:33

A l w a y s S u n n y · Accepted Answer · 2019-08-23 01:48:48Z

1

The best way to do is $r[] = $data['username']; instead of $r[] = array($data['username']); because with this line $r[] = array($data['username']); you are trying to push username as an array not a username string value every time on your $r that's why it creates extra brackets [] surrounding your username value.

but if you don't want to change your existing code then you can use this to get result as expected with this extra line of code $r = array_merge(...$r), here ... known as the splat operator

edited Aug 23, 2019 at 1:48

answered Aug 23, 2019 at 1:42

A l w a y s S u n n y

38.8k9 gold badges68 silver badges120 bronze badges

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

Comments

Ezequiel Fernandez · Accepted Answer · 2019-08-23 01:39:44Z

0

You are assigning an array to another array. see this attached change:

<?php
require_once("../con.php");

$list = mysqli_query($con,"SELECT * FROM user WHERE reff='admin'");
$r = [];
while($data = mysqli_fetch_array($list)) {
    $r[] = $data['username'];
}
echo json_encode($r, JSON_PRETTY_PRINT);
?>

answered Aug 23, 2019 at 1:39

Ezequiel Fernandez

1,08413 silver badges20 bronze badges

Comments

Chaos Fractal · Accepted Answer · 2019-08-23 02:03:54Z

0

1.

Replace $r[] = array($data['username']);

To $r[] = $data['username'];

2.

"SELECT username FROM user WHERE reff='admin'" // * -> username 
// in while
$r[] = $data[0];
or
$r[] = current($data);

3.format in sql

SELECT CONCAT('[',GROUP_CONCAT('"',username,'"'),']') FROM user WHERE reff='admin'

see. https://dba.stackexchange.com/questions/192208/mysql-return-json-from-a-standard-sql-query

if your mysql v5.7.22+

use function JSON_ARRAY、JSON_OBJECT、JSON_QUOTE in sql https://dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html

edited Aug 23, 2019 at 2:03

answered Aug 23, 2019 at 1:43

Chaos Fractal

755 bronze badges

Collectives™ on Stack Overflow

Convert Mysql Database into Json

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related