CODEWITHSUNDEEP
phpsqlhtmlcodeigniter
Lucky13
Lucky13

Reputation: 11473

Display in table

I"ve a table like this 

global_id | auto_trans | add_drivers |child_seat | booster_seat | day   
- - - - - - - -  - - - - - - - -- -- -  - - - - - - --  - - - - - - - -
   18           2          1             5              2          2
- - - - - -  - - - - - - - - - - - - - - - - - - - - -  - - - - - - -  -
   19           5          8             7              6          1 
- - - - - - - -  - - - - - -  - - -  -  - - - - -  - - - - - - - - -  -
   20           2          4             7              9          3
 - - - - - - - - -  -- -  - - - - - -  - - - - - - - -  - - - - - - -  -

I want to display the values in a table in the below format

 ___________|__1_|_2_ |_3 |
  |         |    |    |   |
auto_trans  | 5  | 2  | 2 |
- - - - -  - - - - -  - - -
add_drivers | 8  | 1  | 4 |
 -- -  - - -  - - - - - --
child_seat  | 7  | 5  | 7 |
 - - -  - - - - - - - - - -
booster_seat| 6  | 2  | 9 |
 - - - - - - - - - - - - - -

How to write loop for this?

Upvotes: 0

Views: 178

Answers (2)

David Mårtensson
David Mårtensson

Reputation: 7600

The answer is pivot or crosstab but the actual solution depends on which database you are using.

MS SqlServer 2005 has a PIVOT command to do this but many others do not.

As you are using Php I assume its MySQL och ProstgreSQL

For PostgreSQL check this out: http://www.postgresql.org/docs/current/static/tablefunc.html

For MySQL it seems to be more difficult , check this: http://mysql.bigresource.com/crosstab-query-Pivot-table-1mjeYKlS.html#WAGYCZsH

Upvotes: 0

Berry Langerak
Berry Langerak

Reputation: 18859

The easy answer? By creating a loop in a loop that'll store the value of a columnname by columnname, instead of what row it is in in the database (see below for an example if you're in a pickle and need this solved ASAP). The correct answer, however, is that you probably need to normalise your database a little further. Although the values seem to be related, your request to print them in this way tells me your database might be a little off. 

<?php
/** Just assuming. */
$results = $db->query( 
    'SELECT auto_trans, add_drivers, child_seat, booster_seat ORDER BY auto_trans' 
)->fetchAll( );

$categorised = array( );
foreach( $results as $result ) {
    foreach( $result as $columname => $value ) {
        if( !array_key_exists( $columnname, $categorised ) ) {
            $categories[$columnname] = array( );
        }
        $categories[$columnname][] = $value;
    }
}

echo "<table>";
echo "<tr>";
foreach( $categories as $category => $values ) {
    echo "<tr>";
    echo "<th>" . $category . "</th>";
    foreach( $values as $value ) {
        echo "<td>" . $value . "</td>";
    }
    echo "</tr>";
}
echo "</table>";

Upvotes: 1

Related Questions