class_db.php

Author: Troy Wolf (troy@troywolf.com)
Modified Date: 2006-03-19 22:11
Download: class_db.zip
View class source: class_db.php source

UPDATE: I no longer use this class myself. It works well and can be an excellent learning tool. However, I now use the popular PHP ADOdb class.

class_db.php is a database class that provides methods to work with mysql, postgres, and mssql databases. Other database types can be easily added. The class provides a common interface to the various database types. A powerful feature of the class is the ability to cache datasets to disk using a Time-To-Live parameter. This can eliminate a lot of unneccessary hits to your database! Also, a database connection is not created unless and until needed, thus saving precious database connection resources.

Why this class?

Why not just use the simple PHP functions for working with whatever database you choose? If you forget about caching for a moment, all this class really gives you is a simple and common interface to work with different database types. But face it, most of us only deal with a single database on a single server for most of our applications. So, sure, you don't need this class. But caching can be the performance boost you've been looking for.

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);

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");

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");

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;

About the author

Troy Wolf operates ShinySolutions Webhosting, and is the author of SnippetEdit--a PHP application providing browser-based website editing that even non-technical people can use. Website editing as easy as it gets. Troy has been a professional Internet and database application developer for over 12 years. He has many years' experience with ASP, VBScript, PHP, Javascript, DHTML, CSS, SQL, and XML on Windows and Linux platforms. Check out Troy's Code Library.