[SlugBug] MYSQL query help

[Matthew Gray]

Hi Alan,

I'm not familiar with moodle, but maybe you need to use something
using GROUP BY and GROUP_CONCAT ?
 
maybe something like :
 
SELECT GROUP_CONCAT(mdl_question_answers.id), 
mdl_question_answers.question FROM
mdl_question_answers,mdl_question WHERE
mdl_question.qtype='multichoice' AND
mdl_question_answers.question=mdl_question.id
GROUP BY mdl_question_answers.question

Perhaps using AS to alias the first returned column name to 
something a bit more managable than the full function name. And 
inside GROUP_CONCAT you can set the seperator and sort order 
of the resulting concatenating string.

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
 
I could be way of the mark with this however...
 
 Matt


> Hi,
> 
> I have a moodle installation that has had some problems during an
> upgrade, and i'm trying to repair a table.
> 
> I have a query 
> 
> SELECT mdl_question_answers.id,mdl_question_answers.question FROM
> mdl_question_answers,mdl_question WHERE
> mdl_question.qtype='multichoice' AND
> mdl_question_answers.question=mdl_question.id
> 
> which outputs like this.
> 
> 39      11
> 40      11
> 41      11
> 42      11
> 43      11
> 54      37
> 55      37
> 56      37
> 57      37
> 58      38
> 59      38
> 60      38
> 61      38
> 62      39
> 63      39
> 64      39
> 65      39
> 
> ..
> 
> 
> but i want it like this
> 
> 39,40,41,42,43   11
> 54,55,56,57      37
> 58,59,60,61      38
> 62,63,64,65      39
> 
> I can then use these lines to create some INSERT's into another table
> 
> 
> anybody pass me some clue on this ?
> cheers,
> 
> AED
> -- 
> "If you make decisions about software -- or anything -- based solely
> on short-term cost and benefit, someone with a longer view can easily
> manoeuver you into a trap from which it is hard to escape."  
> 
> 
> 
> _______________________________________________
> SlugBug mailing list
> SlugBug at email-lists.org
> https://www.email-lists.org/mailman/listinfo/slugbug
> 



-- 

Matthew Gray

Solis

T: (0114) 258 5550
F: (0114) 258 5574