PHP Database class with Caching

Caching!

The class makes caching your datasets seamless. In your code, you simply specify the cache filename and TTL (Time to Live) properties per query. The class handles checking to see if the cache file exists. If so, is it within the TTL? The class returns a dataset either from the database or the cache file. If you set a TTL greater than 0, then when the class gets data from the database, it writes it to the cache file for later use.

Before you can use the class, you need to enter your database connection information into the connect() method within the class file. You can enter multiple database connections here, and multiple database types are supported. Notice that case 0 and 1 are type mysql. Case 2 is postgres. Case 3 is mssql. These are merely in the class to show you some options. If you only have a single Postgres database that you ever work with, then modify the zero case for your postgres connection. You can delete the other cases if you want. Zero is the default connection id that will be used if you do not pass in anything.

function connect() {
    $this->log .= "connect() called<br />";
    switch($this->cnn_id) {
      /*
      You can define all the database connections you need in this
      switch statement.
      */
      case 0:
        $this->db_type = "mysql";
        $this->server = "";
        $user             = "";
        $pwd               = "";
        $this->db     = "";
        break;
      case 1:
        $this->db_type = "mysql";
        $this->server = "";
        $user             = "";
        $pwd               = "";
        $this->db     = "";
        break;
      case 2:
        $this->db_type = "postgres";
        $this->server = "";
        $user             = "";
        $pwd               = "";
        $this->db     = "";
        break;
      case 3:
        $this->db_type = "mssql";
        $this->server = "";
        $user             = "";
        $pwd               = "";
        $this->db     = "";
        break;
    }

To use the class in your scripts, you first need to include the class file. Modify according to where you placed the class file.

require_once(dirname(__FILE__).'/class_db.php');

Next, you instantiate the db object. A database connection is not actually created until and if you issue a command that requires a connection. This is a powerful feature of class_db. The connect() method in the class allows you to hard-code multiple database connections. When you create a new db object, pass in the index to the connection you want to use. If you pass in nothing, the default zero index will be used.

$d = new db(0);

The caching feature requires a directory on your webserver to save the cache files. If you prefer, you can hard-code this in the class itself by modifying the 'dir' property in the db() function (the class constructor). The class will default to storing the cache files in the current directory, but for security, you should store them in a non web-accessible directory. You can set this property per object using the code below. You must end the value with a "/". If you do not plan to use caching, don't worry about this property.

$d->dir = "/home/foo/bar/";

Use the fetch() method to execute a SQL statement that returns a dataset. fetch() returns an index array where each item is a row produced by your query. Each item in this array is an associative array where each item is a column from your query. fetch() returns FALSE if there was a failure. In this example, we've decided not to use caching.

$data = $d->fetch("select * from users order by last_name");
if ($data === FALSE) {
  echo "<h2>db fetch() failed!</h2>";
  echo $d->log;
  exit();
}

To use caching, pass in a TTL. The TTL is the number of seconds that you want to consider the data "good". For example, if you do not want the dataset queried from the database more than once every 5 minutes, set the TTL to 300. This will tell the class to query the dataset from the database and cache the results to disk. Any subsequent resuests during the next 5 minutes will use the cache. In fact, the class will not even make the database connection unless it actually needs to query the database directly. The caching is transparent to your application!

$sql = "select year, make, model, mileage"
  ." from cars"
  ." where mileage < 100000"
  ." order by mileage";

$data = $d->fetch($sql, 300);

A very useful and special TTL setting is "daily". This will tell the class that the cached data is good as long as it was queried today. I often use this for queries to small lookup tables where the data rarely changes.

$data = $d->fetch($sql, "daily");

Optionally, you can pass a "query name" to fetch() that will be used to name the cache file. This is useful if you want to be able to distinguish which cache files belong to which queries. The default is to create a filename using an MD5 hash of the SQL statement.

$data = $d->fetch($sql, 300, "cars_less_100000");

The class provides a dump() method that is useful for testing and debugging. It will output a basic HTML table from the data array. It is also an excellent example for how to work with the data array.

$d->dump($data);

Of course, most likely, you don't just want to dump the data to the screen. You can iterate through the rows like so:

foreach($data as $row) {
  echo "<hr />Year: ".$row['year']
    ."<br />Make: ".$row['make']
    ."<br />Model: ".$row['model']
    ."<br />Mileage: ".formatnumber($row['mileage'],0);
}

Or you can grab a specific column within a specific row.

echo "<hr />Data in the 'model' column of the 5th row: ".$data[4]['model'];

The class includes two functions to help you create your SQL statements. They ensure that single ticks are converted into double single ticks and replace empty values with the NULL keyword. They also do a few other thigns depending on whether the value is a string or numeric. Read the comments in the class file for more information about fmt() and fmt2().

$sql = "insert into cars (year,make,model,mileage) VALUES ("
  .db::fmt($year,0)
  .db::fmt2($make,0)
  .db::fmt2($model,0)
  .db::fmt2($mileage,1)
  .")";

Use the exec() method for any SQL statement that does not return a dataset such as INSERT, UPDATE, and DELETE statements. exec() returns the number of rows affected or FALSE if failure.

$rows_affected = $d->exec($sql);
if ($rows_affected === FALSE) {
  echo "<h2>Query execution failed!</h2>";
  echo $d->log;
  exit();
}

There are two ways to see the number of affected rows. Either use the returned value or the object property. (This number is also in the object's log text.)

echo "<br />".$rows_affected." rows affected<br />";
echo "<br />".$d->rows_affected." rows affected<br />";

Did you just INSERT into a table with an auto-number or IDENTITY column, and you need to know the new id? The last_id property is available to you after you run the exec() method.

echo "New ID: ".$d->last_id."<br />";

Finally, anytime you have problems, be sure to look at the 'log' property which will give you specific information related to problems with your database connection, query, or problems with caching.

echo "<h1>Log</h1>";
echo $d->log;

PHP Database Class with Caching

class_db.php

Why this class?

Caching!

About the author