Alex Alex - 2 months ago 6
MySQL Question

Grouping together in HTML similar rows of a MySQL table

I have a MySQL database table where I save the feedback (column 'feed') from users.
They can both ask questions (type = que, with ref = 0) and reply (type = ans) to the posted questions.
The 'ref' column shows the 'id' of the question they refer to:

id username feed type ref
1 mr_x question_1 que 0
2 mrs_y question_2 que 0
3 mr_z answer_to_2 ans 2
4 mr_z answer_to_1 ans 1
5 mr_x answer_to_2 ans 2


Now, I am trying to show this table in HTML. I would like to bring all the answers with the same 'ref' together, and show them below the question they refer to.

A PHP query using ORDER, for example:

"SELECT * FROM feedback ORDER BY ref ASC";


doesn't work here.

My desired output in HTML would be:

mr_x question_1 que 0
mr_z answer_to_1 ans 1
mrs_y question_2 que 0
mr_z answer_to_2 ans 2
mr_x answer_to_2 ans 2


I probably need to count all the answers with the same 'ref', and build a rowspan.
Has anybody ever had a similar challenge?

Answer

The following query will return the records from the feedback table ordered by the parent id, with the record from the parent thread appearing before the children.

SELECT f.*
FROM feedback f
ORDER BY CASE WHEN f.ref = 0 THEN f.id ELSE f.ref END,
         f.id

Explanation:

The ORDER BY uses two criteria for ordering. First, it orders by the id, when the ref be zero (indicating a parent thread), or the ref when it is not zero, indicating a child thread. Then, to place the parent thread before the children, it orders next by the id. This works, because a parent thread will always have an id which is less than its children.

Comments