transpunere randuri in coloane

Discutii despre script-uri si coduri PHP-MySQL, precum si lucru cu XML in PHP.
sterica
Mesaje: 275

transpunere randuri in coloane

Salutare,

Am urmatorul tabel:

Cod: Selectaţi tot

+----+---------+------+----------+
| ID | ID_USER | NOTE | QUESTION |
+----+---------+------+----------+
|  1 |    12   |   3  |     1    |
|  2 |    31   |   2  |     1    |
|  3 |    12   |   9  |     1    |
|  4 |    31   |   8  |     2    |
|  5 |    12   |  10  |     2    |
|  6 |    31   |   3  |     2    |
|  7 |    14   |   4  |     3    |
+----+---------+------+----------+
si vreau sa scot datele din el ca ulterior sa le ajez intr-un tabel HTML sub forma aceasta:

Cod: Selectaţi tot

+----------+----+----+----+
| QUESTION | 12 | 31 | 14 |
+----------+----+----+----+
|     1    |  6 |  2 |  0 |
|     2    |  2 |5.5 |  0 |
|     3    |  0 |  0 |  3 |
+----------+----+----+----+
Valorile 6,2, 5.5 sunt medii ale valorilor din primul tabel.

Am urmatorul cod php

Cod: Selectaţi tot

<?php
 include 'bd_cnx.php';
 $sql = "SELECT
           id_user,
           question,
           AVG(note) AS medium_note
         FROM notes
         GROUP BY id_user, question";

 $result = $conn->query($sql);
 if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
     echo '<tr><th>QUESTION</th>
               <th>'. $row['id_user'] .'</th></tr>';
     echo '<tr><td>'. $row['question'] .'</td><td>' . $row['medium_note'] . '</td></tr>';
    }
}
?>

Codul pe care il am imi genereaza doua coloane si randuri pentru fiecare QUESTION si ID_USER, cum pot face ca sa generez randuri pentru fiecare QUESTION si coloane pentru fiecare ID_USER?
Multumesc!

MarPlo
Salut
Incearca asa (vezi tu logica din cod):

Cod: Selectaţi tot

include 'bd_cnx.php';
$re_out ='No data';
$r_data =[]; //store: [id_user=>[q=>n, ...], ...]
$ar_q =[]; // store question ids [question=>1, ...]

$sql = "SELECT id_user, question, AVG(note) AS medium_note FROM note GROUP BY id_user, question";
$result = $conn->query($sql);
$num_rows = $result->num_rows;

if($num_rows >0) {
  //sets data in $ar_q, $r_data 
  while($row = $result->fetch_assoc()){
    if(!isset($ar_q[$row['question']])) $ar_q[$row['question']] =1;
    if(!isset($r_data[$row['id_user']])) $r_data[$row['id_user']] =[];
    $r_data[$row['id_user']][$row['question']] = $row['medium_note'];
  }

  //build html table
  $re_out ='<table><tr><th>QUESTION</th>';
  $re_out .='<th>'. implode('</th><th>', array_keys($r_data)) .'</th></tr>'; //sets the TH row with the keys of $r_data

  //adds the rows
  foreach($ar_q as $id=>$v){
    //adds the cols
    $re_out .='<tr><td>'. $id .'</td>';
    foreach($r_data as $k=>$ar) $re_out .='<td>'. (isset($ar[$id]) ? number_format($ar[$id], 2) :0) .'</td>';
    $re_out .='</tr>';
  }
  $re_out .='</table>';
}

echo $re_out;

Subiecte similare