MySQL将行显示为列 [英] Mysql display rows as columns

问题描述

我想将行显示为列.

这是名为Term的mysql表

This is mysql table called Term

|Name  ||Year      ||HTML ||CSS ||Js  ||
|Year1 ||2013-08-30||90   ||70  ||70  ||
|Year2 ||2014-08-30||100  ||65  ||80  ||
|Year3 ||2015-08-30||80   ||95  ||90  ||

我想要的是显示为这样的列

What I want is to display as columns like this

 |Subject||Year1 ||Year2 ||Year3 ||
 |HTML   ||90|   ||100    ||80   ||
 |CSS    ||70|   ||65     ||95   ||
 |JS     ||70|   ||80     ||90   ||

代码

<tr>
    <th>Code</th><th>Subject</th><th>year1</th>
    <th>year2</th><th>year3</th>
    </tr>
        <?php 

        $query = mysql_query("SELECT * FROM term WHERE Stdid='$id'");
        while ($row = mysql_fetch_assoc($query)) {
    foreach($row as $key => $value) {

?>
    <tr>
    <th><?php echo $key ?></th> 
    <th><?php echo $value?></th>

    <th>99</th><th>00</th>
    </tr>
<?php  } }  } ?>

</table>

我的结果仅适用于第一年

My result only works for the first year

 |Subject||Year1 ||Year2 ||Year3||
 |HTML   ||90|   ||?     ||?    ||
 |CSS    ||70|   ||?     ||?    ||
 |JS     ||70|   ||?     ||?    ||

推荐答案

首先需要将所有数据转置到临时数组中，然后才能再次输出它们.我会给你2种方法.

You first need to transpose all data in a temporary array before you can output it again. I'll give you 2 methods to do this.

方法1:只需获取每一行并按列索引切换行索引:

Method 1: just fetch every row and switch the row index by the column index:

<table>
    <tr>
        <th>Subject</th>
        <th>year1</th>
        <th>year2</th>
        <th>year3</th>
    </tr>

    <?php
    $mysqli = new mysqli('localhost', 'user', 'pass', 'database');
    $id = 1;
    $report = array();
    $columnIndex = 0;
    $query = $mysqli->query("SELECT HTML, CSS, Js FROM term WHERE Stdid='$id'");
    while ($results = $query->fetch_assoc()) {
        foreach ($results as $course => $score) {
            $report[$course][$columnIndex] = $score;
        }
        $columnIndex++;
    }

    foreach ($report as $course => $results) { ?>
        <tr>
            <th><?php echo $course; ?></th>
            <?php foreach ($results as $score) { ?>
                <th><?php echo $score; ?></th>
            <?php } ?>
        </tr>
    <?php } ?>
</table>

方法2:提取数组中的所有行，因此它成为数组的数组，并使用array_map和回调NULL进行转置(请参见

Method 2: Fetch all rows in an array, so it becomes an array of arrays and use array_map with callback NULL to transpose it (See example 4 at http://php.net/manual/en/function.array-map.php). You need to add the course names in the initial array to include them in the end result.

<table>
    <tr>
        <th>Subject</th>
        <th>year1</th>
        <th>year2</th>
        <th>year3</th>
    </tr>

    <?php
    $mysqli = new mysqli('localhost', 'user', 'pass', 'database');
    $id = 1;
    $data = array(array('HTML', 'CSS', 'Js'));
    $query = $mysqli->query("SELECT HTML, CSS, Js FROM term WHERE Stdid='$id'");
    while ($row = $query->fetch_assoc())
    {
        $data[] = $row;
    }
    $data = call_user_func_array('array_map', array_merge(array(NULL), $data));
    ?>

    <?php
    foreach ($data as $row): ?>
        <tr>
            <?php foreach ($row as $value): ?>
                <th><?php echo $value?></th>
            <?php endforeach; ?>
        </tr>
    <?php endforeach; ?>
</table>

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