Вопрос по php, mysql – Как отобразить строку таблицы MySQL в виде столбца

2

У меня есть две таблицы,result а такжеgp.

вresult Таблица у меня примерно такая:

|id||student_id   ||course_code||grade||session  ||level||semester|
|1 ||TR/2213234561||MAT111     ||A    ||2009/2010||100  ||first   |
|2 ||TR/2213234561||MAT112     ||B    ||2009/2010||100  ||first   |
|3 ||TR/2213234561||MAT113     ||C    ||2009/2010||100  ||first   |
|4 ||TR/2213234567||MAT111     ||D    ||2009/2010||200  ||first   |
|5 ||TR/2213234567||MAT112     ||C    ||2009/2010||200  ||first   |
|6 ||TR/2213234567||MAT113     ||C    ||2009/2010||200  ||first   |

затемgp Таблица

|id||student_id   ||session  ||level||semester||gp |
|1 ||TR/2213234561||2009/2010||100  ||first   ||4.2|
|2 ||TR/2213234567||2009/2010||100  ||first   ||3.5|
|3 ||TR/2213234561||2010/2011||200  ||first   ||4.2|
|4 ||TR/2213234567||2010/2011||200  ||first   ||3.5|

То, что я хочу, это так:

|Matriculation||MAT111||MAT112||MAT113||MAT114||GP |
|TR/2213234561||A     ||B     ||D     ||C     ||4.2|
|TR/2213234567||C     ||D     ||E     ||F     ||3.5|

Код курса не является постоянным - это зависит от курса, зарегистрированного студентами

Я сделал это:

<?php
$rst1 = mysql_query("select distinct course_code from result ", $conn);
echo "<table callspacing='4'>";
echo "<tr>";
echo "<td> Matriculation Number </td>";

$c_code = array();
while ($row = mysql_fetch_array($rst1))
{
    $c_code[] = $row['course_code'];
}

foreach($c_code as $c_code)
{
    echo "<td>" .$c_code. "</td>";
}

$sql ="SELECT result.student_id,
       MAX(CASE WHEN course_code = ' $c_code' THEN grade END)  $c_code,
       gp.CTC 
       FROM result 
       JOIN gp  
       ON gp.student_id = result.student_id
       GROUP
       BY student_id";

echo "<td> GP</td>";                         
$rst = mysql_query("$sql",$conn) or die(mysql_error());

while ($row = mysql_fetch_array($rst))
{
    echo "</tr>";
    echo "<tr>";
    echo "<td>" .$row['student_id']. "</td>";
    echo "<td>"  .$row[$c_code]. "</td>";
}

echo "<td>"  .$row[$c_code]. "</td>";
echo "<td>" .$row['CTC']. "</td>";

echo"</tr>";
echo "</table>";
?>

Первым запросом было получить код курса, поскольку курсы не являются константами.

с этим кодом я получил что-то вроде этого:

|Matriculation||MAT111||MAT112||MAT113||MAT114||GP|
|TR/2213234561|
|TR/2213234567|

Но я хотела

|Matriculation||MAT111||MAT112||MAT113||MAT114||GP |
|TR/2213234561||A     ||B     ||D     ||C     ||4.2|
|TR/2213234567||C     ||D     ||E     ||F     ||3.5|

Любое предложение или направление будут высоко оценены.

Ваш Ответ

1   ответ
2

То, что вы хотите сделать, известно как «поворот»; ваши данные и это то, для чего некоторые другие СУБД имеют встроенную поддержку, но MySQL нет (по замыслу, так как разработчики считают, что такие манипуляции принадлежат на уровне представления).

Однако у вас есть несколько вариантов:

  1. Construct a rather horrible MySQL query to perform the pivoting operation manually:

    SELECT student_id AS Matriculation, MAT111, MAT112, gp AS GP
      FROM gp
      NATURAL JOIN (
        SELECT student_id, grade AS MAT111
        FROM result
        WHERE course_code = 'MAT111'
      ) AS tMAT111
      NATURAL JOIN (
        SELECT student_id, grade AS MAT112
        FROM result
        WHERE course_code = 'MAT112'
      ) AS tMAT112
      -- etc.
    WHERE level = @level AND semester = @semester
    

    If you choose to go down this path, you can make your life slightly easier by generating this query automatically, using either a looping construct in PHP or a prepared statement in MySQL.

    Here is one way that you could do that in PHP:

    1. Obtain a list of courses:

      $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
      $qry = $dbh->query("SELECT DISTINCT course_code FROM result [WHERE ...]");
      $courses = $qry->fetchAll(PDO::FETCH_COLUMN, 0);
      
    2. Loop over the results, constructing the above SQL:

      mb_regex_encoding($charset);
      
      $columns = mb_ereg_replace('`', '``', $courses);
      $sql = "
      SELECT student_id AS Matriculation, `".implode("`,`", $columns)."`, gp AS GP
        FROM gp";
      
      foreach ($columns as $column) $sql .= "
        NATURAL JOIN (
          SELECT student_id, grade AS `$column`
          FROM result
          WHERE course_code = ?
        ) AS `t$column`";
      
      $sql .= "
      WHERE level = ? AND semester = ?";
      
    3. Execute the SQL, passing in the array of courses as parameters:

      $qry = $dbh->prepare($sql);
      
      $params = $courses;
      array_push($params, $level, $semester);
      $qry->execute($params);
      
    4. Output the results:

      echo "<table>";
      
      echo "<tr>";
      for ($i = 0; $i < $qry->columnCount(); $i++) {
        $meta = $qry->getcolumnMeta($i);
        echo "<th scope='col'>" . htmlentities($meta['name']) . "</th>";
      }
      echo "</tr>";
      
      while ($row = $qry->fetch(PDO::FETCH_NUM)) {
        echo "<tr>";
        foreach ($row as $field) echo "<td>" . htmlentities($field) . "</td>"
        echo "</tr>";
      }
      
      echo "</table>";
      
  2. Do the above as a one-off operation so that the structure of your MySQL database is changed to more closely reflect this desired layout (easy once table is converted, but may impact other uses of the database):

    CREATE TABLE StudentGrades (PRIMARY KEY('Matriculation'))
    SELECT student_id AS Matriculation, MAT111, MAT112, gp AS GP
      -- etc. as above
    

    Alternatively, you can create a VIEW which is a sort of "virtual table" structured in this way based on the underlying table.

  3. Pivot the data manually in PHP (relatively tedious).

Похожие вопросы