将MYSQL查询中的行显示为HTML表中的列 [英] Display rows from MYSQL query as columns in HTML table

问题描述

我有一个MYSQL数据库存储每个学期工作的学生的水平。

I have a MYSQL database storing levels for students for each terms work.

我很好地创建php页面，查询等，非常适合hmtl / css需要创建表。我对数组有一个基本的了解。

I'm fine creating the php page, with query etc and very comfortable with the hmtl/css required to create tables. I have a basic understanding of arrays.

我在挣扎的地方是如何获取每个学生的数据并将其显示在html表格的列中。

Where I am struggling is how to take each students data and display it in columns in the html table.

以前我在数据库中的评估表有多个列 - aut7，spr7，sum7等，因此很容易在html表格中显示。然而，我一直在努力使数据库更有效，所以我现在将每个评估作为一个单独的记录存储 - 数据库的设置要好得多，但却出现了以下问题（我的知识缺乏）！

Previously my assessment table in the database had multiple colums - aut7, spr7, sum7 etc so it was easy to display in an html table. However I have been trying to make the database more effective so am now storing each assessment as a separate record - database is much better set-up but has thrown up the following problem (well lack of knowledge on my part!!)

我的意思是一个简单的例子，如下所示：

A simple example of what I mean is as follows:

这是查询的结果：

student_id   term    level
  1          aut7      3b
  1          spr7      3a
  1          sum7      4c
  2          aut7      3a
  2          spr7      4c
  2          sum7      4b

我希望显示在我的html / css表中，如下所示：

I would like it in displayed in my html/css table as follows:

 Student      Aut7      Spr7     Sum7
   1           3b        3a       4c
   2           3a        4c       4a

如果有人可以帮助一个相对较新的PHP / MySQL，我将非常感激。 / p>

If anyone could help a relative newcomer to PHP/MySQL I would be very grateful.

推荐答案

You ne编辑以支持你的结果。不幸的是，MySQL确实有 PIVOT ，但是你可以像这样构建一个查询来获得相同的结果

You need to pivot your results. Unfortunately MySQL does have PIVOT but you can build a query like this to achieve the same results

SELECT student_id, 
  MAX(CASE WHEN term = 'aut7' THEN level END) Aut7,
  MAX(CASE WHEN term = 'spr7' THEN level END) Spr7,
  MAX(CASE WHEN term = 'sum7' THEN level END) Sum7
FROM student_terms
GROUP BY student_id

请参阅 SQL小提琴中的演示。

这篇关于将MYSQL查询中的行显示为HTML表中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！