Create a table from MySQL using PHP PDO

Question

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>

hjpotter92 · Accepted Answer · 2013-04-05 05:54:07Z

The fetch() call only calls a single row from result set.
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.
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, CommentedApr 5, 2013 at 6:45
@BenJones You've used apostrophe character in that question. Use backticks in the SELECT statement. — hjpotter92, CommentedApr 5, 2013 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, CommentedApr 5, 2013 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, CommentedOct 8, 2015 at 13:22

spb13 · Accepted Answer · 2015-10-08 10:56:02Z

 $result = $dbconn->query('SELECT * from accounts'); $colcount = $result->columnCount(); // Get coluumn headers 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, CommentedOct 8, 2015 at 10:58

Stack Exchange Network

Create a table from MySQL using PHP PDO

2 Answers 2

Hot Network Questions

Create a table from MySQL using PHP PDO

2 Answers 2

Related

Hot Network Questions