CODEWITHSUNDEEP
mysqldatabase
katie hudson
katie hudson

Reputation: 2893

Ordering MySQL query results based on an id value

I have a questions table and an answers table. I wont display all fields, but the basic structure is as follows

questions table

+-----------------+----------------------+------+-----+---------+----------------+
| Field           | Type                 | Null | Key | Default | Extra          |
+-----------------+----------------------+------+-----+---------+----------------+
| id              | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| name            | varchar(50)          | NO   | UNI | NULL    |                |
+-----------------+----------------------+------+-----+---------+----------------+

answers table

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| text          | mediumtext       | NO   |     | NULL    |                |
| question_id   | int(10) unsigned | NO   | MUL | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+

What I am attempting to do is obtain all text answers for a particular set of questions, as one result set. As such, I am doing the following

$sql = "select text, GROUP_CONCAT(text SEPARATOR ',') AS text 
        FROM answers 
        WHERE question_id IN (11, 13, 14, 15, 22, 72);

This results in something like this, which seems correct 

Array
(
    [0] => Array
        (
            [text] => data1,data2,data3,data4,data5,data6
        )
)

However, what I am now trying to do is order this result set in a particular order. At the moment I have this

$sql = "select text, GROUP_CONCAT(text SEPARATOR ',') AS text 
        FROM answers 
        WHERE question_id IN (11, 13, 14, 15, 22, 72) 
        ORDER BY FIELD(question_id, 13, 11, 72, 15, 22, 14);

But the result is still in the same order as above.

How can I get the results in the order I am after?

Thanks

Upvotes: 1

Views: 33

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Group_Concat() syntax allows specification of explicit sorting of the expressions/fields being aggregated. In you current attempted query, external ORDER BY clause does not server any purpose, as GROUP_CONCAT() is effectively aggregating all of them in a single row.

SELECT 
  GROUP_CONCAT(text 
               ORDER BY FIELD(question_id, 13, 11, 72, 15, 22, 14)
               SEPARATOR ',') AS text 
FROM answers 
WHERE question_id IN (11, 13, 14, 15, 22, 72)

Upvotes: 1

Tudor Constantin
Tudor Constantin

Reputation: 26861

Try with a subquery:

$sql = "select text, GROUP_CONCAT(text SEPARATOR ',') AS text 
        FROM (SELECT text FROM answers WHERE question_id IN (11, 13, 14, 15, 22, 72) ORDER BY question_id) t;

Upvotes: 1

Related Questions