CODEWITHSUNDEEP
phpjson
Outcast
Outcast

Reputation: 5117

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

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?

Upvotes: 0

Views: 59

Answers (1)

Lawrence Cherone
Lawrence Cherone

Reputation: 46620

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.

Upvotes: 1

Related Questions