May 16, 2013
Need to know if mysql query can get this type of result
Sravis’s Question:
I have a Table structure like below
+--------+---------+
| cat_id | user_id |
+--------+---------+
| 10 | 1 |
| 10 | 2 |
| 11 | 3 |
| 11 | 4 |
+--------+---------+
I’m trying to get the result like below
Array
(
[cat_id] => 10,
Array
(
[user_id] => 1,
[user_id] => 2
)
)
Array
(
[cat_id] => 11,
Array
(
[user_id] => 3,
[user_id] => 4
)
)
I tried using group it didn’t work, If i try with sub query i get error message “Subquery returns more than 1 row.”
Is it possible to achieve this kind of result using mysql query?
No, MySQL api funcions provide a result set of your records. They cannot customize to your requirements. You have to create your own functions to develop that format.
Start by getting unique category id and they go through them in a loop to get there remaining details.
$mainArray = []; //Create a array to store the records
$result = mysqli_query($conn, "SELECT DISTINCT('cat_id') as catid FROM `yourtable`");
while($row = mysqli_fetch_assoc($result)) {
//Now another query to fetch the users
$res = mysqli_query($conn, "SELECT * FROM `yourtable` WHERE cat_id='$row[0]'");
$users = array(); //array to store users
while($r = mysqli_fetch_assoc($res)) {
$users[] = $r['user_id'];
}
// Now add to your main array
$mainArray[] = array('cat_id' => $row['catid'], "users" => $users);
}
var_dump($mainArray);
