CODEWITHSUNDEEP
phpsqlyii
user3076307
user3076307

Reputation:

Change query or data with foreach loop

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..

enter image description here

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..

enter image description here

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?

enter image description here

Upvotes: 2

Views: 448

Answers (4)

PHP Dev
PHP Dev

Reputation: 503

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;

Upvotes: 3

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5135

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!!!

Upvotes: 0

Ahmed Ginani
Ahmed Ginani

Reputation: 6650

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");                  
}

Upvotes: 1

Abdullah Al Shakib
Abdullah Al Shakib

Reputation: 2044

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";

Upvotes: 0

Related Questions