PHP MYSQL Associate array and table

Question

Here is a code. This loads all the header part (i.e the header for the table) dynamically from the database.

The below code works fine. But the column is mismatched. i.e. the first row first column of the header is blank and there is a dislocation in the table.

Code

    <table border="1">
    <?php
    $book_query = mysql_query("select * from book_master");
    $i = 0;
    while($row = mysql_fetch_assoc($book_query))
    {

            $columns = array_keys($row);
            ?> 
            <th>
                <?php
                foreach($columns as $column)
                {
                ?>
                    <td><?php echo $column; ?> </td>

            </th>
            <?php 
        } 
        ?>
        <tr>
            <?php
            foreach($row as $key=>$value)
            {
                ?>
                <td><?php echo $value; ?></td>
                <?php
            }
            ?>
        </tr>
        <?php
        $i++;
    }
    ?>          
    </table>

EDIT:

Here is my print_r($columns) value:

Array ( [0] => Author Name [1] => Book Name [2] => Rating [3] => Location )

I know the problem is with the loop. Could someone help me out?

This is terrible code – you should separate data handling and output. And you are generating invalid HTML here – you are putting `td` elements into `th` elements; _both_ belong into a `tr` instead and are _not_ to be nested into each other. — CBroe, Apr 01 '14 at 12:55
CBroe - Now its working fine. I have changed the th to tr. now its okay.. yes, as you said - terrible code. ;) — user3350885, Apr 01 '14 at 12:58
The mysql extension is deprecated. New code should use mysqli or PDO, both of which have important advantages, such as support for prepared statements. Also, don't use [`SELECT *`](http://stackoverflow.com/q/321299/) unless you're writing a DB administration program; select only the columns you need. — outis, Apr 01 '14 at 14:03

score 2 · Answer 1 · answered Apr 01 '14 at 13:42

You can try to change

         <th>
            <?php
            foreach($columns as $column)
            { ?>
                <td><?php echo $column; ?> </td>
            <?php 
            }   
            ?>
        </th>

to

        <tr>
            <?php
            foreach($columns as $column)
            { ?>
                <th><?php echo $column; ?> </th>
            <?php 
            }   
            ?>
        </tr>

user3350885 · Answer 2 · 2014-04-02T04:01:01.453

Hope this will help someone. Just I have replaced the TH tag with TR and the output is perfect.

    <table border="1">
    <?php
    $book_query = mysql_query("select * from book_master");

    while($row = mysql_fetch_assoc($book_query))
    {

    $columns = array_keys($row);
    ?> 
    <tr>
    <?php
    foreach($columns as $column){ ?>
    <td><?php echo $column; ?> </td>
    <?php } ?>
    </tr>

    <tr>
    <?php
    foreach($row as $key=>$value){ ?>
    <td><?php echo $value; ?></td>
    <?php } ?>
    </tr>


    </table>

score 1 · Answer 3 · answered Apr 01 '14 at 13:25

Just remove TH tag because its create one extra cell, so your layout messed up

<table border="1">
    <?php

    $book_query = mysql_query("select * from book_master");
    $i = 0;
    while($row = mysql_fetch_assoc($book_query))
    {
    if($i == 0){
    $columns = array_keys($row);
    ?> 
    <?php
    foreach($columns as $column){ ?>
    <td><?php echo $column; ?> </td>
    <?php } ?>
    <?php } ?>
    <tr>
    <?php
    foreach($row as $key=>$value){ ?>
    <td><?php echo $value; ?></td>
    <?php } ?>
    </tr>
    <?php
    $i++;
    }
    ?>          
    </table>

PHP MYSQL Associate array and table

Code

3 Answers3

Linked