Mysql display rows as columns

Question

I want to display rows as columns.

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

Code

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

It looks like the query only returns the first row. What does the Stdid and $id stand for? Please add this data to your example table as well. — JonathanStevens, Aug 30 '15 at 16:49
What you're wanting to do would is call transposing in linear algebra. If you Google [transpose mysql] you'll find numerous posts on here that solve it in different ways. — Shawn Conn, Aug 30 '15 at 19:04

score 2 · Accepted Answer · answered Aug 30 '15 at 20:55

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.

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>

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>

fdfdfd · Answer 2 · 2015-08-30T20:48:43.777

0

Try this.

<table width = "1093" align="center" bgcolor="pink">
<tr align="center">
<td colspan="6"><h2>View Term</h2></td>
</tr>

<tr align="center" bgcolor="">
<th>S.N</th>
<th>Subject</th>

<th>Year 1</th>
<th>Year 2</th>
<th>Year 3</th>

</tr>


<?php 


$db = mysqli_connect("localhost","root","YOURPASSWORD","YOURDATABASE");
if (mysqli_connect_errno())

{
    echo"The Connection was not established" . mysqli_connect_error();
    exit();
}


$get_term = "SELECT * FROM term WHERE Stdid='$id'";

$run_term = mysqli_query($db,$get_term);
$i =0 ;


while ($row=mysqli_fetch_array($run_term ))

{
// Get  data from database.
// Change the input in $row[''] if the name of column is different in your tble "term".
    $subject=$row['subject'];

    $year1=$row['year1'];
    $year2=$row['year2'];
    $year3=$row['year3'];

    $i++;   



?>

<tr align="center">

<td> <?php echo $i;?></td>
<td><?php echo $subject;?></td>
<td><?php echo $year1;?></td>
<td><?php echo $year2;?></td>

<td><?php echo $year3;?></td>

</tr>
<?php }?>

</table>

edited Aug 30 '15 at 20:48

answered Aug 30 '15 at 19:02

fdfdfd

501
1
7
21

Thank you so much But what I really need is to display rows as columns This code display as columns but what i need is like this html |90 |80 \100 Css |80 |10 |70 – Abdifatah Abdilahi Aug 30 '15 at 19:34
Hmm.. doesnt it work as you posted above? – fdfdfd Aug 30 '15 at 20:22
Works fine for me. http://imgur.com/4ti5k8n – fdfdfd Aug 30 '15 at 20:28
This is mysql table Pic http://postimg.org/image/a3j5z3gih/ How I need to display see pic http://postimg.org/image/tzqvxr0zz/ I appriciate ur time – Abdifatah Abdilahi Aug 30 '15 at 20:31
Yea it works fine look at the link i posted. – fdfdfd Aug 30 '15 at 20:32
Ahh. looks like your database is abit weird.. you can try using [this](http://imgur.com/kugdIWm) It is much simpler. You should create a column for subject. Also, remember to set primary key and auto increment to stdid. – fdfdfd Aug 30 '15 at 20:37
Thank you but Stdid is the role number containing strings and integers I dont know by problem IM little bit confused – Abdifatah Abdilahi Aug 30 '15 at 20:43
Ahh i get what you are trying to say. Try creating this [table](http://imgur.com/56Jc5Sr) and use the code above. You should be fine. If this helped you please remember to upvote or click on the tick! – fdfdfd Aug 30 '15 at 20:47
This code just displays the data from the database as it is. The original question states that he wants to display columns as rows. Changing his table structure to match your code won't solve his issue, because he will still be stuck with his current data. – JonathanStevens Aug 30 '15 at 21:06

Mysql display rows as columns

2 Answers2

Linked