Mysql query for displaying data

Question

I have a database like this:

id        User_id    Question_id     Answer
1           john      Question_1        b
2           john      Question_2        a
3           john      Question_3        d
4           harry     Question_1        a
5           harry     Question_2        a  
6           harry     Question_3        c 
7           colleen   Question_1        c
8           colleen   Question_2        a
9           colleen   Question_3        b

I want to display the content of the above data in the following format-

id     User_id      Question_1      Question_2        Question_3       
1       john             b               a                 d
2       harry            a               a                 c
4       colleen          c               a                 b

How can I achieve this using sql? I am using mysql and php.

@nicholas-- I tried to, couldn't get the data in the format I want. — Neeraj Dangol
– Neeraj Dangol, Commented Jul 11, 2012 at 9:55

PoeHaH · Accepted Answer · 2012-07-11 09:57:47Z

3

You're looking for a PIVOT table:

http://en.wikibooks.org/wiki/MySQL/Pivot_table

answered Jul 11, 2012 at 9:57

PoeHaH

1,9363 gold badges29 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Puggan Se · Accepted Answer · 2012-07-11 11:39:29Z

1

SELECT
  id,
  User_id,
  MAX(IF(querstion_id = 'Question_1', answer, NULL)) AS Question_1,
  MAX(IF(querstion_id = 'Question_2', answer, NULL)) AS Question_2,
  MAX(IF(querstion_id = 'Question_3', answer, NULL)) AS Question_3
FROM answers
GROUP BY User_id

EDIT 1
added a php version, see comment

$table = array();
$query = "SELECT * FROM answers ORDER BY User_id, Question_id";

... fetch data depending on what interface you use ...

foreach/while(....)
{
   if(!isset($table[$result['User_id']]))
   {
       $table[$result['User_id']] = array();
       $table[$result['User_id']]['id'] = $result['id'];
       $table[$result['User_id']]['User_id'] = $result['User_id'];
   }

   $table[$result['User_id']][$result['Question_id']] = $result['Answer'];
}

Edit 2 how to display it:

then just display it as you would done with a normal query, this is how i usaly converts php arrays to html:

echo '<table><thead>';
echo '<tr><th>' . implode('</th><th>', array_keys(current($table))) . '</th></tr>';
echo '</thead><tbody>';
foreach($table as $row)
{
    echo '<tr><td>' . implode('</td><td>', $row) . '</td></tr>';
}
echo '</tbody></table>';

edited Jul 11, 2012 at 11:39

answered Jul 11, 2012 at 10:11

Puggan Se

5,8462 gold badges25 silver badges51 bronze badges

6 Comments

Neeraj Dangol Over a year ago

The number of Questions may be from 25-30, and there may be 100's of users, will there be any performance issues with the above query ?? Thanks for the answer......

PoeHaH Over a year ago

Pivot would still be better. For this query you need to know how many questions there are

Puggan Se Over a year ago

@Neeraj i think a php solution may be faster, and at least more dynamic, see updated answer

Puggan Se Over a year ago

@PoeHaH I read the link you sugested, and it describe how to build a pivot just like i did, i see no diference, so how is it better?

Neeraj Dangol Over a year ago

@PugganSe Thanks for the solution, but i'm a bit confused, How can i display the data in a tabular format(which I mentioned in my question) using your above php solution?

|

jaychapani · Accepted Answer · 2012-07-11 10:03:15Z

0

Try this...

SELECT tbl.user_id,
       (SELECT answer
          FROM your_table
         WHERE user_id = tbl.user_id AND question_id = 'Question_1') q1,
       (SELECT answer
          FROM your_table
         WHERE user_id = tbl.user_id AND question_id = 'Question_2') q2,
       (SELECT answer
          FROM your_table
         WHERE user_id = tbl.user_id AND question_id = 'Question_3') q3
  FROM (SELECT DISTINCT user_id
                   FROM your_table) tbl

answered Jul 11, 2012 at 10:03

jaychapani

1,5191 gold badge13 silver badges31 bronze badges

Comments

Ajay Kadyan · Accepted Answer · 2012-07-11 10:11:08Z

0

    select id,User_id,
    sum(Answer*(1-abs(sign(Question_id-1)))) as Question_1,
    sum(Answer*(1-abs(sign(Question_id-2)))) as Question_2,
    sum(Answer*(1-abs(sign(Question_id-3)))) as Question_3,
    sum(Answer*(1-abs(sign(Question_id-4)))) as Question_4
    from results group by User_id

answered Jul 11, 2012 at 10:11

Ajay Kadyan

1,0893 gold badges13 silver badges36 bronze badges

Comments

Yash Dayal · Accepted Answer · 2012-07-11 10:13:27Z

0

If you are sure of only 3 fields you can use something like this. Do remember that this query is quite heavy so you may be better of processing this in PHP instead..

select a.user_id, 
a.question_id as Question_1, 
b.question_id as Question_2, 
c.question_id as Question_3 

from TABLE_NAME a, 
TABLE_NAME b, 
TABLE_NAME c  

where a.question_id="Question_1" and a.user in(select user_id from TABLE_NAME) 
and b.question_id="Question_2" and b.user_id =a.user_id 
and c.question_id="Question_3" and c.user_id =a.user_id

edited Jul 11, 2012 at 10:13

answered Jul 11, 2012 at 10:06

Yash Dayal

1,1748 silver badges7 bronze badges

Collectives™ on Stack Overflow

Mysql query for displaying data

5 Answers 5

Comments

6 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related