Fetching columns from DB and creating an HTML table with the retrieved data in PHP

I have a table in database namely 'reg' with columns date an开发者_JAVA技巧d name. I want to select the name column and month_name and year from date column and create a corresponding html table with columns (serial number, name, month, year). How do I do this in PHP?

the solution that use PDO is a good one, i personnaly prefer using it. However if you need to use the mysql functions of PHP, here is an alternative that use mysql_field_name().

in the following exemple, we build the header of the table by applying a for-each loop to the selected fields of the query:

  $db_host = "localhost";
  $db_user = "myuser";
  $db_pass = "mypassword";
  $db_name = "mydatabase";

$connexion = mysql_connect($db_host, $db_user, $db_pass) or die (mysql_error());
$db = mysql_select_db($db_name, $connexion) or die(mysql_error());

$result = mysql_query("SELECT * FROM mytable");

?>
<table>
    <thead>
<?php
for ($i=0; $i < mysql_num_fields($result); $i++) {
    echo '<th>'.mysql_field_name($result, $i).'</th>';
}
?>
    </thead>
    <tbody>
<?php

.....

then you just have to process the query and populate the table

Look into the chapter for PDO in the PHP Manual. Basically, you want to use something like this:

$sth = $dbh->prepare("SELECT col1, col2, col3 FROM tablename");
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_ASSOC);

Write the $result to an HTML template then. If you fetched an associate array, this is as simple as:

<table>
    <thead>
        <tr>
            <th><?php echo implode('</th><th>', array_keys($result[0])) ?></th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($result as $row):
        <tr>
            <td><?php echo implode('</td><td>', $row) ?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
 </table>

This will output all keys of the array as table head columns and then loop over the array and create one row per entry with the values of the array for table cells. If you want more control, access the array through it's keys instead of imploding it.

You will want to check if the $result array contains something before writing it to the template though.

Note that the above is not copy & paste ready. You'd have to figure some stuff yourself. If you don't know any function names I used, look them up. What you ask is basic stuff, so you should also be able to find plenty tutorials on Google.