Reputation: 4462
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
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