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?

 

Related Posts Plugin for WordPress, Blogger...

Tell us what you think

  • http://www.sitening.com/ Tyler Hall

    First off, holy crap. When did all of the code display on our blog get so crappified?! (Our CSS guy is checking into it now…)

    Michael re: SQL injections…

    You’re totally right about it leaving you open to attack. However, the code in this post is nearly a year old. It has since been updated and built into a simple PHP framework we build all of our projects with here at Sitening. We’ve made the code open source. You can see the latest version of DBObject here:

    http://simple-php-framework.googlecode.com/svn/trunk/includes/class.dbobject.php

    And you can view our entire framework here:

    http://code.google.com/p/simple-php-framework/

  • Michael

    Nice idea, but it introduces a big security issue to your code – SQL injections. The rails layer doens a nice job on this, by letting the database driver escape the SQL code prior to execution. This makes the rails code protable while keeping the application safe from injections.
    That said, I totaly agree about rails being overhyped mess. It is much better than ruby, though, which takes features from every language known to man, smash them toghether and hopes for good. The operator overloading clashes with local variable assignment, for example, so if you want to write self code you need to invoke methods in the same object using self.method_name. and that’s from a language that is “optimised for programmer happiness”. And I’m not mentioning the code blocks concepts, which are very well done in SmallTalk but are just a burdon in Ruby.
    If you ask me, RoR is good for very simple applications. For anything else, I’d go with J2EE.

  • http://www.airmagination.com/ Ray

    My main concern about using Ruby on Rails was hosting compatibility. I had to scour the web before I stumbled across a reputable company that offered it (didn’t want to install it myself). There are some issues with the site displaying (it was needed for people to be able to browse listings of cars for sale), still trying to work the bugs out.

  • Chris

    theo: PHP 4 OOP code is not obsolete. If you actually used PHP then you would know this. The only thing that you have to watch out for if you are moving OOP code from PHP 4 to PHP 5 is the passing of objects by reference. It does it by default in PHP 5.

    Like Michael said, hate PHP for the right reasons. I like Ruby too, but Rails and Ruby too have a lot of work to do to in order to catch up and offer as wide a variety of extensions and functionality that PHP offers.

  • michael

    Unfortunately, as messy as PHP is, it’s the most widely available server-side language out there. I probably wouldn’t choose it for a local intranet app (because then you can choose your own server environment), but for portability and ‘lastability’, it’s probably the best-suited language to developing small/medium sites for customers.

    You also missed the main reason PHP is considered a messy language, it’s not the $’s or any other syntax symbols, but the horribly named and huge core function library. There are an incredible amount of functions available on a default PHP install, and they follow no naming pattern (e.g. first_second(), or firstSecond()).

    The $ sign was used in perl because it seperates variables syntactically from the rest of the language. The & was a good choice, because if they’d used ^ or something, it’d just be another oddity to remember. PHP5′s new OO behaviour isn’t breaking any code since as you say, most hosts still use PHP4 – and PHP4′s OO was pretty bad, so leaving it as is wasn’t really an option.

    +1, no namespaces is a screw-up.

    If you’re going to dislike PHP, do it for the proper reasons :P

  • Theo

    While I appreciate the fact that almost all current server side languages, from PHP to Java, now sport similar frameworks to Rails, I personally don’t like PHP, nevermind how popular it may be. The thing I don’t like about PHP is that PHP is IMO a massive hodge podge of syntaxes, copied from whatever language was du jour at the time of writing. For example:
    The $ variable prefix and the -> object member accessor: Perl (Is that dollar really necessary?)
    The & refernce sign: C pointers
    The interface and other OOP additions to PHP5: Java (and some C++) But they simply changed the way new objects are created, thereby making all PHP4 OOP code obsolete, nevermind that the vast majority of sites still use PHP4.
    The __get() and __set() accessor methods. Python says hi.

    All that, coupled with the mess of no namespaces, is very confusing, and, IMO again, simply ridiculous. What is next? Closures? It is also why Ruby and Python are gaining in popularity. They have a simple clear syntax.

  • http://blog.quantum-star.com Pádraic Brady

    I had a look at doing something similar in PHP4 (without the benefit of __set/__get overloading). I blogged my solution a short while back over at http://blog.quantum-star.com/index.php?/archives/201-PHP-Data-Objects-Simplifying-Business-Logic-without-SQL.html

    My requirements differed a bit, so mine is slightly more complex, uses named accessors, and a small spot of code generation.

  • http://www.sitening.com/ Tyler Hall

    Yup, there’s definitely lot of areas you could add to. For example, last night I added a method to automaticall load and clean $_POST vars. So, when a user submits a form, I can update the record using:

    $object->select($id);
    $object->postload();
    $object->update();

  • http://blog.tijs.org Tijs

    Nice idea! It wouldn’t be too hard to add filtering to the base classes and maybe even some error checking based on the field types… will be interesting to build this out a bit. Thanks!

  • http://www.theklaibers.com Nate

    I think this is a great start. Like you, I like things simple – and am too much of a control freak to give in to one specific framework. I often have different needs that aren’t easily done in different frameworks.

    I like your approach, and haven’t seen much of this in several of the different PHP frameworks (symfony, zend, cake, code igniter, etc).

    Keep up the good work.

    Peace,
    Nate