Customizable HTML using PHP & MySQL.

by Josep Subirana

It's common practice when developing large applications consisting of many PHP scripts to set apart in a file such as "globals.inc" and "common.inc" the 'global variables' (such as the title of the page or the background color) and functions to be shared by most of these scripts, and load them into each PHP code with require() at the beginning of the script.


 <?php
    //filename: common.inc

    $bgcolor = "#FFFFFF";
    $shop_title = "Japan Foods";
    //...many more general settings to be shared by many scripts
    // would follow here.
 ?>

It is also easy, to include those global variables into our scripts:


 <?php
    //filename: welcome.php

    require('common.inc');
 ?>
 <HTML>
 <HEAD><TITLE><? echo($shop_title); ?></TITLE></HEAD>
 <BODY BGCOLOR="<? echo($bgcolor); ?>">
 </BODY></HTML>

But what if you want to allow multiple users of that group of PHP scripts to customize the values contained in their own 'common.inc' file as often as they want and without having to set up a PHP script that reads into that file, allows to modify the values of the variables it contains, and finally regenerates the 'common.inc' file?

I came up with an easy solution while developing an e-commerce application where multiple 'shops' sharing a single group of PHP scripts have, each one, its own back-end MySQL database file. It's obvious that it's easier to modify those global variables in the 'common.inc' file if they are stored instead in the MySQL database file from each shop, creating a table with the following structure:


CREATE TABLE settings (
    vid INT PRIMARY KEY AUTO_INCREMENT,
    var VARCHAR(20),
    val VARCHAR(80)
)

Where the fields 'var' and 'val' will store the name and value, respectively, of each global variable.

    vid    var        val

    1    bgcolor        #FFFFFF
    2    shop_title    Japan Foods
    ...


While one may object that reading the 'settings' for each PHP script from a file is really faster, if those settings are to be modified from each shop administrator's remote client, the idea is not that bad. Moreover, the PHP code to read those settings at the beginning of each HTML page is really short:


<?php
    //filename: common.inc

    $mysql_host = "localhost";
    $mysql_user = "php";
    $mysql_password = "php";

    function GetSettings($db_name,$table){
        $query = "SELECT var,val FROM $table";
        if( !$result = mysql_db_query($db_name, $query) ) return 0;

        while( list($VAR, $VAL) = mysql_fetch_row($result) ){

            $GLOBALS["$VAR"] = $VAL;
         }

        mysql_free_result($result);
        return 1;
    }
?>

The trick here is this $GLOBALS[] associative array, a predefined PHP array that stores the names and values of each variable global to a script. That allows us to write the code that reads the 'settings' from the database, within a function.

Obviously, the PHP scripts which include this code through the 'require()' function have to connect to the MySQL server before calling the GetSettings() function:


<?php
    //filename: welcome.php

    require 'common.inc';

    if(!mysql_pconnect($mysql_host, $mysql_user, $mysql_password)){
        //print an error message here or redirect
        exit();
    }

    $db_name = "shop_".$shopid;

    //the id of the current visited shop was passed to this script as $shopid
    //as a HIDDEN in a FORM,in the QUERY STRING (see below) or as a cookie.
    //
    //    <A HREF="welcome.php?shopid=<? echo($shopid); ?>">  
    //    header("Location: welcome.php?shopid=$shopid");
    //
    // For example, the MySQL file name for one particular shop could be 'shop_11'
    // where '11' is the received $shopid.

    $successful = GetSettings($db_name,"settings");

    //the second parameter passed to the function is the name of the MySQL table
    //where the settings for this page are stored. That allows different scripts
    //to read from different 'settings' tables in the same database.

?>
<HTML>
<HEAD>
<TITLE><? echo($shop_title); ?></TITLE>
</HEAD>
<BODY BGCOLOR="<? echo($bgcolor); ?>">

</BODY>
</HTML>

Using the $GLOBALS[] array, the variables stored in the MySQL table are made available as global variables to the front-end scripts in a simple way.