Need a grouping option in mysql

Question

I have a Mysql table like this:

+--------+--------------+
| idform | user_id      |
+--------+--------------+
| 17     |            2 |
| 16     |            2 |
| 15     |            2 |
| 14     |            2 |
| 13     |           18 |
| 12     |           18 |
| 11     |           18 |
| 10     |           18 |
| 9      |           18 |
| 8      |            1 |
| 6      |            2 |
| 5      |            2 |
| 3      |            2 |
| 1      |            2 |
+--------+--------------+
14 rows in set (0.00 sec)

I need a query that gives me a result like this:

+----------------+--------------+
| idform         | user_id      |
+----------------+--------------+
| 17,16,15,14    |            2 |
| 13,12,11,10,9  |           18 |
| 8              |            1 |
| 6,5,3,1        |            2 |
+----------------+--------------+
4 rows in set (0.00 sec)

I tried to use GROUP_CONCAT() function of MYSQL but i couldn't make the result look like this. All i want to do is, MYSQL return the results in order but creates a new group for new user. Create a new group add ids with comma, then on a new user_id, create a new group.

I know i can make it by programmatic way with PHP but if i make it with PHP, i have some problems on pagination.

Any idea how to do this?

Thank you by now.

EDIT: I also tried the query like this: SELECT GROUP_CONCAT(idform) FROM story GROUP_BY user_id. But it gives the result like this:

+---------------------+--------------+
| idform              | user_id      |
+---------------------+--------------+
| 8                   |            1 |
| 1,3,5,6,14,15,16,17 |            2 |
| 9,10,11,12,13       |           18 |
+---------------------+--------------+
3 rows in set (0.00 sec)

`SELECT group_concat(idform) FROM your_table group_by user_id` — skelwa, Jul 27 '18 at 09:16
Thank you for the answer, but it gives a result that has grouped by user_id. It is not the thing i want. — Ben Perry, Jul 27 '18 at 09:22

score 0 · Accepted Answer · answered Jul 27 '18 at 09:30

You need to compare consecutive user-id's and after comparing assign each group a number. Later on, you can use group_concat over the data with that group_number.

I think below query should work for you.

SELECT GROUP_CONCAT(idform)
FROM (
    SELECT
    story.*
    , @groupNumber := IF(@prev_userID != user_id, @groupNumber + 1, @groupNumber) AS gn
    , @prev_userID := user_id
    FROM story
  , (SELECT @groupNumber := 0, @prev_userID := NULL) subquery
) sq
GROUP BY gn;

Need a grouping option in mysql

1 Answers1