Select rows and display as columns from mySQL database

Question

All, I've got the following mySQL table structure:

Table dj_feedback_answers: answer_id, gig_id, question_id, answer_id
Table dj_feedback_summary: summary_id, user_id, gig_date, tip, summary, why_poor, why_fair
Table dj_feedback_questions: question_id, question_value, question_display
Table dj_feedback_ratings: rating_id, rating_value, rating_display

I'm basically asking users questions and the questions are stored in the dj_feedback_questions table and allowing them to give a rating for each question. The ratings are stored in the dj_feedback_ratings.

Each form can having variable questions so when I submit the questions I store the answers in the dj_feedback_answers. There are other more static fields on the form and those get svaed into the dj_feedback_summary. The summary_id and the gig_id are the keys that connect the two tables for the answers that the user submitted.

This means that there can be multiple rows in the dj_feedback_answers for a single row in the dj_feedback_summary table.

What I would like to do is basically be able to utilize the results in a PHP table. So I'd like my results to look something like this (assuming there are three questions in the dj_feedback_questions table):

summary_id, user_id, gig_date, tip, summary, why_poor, why_fair, question_1, question_2, question_3
1, 2, 07/23/2012, 5, This is a summary, It was poor, It was fair, 3, 4, 5

The values that would be stored for the questions would be the answer_id from the dj_feedback_answers.

I tried to create the following query to get my results. This works ok but it only can display a single question instead of all of the questions.

Select dj_feedback_summary.summary_id, 
       dj_feedback_summary.user_id, 
       dj_feedback_summary.gig_date, 
       dj_feedback_summary.tip, 
       dj_feedback_summary.summary,  
       dj_feedback_answers.question_id, 
       dj_feedback_answers.rating_id, 
       dj_feedback_questions.question_value, 
       users.first_name, users.last_name, 
       dj_feedback_ratings.rating_value
from dj_feedback_summary
join dj_feedback_answers on dj_feedback_summary.summary_id=dj_feedback_answers.gig_id
join dj_feedback_questions on dj_feedback_answers.question_id=dj_feedback_questions.question_id 
join users on dj_feedback_summary.user_id=users.user_id
join dj_feedback_ratings on dj_feedback_ratings.rating_id=dj_feedback_answers.rating_id
where dj_feedback_questions.question_value='Overall Gig'
order by dj_feedback_summary.summary_id DESC

Is there a way to modify my query so it can return my results how I would like them?

Any advice is greatly appreciated!

Thanks

Answer 1

If you need values from multiple rows displayed, and it's not essential that they be in separate columns, then the MySQL GROUP CONCAT function would probably suit your purposes.

That would give you question_1, question_2, and question_3 values concatenated into a single column (if I understand your output intent correctly).

Update: To do a more "genuine" pivot (separating the answers to different questions into different columns), you could use one of the following approaches:

1) Use multiple table aliases: join to the dj_feedback_questions table one time for each question, e.g. something along these lines:

SELECT [...] FROM [...]
LEFT JOIN dj_feedback_questions q1 on q1.question_id = 1
LEFT JOIN dj_feedback_questions q2 on q2.question_id = 2
LEFT JOIN dj_feedback_questions q3 on q2.question_id = 3

You will then need to a) join to dj_feedback_answers similarly (multiple times with aliases), since you get to it via question_id, and you want different questions separated into different columns, and b) aggregate the results and decide how you want to handle nulls, since doing a regular JOIN instead of a LEFT JOIN will cause you to lose the row for any summary that lacks an answer to one or more questions.

2) Break into columns with an IF block: See this SO question (specifically the accepted answer) for an illustration: Transpose mysql query rows into columns