MySQL Table to HTML Table

Some MySQL tables span pages or have text content that gets hidden in phpMyAdmin (or whatever software you use to access MySQL).

Generally, when I wish to have all the table data available to me within a scroll, I export the MySQL table into an HTML table. It actually happens quite a bit.

There are 3 reasons for me to see a MySQL table in an HTML table. You may have other reasons.

• When I import content into a MySQL database from a CSV file a client has provided, it sure is nice to quickly verify the MySQL table was updated as expected.

• During development, I may wish to have a tab in the browser to immediately see the contents of an entire MySQL table.

• There are a few frequently updated MySQL tables on our server that I like to stay on top of.

A PHP script to do the import from a MySQL table and into an HTML table accompanies this article. The script is customized with your MySQL access credentials.

The content of the wp_posts table for a new WordPress installation is used to illustrate what the imported HTML table looks like.

When the script is uploaded to your server and accessed by browser with its URL, the browser displays the entire table. Every time you reload, the entire table is loaded again (great for monitoring a frequently updated MySQL table).

Here is the MySQL wp_posts table of a new WordPress installation in an HTML table, domain name changed to example.com. The HTML table is in a scrollable iframe and can also be loaded into a new browser tab.

Here is the source code of the PHP script. Customization notes follow.

<?php
/*
MySQL table to HTML Table
Version 1.0
October 16, 2021
Will Bontrager Software LLC
https://www.willmaster.com/
*/

/* Customization */
/* Provide the MySQL credentials here. */
$MySQL_hostname = 'localhost';
$MySQL_username = 'abcdef_wp';
$MySQL_password = 'sfdERERdsseee';
$MySQL_database = 'abcdef_wpuser';
$MySQL_table    = 'wp_posts';
/* End of customization. */

class MySQLclass
{
   private $MySQLi;
   private $result;
   public $numRowsInResult;
   public $lastID;
   public $error;

   function __construct()
   {
      $this->numRowsInResult = 0;
      $this->lastID = 0;
      $this->error = array();
      $th = func_get_args();
      if( count($th) )
      {
         foreach( $th[0] as $k => $v ) { $this->$k = $v; }
      }
      $this->MySQLi = new mysqli( $this->hostname, $this->username, $this->password, $this->database );
      if( $this->MySQLi->connect_errno )
      {
         $this->error[] = 'Unable to connect to the database server (error number ' . $this->MySQLi->connect_errno . '):<br> ' . $this->MySQLi->connect_error;
      }
      return $this->error;
   } # function __construct()

   public function ErrorMessage() { return $this->error; } # public function ErrorMessage()

   public function SelectRecordData($arr)
   {
      $this->error = array();
      if( empty($arr['tablename']) )
      {
         $this->AppendErrorNotationToErrorMessage('Table name is required.');
         return false;
      }
      $sql = 'SELECT ';
      if( isset($arr['cols']) )
      {
         if( is_array($arr['cols']) ) { $sql .= implode(',',$arr['cols']) . ' '; }
         else { $sql .= implode(',',preg_split('/[, ]+/',$arr['cols'])) . ' '; }
      }
      else { $sql .= '* '; }
      $sql .= "FROM {$arr['tablename']} ";
      if( isset($arr['where']) ) { $sql .= "WHERE {$arr['where']} "; }
      if( isset($arr['order']) ) { $sql .= "ORDER BY {$arr['order']} "; }
      $limit = isset($arr['limit']) ? preg_replace('/[^\d\,]+/','',$arr['limit']) : 0;
      $offset = isset($arr['offset']) ? intval($arr['offset']) : 0;
      if( $offset and (! $limit ) ) { $limit = 9999999; }
      if( $limit ) { $sql .= "LIMIT $limit "; }
      if( $offset ) { $sql .= "OFFSET $offset "; }
      if( ($this->result = @mysqli_query($this->MySQLi,$sql)) )
      {
         $this->numRowsInResult = mysqli_num_rows($this->result);
         return true;
      }
      $this->AppendErrorNotationToErrorMessage('Selection failed.');
      return false;
   }

   public function GetRow()
   {
      if( ($row = mysqli_fetch_assoc($this->result)) ) { return $row; }
      return array();
   }

   private function AppendErrorNotationToErrorMessage($s)
   {
      return rtrim($s,":. \t\n\r\0\x0B") . ' (error number ' . $this->MySQLi->errno . '): ' . $this->MySQLi->error;
   }
} # class MySQLclass

$MySQL = new MySQLclass( array( 'hostname'=>$MySQL_hostname, 'username'=>$MySQL_username, 'password'=>$MySQL_password, 'database'=>$MySQL_database ) );
if( count($MySQL->error) ) { echo('<pre>'.print_r($MySQL->error,true).'</pre>'); exit; }
$success = $MySQL->SelectRecordData(array('cols'=>'*','tablename'=>$MySQL_table));
if( $success )
{
   echo <<<PAGETOP
<!doctype html>
<html>
<head>
<title>$MySQL_table Export</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<style>
body { font-size:100%; font-family:sans-serif; }
table { border-collapse:collapse; border:2px solid black; }
tr { vertical-align:bottom; font-size:85%; }
td { vertical-align:top; }
</style>
<table border="1" cellpadding="6" cellspacing="0">
PAGETOP;
   $topdone = false;
   while( ($row = $MySQL->GetRow()) )
   {
      if( !$topdone )
      {
         $topdone = true;
         echo "\n<tr>";
         foreach( $row as $k => $v ) { echo "\n<th>$k</th>"; }
         echo "\n</tr>";
      }
      echo "\n<tr>";
      foreach( $row as $k => $v ) { echo "\n<td>".htmlspecialchars($v).'</td>'; }
      echo "\n</tr>";
   }
   echo '</table></body></html>';
}
else { echo '<p>Unable to connect to table.</p>'; }
?>

Customization notes —

1. These lines are MySQL credentials. They will need to be updated.

   $MySQL_hostname = 'localhost';
$MySQL_username = 'abcdef_wp';
$MySQL_password = 'sfdERERdsseee';
$MySQL_database = 'abcdef_wpuser';

   Replace the red colored values with the MySQL credentials for the hostname, the username, the password, and the the database name for your database. (Information about the table name follows.)

   For WordPress, the information is in wp-config.php. For other software, generally either in a configuration or preferences file.

2. The table name that you specify for this PHP script is the name of the MySQL table with data you wish to view in an HTML table.

   $MySQL_table = 'wp_posts';

   Replace wp_posts with the table name.

Name the PHP script db2table.php or other *.php file name you prefer.

Upload db2table.php to your server and make a note of its URL.

Type its URL into your browser.

You should see the contents of the MySQL table you specified when you customized the db2table.php file.

This little PHP script is so very handy when you wish to see MySQL table content in an HTML table. Once set up, it takes only a tap on "reload" and the browser displays the lastest data.

This article first appeared with an issue of the Possibilities newsletter.

Will Bontrager