PHP Display Database Table

For administration panels or at times just data shown on the site, you will need to show a full database table. Now, you can manually enter the column’s rows names, but an easier way and more dynamic way is to just get the column names from MySQL or various other databases you use.

In this tutorial, we will be using the PHP class PDO. PDO is more secure than the original mysql_ functions, and if you haven’t yet, you should make the change right away. I should mention, that using this code could cause some security issues with your site. If you display personal information, account information, password hashes, session keys, or any other information similar, you will most likely have accounts hacked. Also, showing column names can help hackers run SQL injections because they will know the column names you are using. You can always use the MySQL alias for table names, so they return as a different name from the database column name.

We will start by setting up a basic query and outputting this data into a table. This also sets up the PDO variables.

try {
	$dbh = new PDO("mysql:dbname=DATABASENAME;host=127.0.0.1","username","password");
}catch(PDOException $e) {
	die('Error');
}

$query = "SELECT * FROM `table` ORDER BY `id` ASC";
$sth = $dbh->prepare($query);
if(!$sth->execute()) {
	die('Error');
}

while($row = $sth->fetch(PDO::FETCH_ASSOC)) {
	echo "<tr>";
	foreach($row as $value) {
		echo "<td>{$value}</td>";
	}
	echo "</tr>";
}

The above code will connect to your database and run a basic query. PDO then loops through all of the result, and of those results we loop through each array. We use a foreach loop instead of a normal numeric loop since we don’t know how many columns or the column names.

To get get the column names, we need the keys of each value for the first row only, since all the rows will have the same column names. Similar to above, we loop through the rows, but this time we grab the key and the value. This code will be placed right below the while() line, and before the echo of the opening table row tag.

if($tableheader == false) {
	echo '<tr>';
	foreach($row as $key=>$value) {
		echo "<th>{$key}</th>";
	}
	echo '</tr>';
	$tableheader = true;
}

After this, we set the variable tableheader to false on the top of the file, and open and close the table tags before and after the while loop, giving us the following finished code.

<?php
	try {
		$dbh = new PDO("mysql:dbname=DATABASENAME;host=127.0.0.1","username","password");
	}catch(PDOException $e) {
		die('Error');
	}

	$tableheader = false;
	$query = "SELECT * FROM `table` ORDER BY `id` ASC";
	$sth = $dbh->prepare($query);

	if(!$sth->execute()) {
		die('Error');
	}

	echo "<table>";

	while($row = $sth->fetch(PDO::FETCH_ASSOC)) {
		if($tableheader == false) {
			echo '<tr>';
			foreach($row as $key=>$value) {
				echo "<th>{$key}</th>";
			}
			echo '</tr>';
			$tableheader = true;
		}
		echo "<tr>";
		foreach($row as $value) {
			echo "<td>{$value}</td>";
		}
		echo "</tr>";
	}
	echo "</table>";

Now, here is a little bit of the code breakdown. The first 5 lines creates a new PDO database connection. PDO is a database interface to make inserting and grabbing data a lot easier and safer. It escapes illegal characters when you bind them properly. Of course, if the database doesn’t connect, it hits the catch exception and dies.

After that, we setup some variables such as the tableheader and query variables. Tableheader tells us if we generated the header row yet. The query variable is our SQL query we want to execute. You can insert variables and other clauses in this, but for the point of this tutorial it is kept as simple as possible, by grabbing all the rows from the database.

After setting up the variable sth to be the PDOStatement variable, we execute the query without any data or bindings. If the query fails, we run die again so nothing else further runs. In a real site, you would want to show a user safe message.

We now loop through each row returned. We set the variable row to the current returned associative array row. You can fetch numeric arrays or both, but anything other than the PDO::FETCH_ASSOC will return unwanted results for this table.

In the loop, we check to see if we have generated the header yet. If we haven’t, we run a foreach setting both the key and value variable. We are only interested in the key variable, which is the column name from the SQL database. Then we run through our normal loop, setting the value variable in the foreach loop, and echo out each of the data rows.