Caching a MySQL HTML table with PHP

MySQL and PHP, the perfect match right?! Well close enough, anyway displaying a table with data from a MySQL database is a common task for a web developer. It’s easily done however bigger tables combined with more columns and constant calling on this data can slow your webpage down.

The solution is to have a cron job build the HTML table (and page) for you, so that the only loading your users do is a simple HTML page. Through the use of ob_start we capture what is echo’d and store it as a HTML file (page).

Here is the script, using the basics of MySQLi and HTML tables this PHP script will save what we echo as table.html

<?php
$cachefile = 'table.html';
$connect = mysqli_connect("127.0.0.1", "USERNAME", "PASSWORD", "DATABASE");
ob_start();
echo "
<!DOCTYPE html>
<html lang='en'>
<head>
    <title>HTML title</title>
    <meta name='viewport' content='width=device-width, initial-scale=1'>
    <meta http-equiv='content-type' content='text/html;charset=UTF-8'>
    <meta name='description'
          content='HTML description'>
    <link rel='icon' href='yourico.ico'>
    <link rel='stylesheet' href='https://stackpath.bootstrapcdn.com/bootswatch/4.1.1/darkly/bootstrap.min.css'>
    <link rel='stylesheet' href='custom.css'>
</head>
<body>
<div class='container'>";
$sql = "SELECT `name`, `score`, `money`, `deaths`, `kills` FROM `table`";
$result = mysqli_query($connect, $sql);
echo "<div class='table-responsive'>
  <table class='table table-hover table-bordered'><thead>
<tr>
<th>Name</th>
<th>Score</th>
<th>Money</th>
<th>Deaths</th>
<th>Kills</th>
</thead>
</tr>";

while ($row = mysqli_fetch_array($result)) {
    echo "<tr>";
    echo "<td>" . $row['name'] . "</td>";
    echo "<td>" . $row['score'] . "</td>";
    echo "<td>" . $row['money'] . "</td>";
    echo "<td>" . $row['deaths'] . "</td>";
    echo "<td>" . $row['kills'] . "</td>";
    echo "</tr>";
}
echo "</table></div></div></body></html>";
$fp = fopen($cachefile, 'wa+');
fwrite($fp, ob_get_contents());
fclose($fp);
ob_end_flush();

Incredibly simple and so very helpful in getting page loading times down. Whilst small databases or minimal column fetches might be quick if you need to call a lot of data into a table using a caching method like the above will save on server stress and page load times. Just ensure to have the cron constant if your data is updated regularly.