CODEWITHSUNDEEP
mysql
Vishnu Gopinath
Vishnu Gopinath

Reputation: 75

group users by same field value

I have table with different users and their preference, like this 

+--------+----------+--------------+
| id     | user_id  | preference_id|
+--------+----------+--------------+
|  15145 | 12       |    1         |
|  15146 | 13       |    1         |
|  15148 | 15       |    2         |
|  15150 | 25       |    2         |
|  15314 | 26       |    3         |
|  15315 | 29       |    1         |
|  15316 | 30       |    3         |
|  15317 | 32       |    3         |
|  15318 | 33       |    1         |
|  15319 | 34       |    2         |
+--------+----------+--------------+

I need group this users based on preference id. like this:

+--------+----------+--------------+
| id     | user_id  | preference_id|
+--------+----------+--------------+
|  15145 | 12       |    1         |
|  15146 | 13       |    1         |
|  15315 | 29       |    1         |
|  15318 | 33       |    1         |
+--------+----------+--------------+

+--------+----------+--------------+
| id     | user_id  | preference_id|
+--------+----------+--------------+
|  15314 | 26       |    3         |
|  15316 | 30       |    3         |
|  15317 | 32       |    3         |
+--------+----------+--------------+

+--------+----------+--------------+
| id     | user_id  | preference_id|
+--------+----------+--------------+
|  15148 | 15       |    2         |
|  15150 | 25       |    2         |
|  15319 | 34       |    2         |
+--------+----------+--------------+

I checked this mysqli query but this showing not give my needs.

$sql="SELECT * FROM gic_user_preference GROUP BY preference_id";
$result=mysqli_query($createCon->connect(), $sql);
while ($arr = mysqli_fetch_assoc($result)) {
    var_dump($arr);
}

Upvotes: 0

Views: 56

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31812

You can save the data in groups in PHP while fetching it from DB using an associative array indexed by your group field:

$groupedData = [];
while ($arr = mysqli_fetch_assoc($result)) {
    $groupedData[$arr['preference_id']][] = $arr;
}

$groupedData will then look like

[
    1 => [
        ['id' => 15145,'user_id' => 12, 'preference_id' => 1],
        ['id' => 15146,'user_id' => 13, 'preference_id' => 1], 
        //...
    ],
    2 => [
        ['id' => 15148, 'user_id' => 15, 'preference_id' => 2],
        ['id' => 15150, 'user_id' => 25, 'preference_id' => 2],
        //...
    ],
    3 => [
        // ...
    ],
    // ...
]

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

seems you need order by 

SELECT * FROM gic_user_preference ORDER  BY preference_id

and check for preference_id change in your loop ... otherwise you must perform a select for each distinct preference_id and show the result

Upvotes: 1

Related Questions