How to produce valid json output which includes arrays of text

Question

I created a mySQL database with phpMyAdmin in my local server. In this database I stored the names and the favourite NBA teams of my friends (along with an id as a primary key for the database). However, some of my friends may have two favourite teams. I wrote and run the following php script to retrieve these data from the one table I have in the database (I know that it is not the best way to have one table for many-to-many relationship) and project them on my local web server (XAMPP):

$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 output for one person:

   [{
        "id": "1",
        "name": "David Belton",
        "team": "Boston Celtics"
    }, {
        "id": "1",
        "name": "David Belton",
        "team": "Cleveland Cavaliers"
    }]

However, I would like to get this something like this as output for each specific person:

{
    "id": "1",
    "name": "David Belton",
    "team": ["Boston Celtics", "Cleveland Cavaliers"] 
}

Is this possible in PHP?

If so, is it recommended or should I do this with MySQL back at the database?

I'd suggest a different database structure. One table for users, one table for their favorites (linked by a user ID foreign key). — ceejayoz, Mar 14 '18 at 13:43
If you *must* store it in a single table (you shouldn't), store a comma-separated or JSON-encoded text of the multiple favorites in the single user record. — ceejayoz, Mar 14 '18 at 13:46
Thank you @ceejayoz for your comment. Yes, I know what you mean about the foreign key. However, you are welcome to write this in detail if you want to. — Outcast, Mar 14 '18 at 13:46
No I must not this but I was just wondering how to do this quickly by avoiding foreign keys or joining tables etc... — Outcast, Mar 14 '18 at 13:48
How does this look in MySQL? I believe you should normalize the table st there first. — Onur A., Mar 14 '18 at 13:48
@Poete_Maudit Doing this via a JOIN is fast, easy, and something you should definitely get comfortable with. — ceejayoz, Mar 14 '18 at 13:49
Ok @ceejayoz. I respect this. Do you have any very clear link which explains how to do this? — Outcast, Mar 14 '18 at 13:51
@Poete_Maudit https://stackoverflow.com/questions/14902888/how-should-i-store-user-favorites-in-mysql-table — ceejayoz, Mar 14 '18 at 13:53
Ok thanks. Can I ask you something quick here?: Why using two tables? Usually many-to-many relationships use three arrows for two types of things. — Outcast, Mar 14 '18 at 13:57
Yes, if you have a `teams` table you'd want a `user_teams` ("pivot table") or something that's just a pair of IDs. Since you're currently storing them as strings in the users table, I gave the two-table approach. — ceejayoz, Mar 14 '18 at 14:04
Ok, thanks for the help. If you want to see in more detail what I did with MySQL have a look here: https://stackoverflow.com/questions/49279952/how-to-join-arrays-with-mysql-to-finally-produce-a-valid-json-output-which-inclu. — Outcast, Mar 14 '18 at 14:15

score 0 · Answer 1 · answered Mar 14 '18 at 13:47

You must change the while loop to add to the final array only unique data, based on the ID column:

if ($resultCheck > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        if (!isset($arr[$row['id']])) {
            $arr[$row['id']] = $row;
            $arr[$row['id']]['location'] = [$row['location']];
        } else {
            $arr[$row['id']]['location'][] = $row['location'];
        }
    }
}

echo json_encode(array_values($arr), JSON_PRETTY_PRINT);

But you should consider what @ceejayoz commented.

score 0 · Answer 2 · answered Mar 14 '18 at 13:48

Try this:

$sql = 'SELECT id, name, GROUP_CONCAT(team) as team FROM friends GROUP BY id;';
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);

arr = [];

if ($resultCheck > 0) {
    $key = 0;
    while ($row = mysqli_fetch_assoc($result)) {
        $arr[$key] = $row;
        $arr[$key]['team'] = explode(",", $row['team']);
        $key++;
    }
}

echo json_encode($arr, JSON_PRETTY_PRINT);

score 0 · Answer 3 · answered Mar 14 '18 at 13:49

The typical way of doing this is to have a users table and a user_favorites table. Store the user-specific items in users, and user_favorites would be something like user_id: 1, team: Boston Celtics.

Alternatively (and less best-practice-y), you could store the multiple favorites in a single users record. Depending on your data, a CSV representation (Boston Celtics,Cleveland Cavaliers) or maybe a TEXT field you store a JSON-encoded string into (["Boston Celtics", "Cleveland Cavaliers"]) would work.

The downside of this approach is it's less readily indexable by the MySQL database, so SELECT * FROM users WHERE favorite LIKE '%Boston Celtics%' isn't going to be very fast when you've lots of users.

nope.. there should be 3 tables: `users`, `teams`, `users_favorite_teams`.. the later table's columns being only `user_id`, `team_id` — Mihai Matei, Mar 14 '18 at 13:56
@MateiMihai In general, I agree, but it's not clear OP has/needs a `teams` table at all. It could be just free-text profile data of some kind. — ceejayoz, Mar 14 '18 at 14:05

How to produce valid json output which includes arrays of text

3 Answers3