Change query or data with foreach loop

Question

I am trying to make my data by passing an exam id... the numbers are my student numbers. The data is stored in my db like this..

Here is my php code

$sql = "SELECT number, question_id, answer FROM `questions_answers` WHERE exam_id=" . $id;

$command = Yii::app()->db->createCommand($sql);
$data = $command ->query();

This is my for each loop, i am writing to csv file.

foreach($data as $result) {
   fwrite($fp, $result['number'] . "," . $result['answer'] . "\r\n");          
}

This is giving me result like this..

I want my result to be like this.. Where 2 is the answer of Q1 giving by number 3100000123, then 3 is answer of Q2, 1 is answer of Q3. Similarly for next student number.. I have tried different things but none of them worked for me. How can i achieve my data like this?

You are looking for pivot tables. Plenty of answers here on SO. You can get the result with a single query — Lelio Faieta, Apr 26 '17 at 06:33
How will i query with pivot table? any example according to my requirements above — , Apr 26 '17 at 06:38
Search this so site for pivot table with sql. You will literally find tons of examples — Lelio Faieta, Apr 26 '17 at 06:39
Try http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Vinoth_S, Apr 26 '17 at 06:40

score 3 · Answer 1 · answered Apr 26 '17 at 08:34

3

try this.

 SELECT number, SUM( IF( question_id =1, answer, NULL ) ) AS Q1, 
 SUM( IF( question_id =2, answer, NULL ) ) AS Q2,
 SUM( IF( question_id =3, answer, NULL ) ) AS Q3,
 FROM test
 GROUP BY number;

answered Apr 26 '17 at 08:34

PHP Dev

483
3
16

score 1 · Accepted Answer · answered Apr 26 '17 at 07:02

Try this way:

$newArr = array();
foreach($data as $result) {
    $newArr[$result['number']][] = $result['answer'];
}

foreach($newArr as $number=>$answer) {  
    $answer_sring = implode(",",$answer);
    fwrite($fp, $number . ", ".$answer_sring. "\r\n");                  
}

score 0 · Answer 3 · answered Apr 26 '17 at 06:47

0

You can use GROUP_CONCAT

$sql = "SELECT number, GROUP_CONCAT(question_id) question_id, GROUP_CONCAT(answer) answer FROM `questions_answers` WHERE exam_id=" . $id ." GROUP BY number";

answered Apr 26 '17 at 06:47

Abdullah Al Shakib

2,034
2
15
16

Satwik Nadkarny · Answer 4 · 2017-04-26T07:21:48.300

You can make use of PIVOT. Let's say your source table is named tab. Then you make use of the PIVOT in the following way :

SELECT  *
FROM    
(SELECT     question_id,
            answer,
            number
 FROM       tab
) src
PIVOT
(
  MAX(answer)
  FOR question_id IN ([1], [2], [3])
) piv

You would assign this to the $sql variable :

$sql = '
    SELECT  *
    FROM    
    (SELECT     question_id,
                answer,
                number
     FROM       tab
    ) src
    PIVOT
    (
      MAX(answer)
      FOR question_id IN ([1], [2], [3])
    ) piv'

You can see this working here -> http://rextester.com/RJDP17408

Hope this helps!!!

Change query or data with foreach loop

4 Answers4