Create html table from a join mysql table

[vincekaribusana]

Hi,

I’ve got a join table in my database, where user_join_id refers to user table and group_join_id refers to group table and it looks like:

user_group_join_id | user_join_id | group_join_id
1 | 21 | 1
2 | 22 | 1
3 | 21 | 2
4 | 23 | 2
5 | 24 | 1
6 | 24 | 2

How can I build a html table to show the values like:

user_id | group_id
21 | 1,2
22| 1
23 | 2
24| 1,2

If i run a query with join I get something like

user_id | group_id
21|1
21|2
22|1
23|2
24|1
24|2

many thanks for your help

[droopsnoot]

As long as you order it by the user_id, you could keep a note of the previous used_id, and only throw a new row break in the html when the user_id changes.

prevuser = "";
for each (row) {
  if (row[user_id] <> prevuser {
    if (prevuser <> "") {
      close table row
      }
    open new table row
    display user id
    }
  display group_id
  prevuser = user_id
}

Pseudo-code, obviously.

[benanamen]

SELECT
user_join_id,
GROUP_CONCAT( group_join_id ) group_id
FROM
your_table_name
GROUP BY
user_join_id

An actual working query, obviously (I assume you can take the results and output the two columns)