CODEWITHSUNDEEP
phpmysql
dimaz
dimaz

Reputation: 43

Convert Mysql Database into Json

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?

Upvotes: 3

Views: 96

Answers (3)

Chaos Fractal
Chaos Fractal

Reputation: 75

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

  1. 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

Upvotes: 0

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38502

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

Upvotes: 1

Ezequiel Fernandez
Ezequiel Fernandez

Reputation: 1074

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

Upvotes: 0

Related Questions