# Create a table from MySQL using PHP PDO

I am trying to simply present a table in HTML that is stored in a MySQL database. I would like to use Object Oriented PHP to access and fetch the data for the table. I have spent some time learning the different elements and have tried to put together a generic template I can use to access the tables in the database.

Questions:

• Is there anything wrong with the code below?
• Is there a better way to do this?
• Are there any redundancies in the code? Is a more generally preferred/standard way of doing this? I've seen foreach and while being used...

<html>
<table>
<tr>
<th>field1</th>
<th>field2</th>
<th>field3</th>
<th>field4</th>
<th>field5</th>
</tr>
<?php
require_once 'db_config.php';
$dbh = new PDO($dsn, $dbuser,$dbpass);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$sth =$dbh->prepare("SELECT * FROM a_temp");
$sth->execute();$result = $sth->fetch(PDO::FETCH_ASSOC); ?> <?php foreach($result as $index =>$row) : ?>
<tr>
<td><?php echo $row[field1]; ?></td> <td><?php echo$row[field2]; ?></td>
<td><?php echo $row[field3]; ?></td> <td><?php echo$row[field4]; ?></td>
<td><?php echo $row[field5]; ?></td> </tr> <?php endforeach;?> </table> </body> </html>  ## 2 Answers 1. The fetch() call only calls a single row from result set. 2. Your foreach loop will, as a result, just go in the first result and do nothing. It may even produce an error/notice for undefined index in $row.
3. While using a SELECT statement in PHP, always include the specific columns that you want to fetch. This is among good practices.

$sth =$dbh->prepare("SELECT field1, field2, field3, field4, field5 FROM a_temp");
$sth->execute(); ?> <?php foreach($sth->fetch(PDO::FETCH_ASSOC) as $row) : ?> <tr> <td><?php echo$row['field1']; ?></td>
<td><?php echo $row['field2']; ?></td> <td><?php echo$row['field3']; ?></td>
<td><?php echo $row['field4']; ?></td> <td><?php echo$row['field5']; ?></td>
</tr>
<?php endforeach;?>
</table>


Or, if you want to use a while loop:

$sth =$dbh->prepare("SELECT field1, field2, field3, field4, field5 FROM a_temp");
$sth->execute(); ?> <?php while($row = $sth->fetch(PDO::FETCH_ASSOC) ) { ?> <tr> <td><?php echo$row['field1']; ?></td>
<td><?php echo $row['field2']; ?></td> <td><?php echo$row['field3']; ?></td>
<td><?php echo $row['field4']; ?></td> <td><?php echo$row['field5']; ?></td>
</tr>
<?php } ?>
</table>

• Thank you, that's really quite helpful as I am learning. I've posted a similar question (link below) in case you have any insight. Appreciate it! – Ben Jones Apr 5 '13 at 6:45
• codereview.stackexchange.com/q/24748/23810 – Ben Jones Apr 5 '13 at 6:53
• @BenJones You've used apostrophe character in that question. Use backticks in the SELECT statement. – hjpotter92 Apr 5 '13 at 11:15
• Pedantic moment: using backticks has no advantages, but has (minor) disadvantages. In particular, the only advantage that it has is that it allows you to use reserved keywords. You shouldn't be using reserved keywords anyway, so that's cancelled out. As for disadvantages though, it immediately kills any chance at SQL-interoptability. This is rarely going to be a problem since changing RDBMS is rarely a case of changing the driver anyway, but there's no reason to add an immediate barrier for 0 benefit. – Corbin Apr 5 '13 at 16:30
• Just wondering, shouldn't it be fetchAll in the foreach example? As in foreach($sth->fetchAll(PDO::FETCH_ASSOC) as$row):? – somethinghere Oct 8 '15 at 13:22
    $result =$dbconn->query('SELECT * from accounts');
$colcount =$result->columnCount();

echo ('<table><tr>');
for ($i = 0;$i < $colcount;$i++){
$meta =$result->getColumnMeta($i)["name"]; echo('<th>' .$meta . '</th>');
}
echo('</tr>');

// Get row data
while ($row =$result->fetch(PDO::FETCH_ASSOC)) {
echo('<tr>');
for ($i = 0;$i < $colcount;$i++){
$meta =$result->getColumnMeta($i)["name"]; echo('<td>' .$row[\$meta] . '</td>');
}
echo('</tr>');
}

echo ('</table>');


Note: There may be an issue with Boolean fields and this wont work on every PDO driver... Check out the php docs on getColumnMeta http://php.net/manual/en/pdostatement.getcolumnmeta.php

• Welcome to Code Review! You have presented an alternative solution, but haven't reviewed the code. Please explain your reasoning (how your solution works and how it improves upon the original) so that the author can learn from your thought process. – SuperBiasedMan Oct 8 '15 at 10:58