Intelligent objects

November 16, 2008

Please note!! I have shamelessly stolen this article from my old and (even if I do say so myself) rather boring blog. So please ignore the terrible writing style! I’ll rewrite this article in the future, but until then, I present you with version 1.0

Anyone who has worked with the PHP/MySQL duo will no-doubt have at some point both added and edited a row in a database. As they have also no-doubt found, this requires a lot of SQL coding which can uglify their beautiful PHP code. One of the things which turns my brain into a sponge the most is adding and editing of objects to a database. Wouldn’t it be great if you could just save them, and let PHP/MySQL work out whether to insert them as a new row or just update an existing one? Well luckily, there is a way, read on if you would like to find out more…

I have used a couple of useful functions in this article, one of which is a pluralize function, thanks to Paul Osman and a function called getFieldsForClass, which does exactly what it says on the tin. Both are available to download here.

The first thing to do is to create a Generic Object class from which every other object will inherit some useful functions. I have implemented the solution in this way so that it is possible to call $object->save() which I thought was quite easthetically pleasing.

This article also assumes that your objects have fields which match the names of the columns in your table. A tutorial on how this can be done automatically will be written soon.

Class GenericObject
{
  function __construct ()
  {
    // nothing here
  }

  function save ()
  {
    // ...
  }
}

The way that the function will work is quite simple if you think about it logically. The question is, how do we know if the object is a new one, or if it is one which we are editing (hence just need to update the row rather than inserting a new one). Pretty much every object which you will need to save to a database will have an auto_increment id value for the primary key. (Ofcourse the auto_incremented id for primary key is not always used if another column will have unique values, but I stick the id column in for consistency’s sake). So now the problem is straightforwards; if the object already has an id, update the record, if not, create a new one. So this rather complicated problem has been broken down to essentially one IF statement (with a bit of code to deal with the database).

Class GenericObject
{
	function save ()
	{
		/* get the fields for the object to be saved */
		$fields = Functions::getFieldsForClass ($this);

		/* get the table name */
		$tableName = Functions::pluralize (get_class ($this));

		/**
		 * check to see if the object is already in the database
		 * (already has an id). If so, just update it, if not
		 * create a new database record.
		 */
		if (isset ($this->id))
		{
			/* update the record */
			$sqlF = '';
			foreach ($fields as $f)
			{
				/* skip over id */
				if ($f != 'id')
				{
					$val = $this->$f;
					$sqlF .= "`{$f}` = {$val}, ";
				}
			}
			/* remove the last ', ' */
			$sqlF = substr ($sqlF, 0, strlen ($sqlF) - 2);

			/* now we can make the final query */
			$sql = "UPDATE `{$tableName}` SET {$sqlF} WHERE `id`={$object->id};";
		}
		else
		{
			/* create a new record */
			$sqlF = '(';
			$sqlV = '(';
			foreach ($fields as $f)
			{
				/* skip over id as it's auto incremented */
				if ($f != 'id')
				{
					$val = $this->$f;
					$sqlF .= "`{$f}`, ";
					$sqlV .= "'{$val}', ";
				}
			}

			/* chop off ', ' and add ')' */
			$sqlF = substr ($sqlF, 0, strlen ($sqlF) - 2) . ')';
			$sqlV = substr ($sqlV, 0, strlen ($sqlV) - 2) . ')';

			/* finish the query construction */
			$sql = "INSERT INTO `{$tableName}` {$sqlF} VALUES {$sqlV}";
		}
		return somefunctionToQueryDatabase ($sql);
	}
}

Now when you want to create and edit objects, you can simply call the save function on the object. (A tutorial on something called an Object Factory will be coming soon which will help to retrieve objects from the database). When writing the code for your object class, be sure to give it the same variable names as the columns in the table, and to extend the GenericObject class.

Class MyObject extends GenericObject
{
  public $id;
  public $foo;
}

// example usage
$example = new MyObject ();
$example->foo = "bar";
$example->save (); // this will create a new row in the table

// code to retrieve object from database
$fromDatabase->foo = "foobar";
$fromDatabase->save (); // this will update the existing row

Managing your objects now couldn’t be simpler, and with my next tutorial on Object Factories, you’ll be waving goodbye to the frustrations of arguing with the sometimes seemingly untamable PHP and MySQL combination.

P.S. Apologies for the atrocious commenting style used.