Group data from table in PHP

Asked by At

I have a table sql like this

post_id    point    user_id
5          1.0      1
6          1.5      1
7          0.0      2
8          1.5      3
9          1.0      3
...

I want to print the data into this ( group the posts by user_id )

user_id        post_id(point)                     total point
1              5(1.0)  |  6(1.5)    | ...         2.5
2              7(0.0)  |            | ...         0.0
3              8(1.5)  | 9(1.5)     | ...         3.0
...

I tried something like this, but it returned array

SELECT * FROM table WHERE post_id = ?

I'm sorry if this a silly question but I'm newbie in MySQL

4 Answers

2
Qirel On Best Solutions

Run a query where you GROUP BY the user_id. Use GROUP_CONCAT() with CONCAT() to generate your points/posts column. CONCAT() puts together the string to form the postID (points) format, GROUP_CONCAT() puts all the values grouped by that group (in this case the user_id) into one string separated by the separator |.

SELECT user_id, 
       GROUP_CONCAT(CONCAT(post_id, '(', points, ')')  SEPARATOR ' | ') as point_id_posts,
       SUM(points) as totalPoint
FROM table
GROUP BY user_id
1
O. Jones On

This is a job for GROUP BY and GROUP_CONCAT(). Try something like this.

(https://www.db-fiddle.com/f/4sVVgGk6SyEnFE4C3emMp9/0)

SELECT user_id,
       GROUP_CONCAT( CONCAT(post_id, '(', point, ')') ORDER BY post_id SEPARATOR ' | ' ),
       SUM(point) 
  FROM tbl
 GROUP BY user_id
1
Varun Malhotra On

I think This will help you

SELECT user_id,GROUP_CONCAT(CONCAT(post_id,'(',point,')') SEPERATOR ' | ') as post_points,SUM(point) as total_points FROM table_name GROUP BY user_id
0
Pankaj Yadav On

Query to achieve your requirment

SELECT user_id,
       GROUP_CONCAT( CONCAT(post_id, '(', point, ')') ORDER BY post_id SEPARATOR ' | ' ) as post_id_point,
       SUM(point) 
  FROM tbl
 GROUP BY user_id