Getting Real With Databases in PHP

Raven

Getting Real With Databases in PHP

It’s no secret that I don’t like Ruby on Rails. My friends know it. My co-workers definitely know it. And I guess now you do, too. Ruby itself is a fine language, and there are lots of great web apps running on Rails, but the framework just doesn’t fit with my development style. I like to keep things simple. I’m too much of a control-freak to sell my soul to a particular framework.

That said, many of the ideas behind Ruby on Rails are spot on. I particularly like how well it abstracts database queries into separate objects for each table. That’s very cool and can save having to write a lot of repetitive code. But having to run a Rails script to generate new files is a little too cumbersome for me. Like I said, I prefer to keep things simple. Can we do the same thing with PHP? Using just one file?

Yes.

So, I present to you, my analogous solution in PHP. It’s not perfect. It may not be right for you, but I like it and use it in most of my projects.

What Does It Do?

The class I’m going to show you will be used as a base class that can select, insert, update, and delete records from any table we throw at it. What sets it apart from other database wrappers is that it overloads many of PHP’s built-in functions to allow greater flexibility and more natural syntax.

Here’s an example of the code we can write once our class is finished.

$user = new User();
$user->name = "Tyler";
$user->location = "Nashville, TN";
$user->insert();

$dog = new Animal();
$dog->select(5);
$dog->breed = "Dalmatian";
$dog->update();
$dog->delete();

That code demonstrates the four basic database commands

  • Select
  • Insert
  • Update
  • Delete

without having to write out the long mysql_query("blah blah", $db) function call. More importantly though, both object types (User and Animal) come from the same base class i.e., we’re not having to write duplicate SQL wrappers for each object we use.

PHP’s Secret Sauce

The voodoo that let’s us do this is the new object model that was introduced in PHP5. (You could do it in PHP4, but it wouldn’t be nearly as elegant or useful.) Specifically, the ability to overload an object’s constructor, __set(), and __get() methods are where the real magic takes place.

Creating DBObject

The first step is to create the class definition. We’ll call it DBObject since it allows us to treat each table in the database as an object.

class DBObject {
public $id;
private $id_name;
private $table_name;
private $columns = array();
function __construct($table_name, $id_name, $columns)
{
$this->table_name = $table_name;
$this->id_name = $id_name;
foreach($columns as $key)
$this->columns[$key] = null;
}
}

Inside the class we’ve declared four variables.

  • $id will store the actual ID of the record our object represents.
  • $id_name is the name of the ID column in the table
  • $table_name is, predictable, the name of the table
  • $columns is an array containing the names of all of the columns in the table (except for the ID field)

The function __construct() is where the object is initialized. It accepts the table name, ID field name, and the array of column names and stores each one in the appropriate class variable.

Get() and Set()

Next up we need to allow our users to be able to write $object->some_property as they would with any other class. But how can we do this if we don’t know ahead of time what variables we’ll be using? By overloading the __get() and __set() functions!

function __get($key) {
return $this->columns[$key];
}
function __set($key, $value) {
if(array_key_exists($key, $this->columns))
{
$this->columns[$key] = $value;
return true;
}
return false;
}

When a user writes $object->property PHP will pass “property” to the __get() function. We then lookup that variable and return its value.

__set() is very similar. It receives two arguments: the variable to set and its value. For example $object->foo = 42 would call __set('foo', 42). All we have to do is set the variable’s new value. If the variable isn’t defined (i.e., it’s not a column in the table) we simply return false.

Manipulating the Database

With the three magic functions taken care of, it’s time for the fun part. Like I said in the beginning, I’m all about keeping things simple and saving time. The following four functions are the ones I find myself having to write over and over and over again throughout all of my projects. By abstracting them into this class, I save myself a ton of time.

Selecting Records

We need to execute the SQL statement

SELECT * FROM table_name WHERE id = 'some_value'

and store the results into our object.

Here’s the code:

function select($id) {
global $db;
$db->query("SELECT * FROM " . $this->table_name . " WHERE " . $this->id_name . " = '$id'");
if(mysql_num_rows($db->result) == 0)
return false;
else
{
$this->id = $id;
$row = mysql_fetch_array($db->result, MYSQL_ASSOC);
foreach($row as $key => $val)
$this->columns[$key] = $val;
}
}

Note: In these examples I’m using my own custom database wrapper. 

In this function, the user passes an ID value which we’ll query the database for. We build the SQL statement using the values given in our object’s constructor and then execute the query.

If we don’t get any results, we return false. Otherwise, we set the object’s ID and loop through each column in the result, setting the appropriate variables.

Insert a New Record

To insert a record the user first has to set each variable in our object. For example, if we had a database table of people:

$person->name = "Elizabeth Troup";
$person->eye_color = "#802A2A";
$person->job = "Marketing Director";

Then they call $person->insert(). It’s at this point that we jump in, gather up the values they set, and insert the new record.

function insert()
{
global $db;

unset($this->columns[$this->id_name]);
$columns = join(“, “, array_keys($this->columns));
$values = “‘” . join(“‘, ‘”, $this->columns) . “‘”;
$db->query(“INSERT INTO ” . $this->table_name . ” ($columns) VALUES ($values)”);
$this->id = mysql_insert_id($db->db);
return $this->id;
}

There are two main parts to the INSERT query: The columns separated by commas and their corresponding values in single quotes separated by commas. Joining the column names is simple:

$columns = join(", ", array_keys($this->columns));

Joining the values is exactly the same except we need to remember to add opening and closing quotes.

$values = "'" . join("', ", $this->columns) . "'";

Now we have everything we need to put together our query, execute it, grab the ID of the new record and return it to the user.

Updating Records

Updating a record is very similar to inserting. We need to grab the column names, their values, and form the query.

function update()
{
global $db;

unset($this->columns[$this->id_name]);
$arrStuff = array();
foreach($this->columns as $key => $val)
$arrStuff[] = "$key = '$val'";
$stuff = implode(", ", $arrStuff);
$db->query("UPDATE " . $this->table_name . " SET $stuff WHERE " . $this->id_name . " = '" . $this->id . "'");
return mysql_affected_rows($db->db); // Not always correct due to mysql update bug/feature
}

There’s not much more to say on updating a record, however I would like to make note of the last line. When you update a mySQL record and nothing gets updated (i.e. you update it with the same data that’s already there) then mySQL reports 0 rows updated even though, technically, one was updated. Keep this bug/feature in mind while checking for success from this function.

Deleting Records

To delete a record, the user just needs to give us the ID.

function delete()
{
global $db;
$db->query("DELETE FROM " . $this->table_name . " WHERE " . $this->id_name . " = '" . $this->id . "'");
return mysql_affected_rows($db->db);
}

Extending DBObject

That completes our class. Everything is in place. Now we just need to make it useful by creating some simple classes with meaningful names that inherit from DBObject.

In the original example at the beginning of this post I used two example classes: User() and Animal(). I told you that they both derived from the same base class. Here’s how to set it up.

class User extends DBObject
{
function __construct()
{
parent::__construct('users', 'user_id', array('name', 'location', 'eye_color', 'job'));
}
}

class Animal extends DBObject
{
function __construct()
{
parent::__construct('animals', 'animal_id', array('name', 'breed', 'color'));
}
}

Both of these classes inherit from DBObject. In each of their respective constructors we pass three variables to the parent’s (DBObject’s) constructor:

  • The name of the table
  • Name of the ID column
  • An array of the column names in the table

Getting Real

This class isn’t perfect by any stretch of the imagination. Sure it makes building web apps a lot quicker. It takes a lot of the tedious code and wraps it up into a nice package. But you have to keep in mind that you’re going to be taking a performance hit because of the overhead from all the function calls. It becomes a balancing act. Do you favor quick development time over execution speed? Do you really care? If you subscribe to the recent Getting Real buzz, then you don’t care about performance. The goal is to get your web app out there before the next guy. Delivering half a product a week early is better than delivering a finished product a week late. Right?

Maybe?

 

Comments are closed on this post