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

    
    
    
    
    
    
    


";
$sql = "SELECT `name`, `score`, `money`, `deaths`, `kills` FROM `table`";
$result = mysqli_query($connect, $sql);
echo "

  
";

while ($row = mysqli_fetch_array($result)) {
    echo "";
    echo "";
    echo "";
    echo "";
    echo "";
    echo "";
    echo "";
}
echo "


Name
Score
Money
Deaths
Kills
" . $row['name'] . "
" . $row['score'] . "
" . $row['money'] . "
" . $row['deaths'] . "
" . $row['kills'] . "
";
$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.