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
– Haider Ali, Commented Mar 15, 2018 at 10:08
Poete_Maudit has described its database here : stackoverflow.com/questions/49294704 — Syscall
– Syscall, Commented Mar 15, 2018 at 10:08
Thanks for your comments. Yes, I have stored it as text in the database. — Outcast
– Outcast, Commented Mar 15, 2018 at 10:09

Lawrence Cherone · Accepted Answer · 2018-03-15 10:10:19Z

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, 2018 at 10:10

Lawrence Cherone

46.7k7 gold badges66 silver badges107 bronze badges

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

1 Comment

Outcast Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related