php里怎么把行变成列,php-如何将mysql表行显示为列

我有两个表,结果和GP.

在结果表中,我有类似以下内容：

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

课程代码不是恒定的-取决于学生注册的课程

我已经做到了：

$rst1 = mysql_query("select distinct course_code from result ", $conn);

echo "

echo "

";

echo "

Matriculation Number ";

$c_code = array();

while ($row = mysql_fetch_array($rst1))

{

$c_code[] = $row['course_code'];

}

foreach($c_code as $c_code)

{

echo "

" .$c_code. "";

}

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

GP";

$rst = mysql_query("$sql",$conn) or die(mysql_error());

while ($row = mysql_fetch_array($rst))

{

echo "

";

echo "

";

echo "

" .$row['student_id']. "";

echo "

" .$row[$c_code]. "";

}

echo "

" .$row[$c_code]. "";

echo "

" .$row['CTC']. "";

echo"

";

echo "

";

?>

第一个查询是获取课程代码,因为课程不是常数.

有了该代码,我得到了这样的东西：

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

任何建议或方向将不胜感激.

解决方法:

您希望执行的操作被称为“数据透视”,其他RDBMS对此具有本机支持,而MySQL则没有(通过设计,因为开发人员认为这种操作属于表示层).

但是,您有几种选择：

>构建一个非常可怕的MySQL查询,以手动执行数据透视操作：

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

如果您选择沿着这条路走,则可以使用PHP中的循环结构或MySQL中的预处理语句,自动生成此查询,从而使您的生活稍微容易一些.

这是您可以在PHP中执行此操作的一种方法：

>获取课程列表：

$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);

>遍历结果,构造上面的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 = ?";

>执行SQL,将课程数组作为参数传递：

$qry = $dbh->prepare($sql);

$params = $courses;

array_push($params, $level, $semester);

$qry->execute($params);

>输出结果：

echo "

echo "

";

for ($i = 0; $i < $qry->columnCount(); $i++) {

$meta = $qry->getcolumnMeta($i);

echo "

" . htmlentities($meta['name']) . "";

}

echo "

";

while ($row = $qry->fetch(PDO::FETCH_NUM)) {

echo "

";

foreach ($row as $field) echo "

" . htmlentities($field) . ""

echo "

";

}

echo "

";

>一次性执行上述操作,以便更改MySQL数据库的结构以更紧密地反映此所需的布局(转换表后很容易,但可能会影响数据库的其他用途)：

CREATE TABLE StudentGrades (PRIMARY KEY('Matriculation'))

SELECT student_id AS Matriculation, MAT111, MAT112, gp AS GP

-- etc. as above

或者,您可以创建VIEW,这是一种基于基础表以这种方式构造的“虚拟表”.

>在PHP中手动旋转数据(比较乏味).

标签：php,mysql

来源： https://codeday.me/bug/20191011/1892957.html