CODEWITHSUNDEEP
phpmysql
Kuldeep Dangi
Kuldeep Dangi

Reputation: 4462

Get all(one to many) records belongs to a user as an array from mysql using php

I have a table as below

user    category
1          157
1          158
2          158
2          159
3          157

Required output using PHP is

[
    1 => [157,158],
    2 => [158,159],
    3 => [157]
]

One solution could be get all result from mysql & then run a foreach on it like this

foreach ($result as $row) {
    $finalResult[$row['user']][] = $row['category'];
}

But is there any other optimal way of doing it?

Upvotes: 0

Views: 42

Answers (1)

Rajdeep Paul
Rajdeep Paul

Reputation: 16963

Use GROUP_CONCAT() function for this.

Here's the reference:

So your query should be like this:

SELECT user, GROUP_CONCAT(category SEPARATOR ',') AS categories FROM your_table GROUP BY user;

Output:

+------+------------+
| user | categories |
---------------------
|   1  |  157,158   |
---------------------
|   2  |  158,159   |
---------------------
|   3  |    157     |
+-------------------+

Edited:

// suppose $conn is your connection handler

$finalResult= array();
$query = "SELECT user, GROUP_CONCAT(category SEPARATOR ',') AS categories FROM your_table GROUP BY user";

if ($result = $conn->query($query)) {

    while ($row = $result->fetch_assoc()) {
        $finalResult[$row['user']] = explode(",", $row['categories']);
    }

}

Upvotes: 1

Related Questions