HTML table with sql data in php (table row depend by sql columns)

Question

Hello i got this sample data in sql

$data = array(
  array('id' => '1','name' => 'name1','surname' => 'surname1'),
  array('id' => '2','name' => 'name2','surname' => 'surname2'),
  array('id' => '3','name' => 'name3','surname' => 'surname3'),
  array('id' => '4','name' => 'name4','surname' => 'surname4')
);

I want to dispplay in in html table but my code didnt work :

<?php
mysql_connect("localhost", "root", "");
mysql_select_db("test");
$select_data = "SELECT * FROM dane ORDER BY `id` DESC";
?>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html">
        <meta charset="utf-8">
        <title></title>
    </head>
    <body>
        <table style="width: 100%;" border="1" cellspacing="5" cellpadding="5">
            <thead>
                <tr>
                    <th align="left" valign="middle">id</th>
                    <th align="center" valign="middle">name</th>
                    <th align="center" valign="middle">surname</th>                    
                </tr>
            </thead>
            <?php
            $result = mysql_query($select_data);
            while ($data = mysql_fetch_row($result)) { 
            }
            ?>
            <tbody>
                <tr>
                    <td align="center" valign="middle"><?php echo $data['id']; ?></td>
                    <td align="center" valign="middle"><?php echo $data['name']; ?></td>
                    <td align="left" valign="middle"><?php echo $data['surname']; ?></td>
                </tr>    
            </tbody>
        </table>
    </body>
</html>

But i wann't also that the numer of rows in html table depends by number of columns in sql table. For example in this case i want to display only three rows (three columns in sql table). When i add the column's to sql table i want to rows in html output table increses dynamicly.

Could someone help me with this code ?

gbestard · Accepted Answer · 2014-05-13T08:22:11.077

Change your code to this:

<tbody>
    <?php
    $result = mysql_query($select_data);
    while ($data = mysql_fetch_row($result)) {
        ?>
        <tr>
            <td align="center" valign="middle"><?php echo $data['id']; ?></td>
            <td align="center" valign="middle"><?php echo $data['name']; ?></td>
            <td align="left" valign="middle"><?php echo $data['surname']; ?></td>
        </tr>
        <?php
    }
    ?>
</tbody>

You are closing your while loop before displaying the results

score 1 · Answer 2 · answered May 13 '14 at 07:59

You close your while-loop not correct:

<?php
mysql_connect("localhost", "root", "");
mysql_select_db("test");
$select_data = "SELECT * FROM dane ORDER BY `id` DESC";
$result = mysql_query($select_data);
?>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html">
        <meta charset="utf-8">
        <title></title>
    </head>
    <body>
        <table style="width: 100%;" border="1" cellspacing="5" cellpadding="5">
            <thead>
                <tr>
                    <th align="left" valign="middle">id</th>
                    <th align="center" valign="middle">name</th>
                    <th align="center" valign="middle">surname</th>                    
                </tr>
            </thead>
            <tbody>
            <?php while ($data = mysql_fetch_row($result)):?>
                <tr>
                    <td align="center" valign="middle"><?php echo $data['id']; ?></td>
                    <td align="center" valign="middle"><?php echo $data['name']; ?></td>
                    <td align="left" valign="middle"><?php echo $data['surname']; ?></td>
                </tr>
            <?php endwhile;?>
            </tbody>
        </table>
    </body>
</html>

Using the the "while(cond):" and "endwhile;" command you can see better where something starts and where it ends than using the encapsulation with braces.

Please consider to switch your Database Wrapper from mysql_ to PDO or mysqli, since mysql is not anymore actively supported.

You could also use instead:

<?php echo $data['id']?>

rather the shortform:

<?=$data['id']?>

Which is also avaiable w/o php short open after 5.3 (I think it was 5.3)

Thank you i wil rok it out, but i dot want to use pdo. How to display the number of rows the same as sql columns value ? — user1597594, May 13 '14 at 08:09
You maybe are looking for "http://www.php.net/manual/en/function.mysql-num-rows.php". You simply use then "$count=mysql_num_rows($result);". — D. Schalla, May 13 '14 at 08:15
D. Schalla - i have three columns in sql table, and i want the code to display dynamic the same amount of table rows as columns in SQL (i want to display only three first rows in this case). — user1597594, May 13 '14 at 08:35
Either combine your query or build one prior and use this help: http://stackoverflow.com/questions/10492164/how-do-i-count-columns-of-a-table Also if a answer helped you or is your solution, please upvote/accept it. — D. Schalla, May 13 '14 at 08:37

monyiliev · Answer 3 · 2014-05-13T21:05:56.247

If I understand your question correctly, you would like to have the number of returned rows match the number of columns in your table dane. The following code should do just that and I'm using mysqli which I strongly recommend. The mysql_query extension is deprecated as of PHP 5.5.0.

<?php
$db = new mysqli('localhost', 'root', '', 'test'); // server, user, pass, database
$table_name = 'dane'; // table

// Let's make sure we could establish a connection
if($db->connect_errno > 0){
    die('Unable to connect to the database ' . $db->connect_error);
}

// Build our select to return column names only
$select_cols = "SELECT column_name FROM information_schema.columns WHERE table_name='$table_name'";


if(!$result = $db->query($select_cols)){
    die('There was an error running the query.');
}

while($row = $result->fetch_assoc()){
    $cols[] = $row['column_name']; // Store the columns to an array. It will be further used.
}

// Implode the column names to a comma delimited string to use in the next select. It's also a good practice not to use asterisk in your select statements
$table_headers = implode(',', $cols);

// Query for records with a limit to number columns in the $table_name
$select_data = "SELECT $table_headers FROM $table_name ORDER BY `id` DESC LIMIT 0 , $result->num_rows"; 

if(!$result = $db->query($select_data)){
    die('There was an error running the query ' . $db->error);
}

while($row = $result->fetch_assoc()){
    $data[] = $row; // Store the data into an array to be used in the html table
}
$db->close(); // Close our connection
?>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html">
        <meta charset="utf-8">
        <title></title>
    </head>
    <body>
        <table style="width: 100%;" border="1" cellspacing="5" cellpadding="5">
            <thead>
                <tr>
                    <?php foreach ($cols as $k) : // Loop through columns ?>
                    <th align="center" valign="middle"><?php echo $k; ?></th>
                    <?php endforeach; ?>                  
                </tr>
            </thead>
            <tbody>

                    <?php foreach ($data as $k) : // Loop through each $data array() ?>
                    <tr>
                        <?php foreach ($k as $v) : // Let's display the records ?>
                        <td align="center" valign="middle"><?php echo $v; ?></td>
                        <?php endforeach; ?>
                    </tr>
                    <?php endforeach; ?>

            </tbody>
        </table>
    </body>
</html>

I also took the liberty to dynamically display the column names as table headers which should eliminate the need to manually add them later when your columns increase. If you would like to manually create them simply replace the top php portion with this one:

<?php
$db = new mysqli('localhost', 'root', '', 'test'); // server, user, pass, database
$table_name = 'dane'; // table

// Let's make sure we could establish a connection
if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

// Build our select to return column names only
$select_cols = "SELECT column_name FROM information_schema.columns WHERE table_name='$table_name'";


if(!$num_cols = $db->query($select_cols)){
    die('There was an error running the query.');
}

$select_data = "SELECT * FROM $table_name ORDER BY `id` DESC LIMIT 0 , $num_cols->num_rows"; 

if(!$result = $db->query($select_data)){
    die('There was an error running the query [' . $db->error . ']');
}

while($row = $result->fetch_assoc()){
    $data[] = $row; // Store the data into array to be used in the html table
}
$db->close(); // Close our connection

// print_r('<pre>');
// print_r($data);
// print_r('</pre>');
?>

and adjust the html code between <thead></thead>. This entire sample was put together pretty quickly so it could definitely be improved and adjusted to whatever needs. Please inspect it for any typos as well.

HTML table with sql data in php (table row depend by sql columns)

3 Answers3