David Jeffries

Download the source

I wrote a post about why I love the singleton and used a database class as an example. Here I'll explain the basic database class I use to connect to MySQL which demonstrates the implementation of the singleton design pattern.

As explained in my post about singletons, this forces/ensures that only one connection to the database is ever made. So to get the DB class object, you would just:

<?php
  $db = DB::getInstance();
  $db->query(/*your query in here*/);
?>

Once that is done, anywhere else in your code that you need to get a database object, you just do the same thing over again. You call the "DB::getInstance()" method, and it will give you the object currently in memory.

This is very useful because you are not constantly opening and closing connections, and you are never opening more than one connection to the database. You are instead only opening a connection at the last possible moment, (right before you query), and reusing the connection later (if necessary).

The Query Method

This is a method I thought up one day when I really wanted to use PHP's sprintf function. I didn't want to write

<?php
  query(sprintf("select %s, %s from table name where id = %u", "col1",
    "col2", "33523"));
?>

So instead of always having to pass the output of the sprintf function, I made it so that the query function accepts the same parameters of sprintf.

With this, instead of writing the code above, you can write

<?php
  $db->query("select %s, %s from table name where id = %u", "col1",
    "col2", "33523");
?>

which removes the need to always use the sprintf function (well, it's still used, but used inside the query function). This makes it really easy to build complex query strings. Making the query function compatible with the sprintf function is one of the most useful things I've done.

How query() works
The code for the query is the following

<?php
function query($query, $arg0, $arg1){
  $d = func_get_args();
  $this->qCount++;
  //merge the arguments array, and pass that to the sprintf function.
  //then send that built string to the mysql_query function.
  return mysql_query(call_user_func_array("sprintf", array_merge($d)));
}
?>

The two main parts are func_get_args() and call_user_func_array(). The first, func_get_args(), gets an array of all the passed parameters to the function. This is what allows the query function to have unlimited arguments, even though not all of them are named.

The second important part, call_user_func_array() allows you to call any function with the supplied array of parameters. Calling call_user_func_array with the array of parameters that are given to query, and saying you want to call "sprintf" is what builds the string.

That's about it. Your code should now be a little nicer and cleaner, and building complex queries will be much easier to work with and read.