How to produce json output with array of text instead of text

Question

I created a mySQL database with phpMyAdmin in my local server. In this database I stored the names and their favourite NBA teams (along with an id as a primary key for the database). I wrote and run the following php script to retrieve these data from the database and project them on my local web server (XAMPP):

<?php

$dbServername = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName = 'Friends';

$conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);

header('Content-Type: application/json');

$sql = 'SELECT * FROM friends;';
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);

$arr = [];
if ($resultCheck > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        $arr[] = $row;
    }
}
echo json_encode($arr, JSON_PRETTY_PRINT);

However, in this way I take this json output:

[...,
{
    "id": "1",
    "name": "David Belton",
    "team": "["Boston Celtics", "Houston Rockets"]"
},
...]

But I would like to take the following json output:

[...,
{
    "id": "1",
    "name": "David Belton",
    "team": ["Boston Celtics", "Houston Rockets"]
},
...]

Therefore I want to take [Boston Celtics", "Houston Rockets"] for "team" which is an array instead of "[Boston Celtics", "Houston Rockets"]" which is text.

Note that in the column team in the database the values are stored as ["Boston Celtics", "Houston Rockets"] (not json) which is text/string.

How can I do this easily with PHP?

So you want the team property in your output to be a string not an array! — Haider Ali, Mar 15 '18 at 10:08
Poete_Maudit has described its database here : https://stackoverflow.com/questions/49294704 — Syscall, Mar 15 '18 at 10:08
Thanks for your comments. Yes, I have stored it as text in the database. — Outcast, Mar 15 '18 at 10:09
Thanks also for the link. I have made this question so I have this in my mind however I was thinking that it was getting too complex so I thought that I could do it directly in a csv file and then just import in the database as such. — Outcast, Mar 15 '18 at 10:15
Sorry give me one minute please because I may do something incorrectly. — Outcast, Mar 15 '18 at 10:27

score 1 · Accepted Answer · answered Mar 15 '18 at 10:10

1

As it looks like json (though broken), you could decode it before applying it to the array.

Like:

$arr = [];
if ($resultCheck > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        $row['team'] = json_decode($row['team'])
        $arr[] = $row;
    }
}
echo json_encode($arr, JSON_PRETTY_PRINT);

But if it's not json, you might need to do more work.. This is why you should normalise the database and not store multiple values in the same column.

answered Mar 15 '18 at 10:10

Lawrence Cherone

46,049
7
62
106

I tested and it works (if I am not doing it wrong). Apologies to the other user who has suggested a similar solution but I falsely dismissed it. – Outcast Mar 15 '18 at 10:44

How to produce json output with array of text instead of text

1 Answers1