Supporting many web sites with one database

April 19, 2008

If you are restricted by the web hosting company about how many databases you can create, there is a trick you can use to get the maximum out of that ‘single’ database. The answer is to use a prefix with tables. The secret is to use the key every time you refer the tables.

Creating an include file

Apart from saving the prefix I use this file to save connection information as well. I also create the database connection here. So everytime I need database access to a file, I just include the file with:

<?php
require_once("inc_dbaccess.php");
?>

What’s in it

Since I use mySql for most of my apps, I use ‘pconnect’ instead of ‘connect’. This single step improves database access when used online. Go here for more info on connection pooling. ‘$db’ helps in many places. I used it many with ‘mysql_affected_rows’ to check successful execution of an SQL statement. $client variable is used here incase you do not wish to use the key/value pair system to save configuration information. But that is completely optional.

<?php
$url="localhost"; //you may need to change this
$database="dbnamehere";
$dbprefix = "sls_"; //use something that reminds you of the project
$username="root";
$password="";
$client="client name here"; //this way you could use
the same app for many clients.
$db=mysql_pconnect($url,$username,$password); //pconnect honours
connection pooling
@mysql_select_db($database) or die( "Unable to select database");
?>

Usage

Here’s an example using the ‘dbprefix’ technique.

<?php
require_once("inc_dbaccess.php");
$query = "SELECT category FROM ".$dbprefix."config WHERE metaKey=$metaKey";
$result = mysql_query($query);
$tmp=mysql_affected_rows($db);
if($tmp) { echo “db command successful”; }
?>

Conclusion

As you notice this include file has many uses; such as:

Multiple applications using the same database
Being able to check the status of SQL execution
If you do not use key/value techniques for configuration management, this file can be used to save configuration information.