Script Perfect

PHP Database Class, The Right Way

Posted by Tim On September - 8 - 2009

How many times do your fingers type out the commands to run a query or fetch a row? If you are performing database tasks on the majority of your pages and you have not simplified the process then you need to keep reading. Developing a database class is not very difficult, in fact there are plenty of them out there which can set you on your way. Once you start using them you will most likely never go back.

A database class should be thorough, simple, and save you time. A single command to connect or disconnect as well as most of your common functions should be included. Do not re-invent the wheel if you have a large scale project, there are plenty of pre-build APIs which will handle much more than the examples given here. Below is a simple class which can get you up and running as well as examples of how to use it, let’s take a look:

The Class:

class database {

  var $host;

  var $user;

  var $pass;

  var $database;

  var $persistent=0;

  var $last_query;

  var $result;

  var $connection_id;

  var $num_queries=0;

function configure($host, $user, $pass, $database, $persistent=0)

  {

    $this->host=$host;

    $this->user=$user;

    $this->pass=$pass;

    $this->database=$database;

    $this->persistent=$persistent;

    return 1; //Success.

  }

  function connect()

  {

    if(!$this->host) { $this->host="localhost"; }

    if(!$this->user) { $this->user="root"; }

    if($this->persistent)

    {

      $this->connection_id=mysql_pconnect($this->host, $this->user, $this->pass) or $this->connection_error();

    }

    else

    {

      $this->connection_id=mysql_connect($this->host, $this->user, $this->pass, 1) or $this->connection_error();

    }

    mysql_select_db($this->database, $this->connection_id);

    return $this->connection_id;

  }

  function disconnect()

  {

    if($this->connection_id) { mysql_close($this->connection_id); $this->connection_id=0; return 1; }

    else { return 0; }

  }

}

Above is the bare bones, this will allow you to connect or disconnect to your database, all you would need to do to connect is add the following lines to the beginning of your code. Remember, once you are connected you will not have to do so again.

require_once ‘database.php’; //This is your class

        $db=new database;

        $db->configure(‘localhost’, ‘username’, ‘password’, ‘database’, ‘persistent’);

        $db->connect();

        $c=$db->connection_id;

So now we are connected to our db, we can add more functions to our class now to make it more usable and friendly. Add the following functions:

function change_db($database)

  {

    mysql_select_db($database, $this->connection_id);

    $this->database=$database;

  }

  function query($query)

  {

    $this->last_query=$query;

    $this->num_queries++;

    $this->result=mysql_query($this->last_query, $this->connection_id) or $this->query_error();

    return $this->result;

  }

  function fetch_row($result=0)

  {

    if(!$result) { $result=$this->result; }

    return mysql_fetch_assoc($result);

  }

  function num_rows($result=0)

  {

    if(!$result) { $result=$this->result; }

    return mysql_num_rows($result);

  }

  function connection_error()

  {

    die("<b>FATAL ERROR:</b> Could not connect to database on {$this->host} (".mysql_error().")");

  }

  function query_error()

  {

    die("<b>QUERY ERROR:</b> ".mysql_error()."<br />

    Query was {$this->last_query}");

  }

  function fetch_single($result=0)

  {

    if(!$result) { $result=$this->result; }

    return mysql_result($result, 0, 0);

  }

  function clean_input($in)

  {

    $in=stripslashes($in);

    return str_replace(array("<",">",‘"’,"’","\n"), array("&lt;","&gt;","&quot;","&#39;","<br />"), $in);

  }

  function unhtmlize($text)

  {

    return str_replace("<br />","\n", $text);

  }  

  function escape($text)

  {

    return mysql_real_escape_string($text, $this->connection_id);

  }

  function affected_rows($conn = NULL)

  {

    return mysql_affected_rows($this->connection_id);

  }

The Use

So the above code added into your class will give you all of the basic connection and querying tools you should need to perform most tasks. You may want to modify and add additional functions to this class. So a basic query would now look like:

$result = $db->query("select * from table");

Escaping…
So if we wanted to take a group of variables posted from a form and escape them all we could write a small routine using our class. What the below would do is allow us to configure an array with the names of all the variables we are expecting. If the variable exists we escape it and return the variable as it’s original name. An example would be: a user fills out a form with two fields (username, and password), the form is posted to our php script. We are expecting those two inputs so we add it to our array. We then escape those values and return them as $username and $password. Take a look:

$expected = array(‘username’, ‘password’);

        foreach ($_POST as $newkey => $newvalue) {

                if (in_array($newkey, $expected)) {

                ${$newkey} = $db->escape($newvalue);

                }

        }

echo "Username: $username, Password: $password";

The above code will take any number of variables passed from your form and escape them all while returning them in a variable form. It is a great function which adds security by helping prevent injections.

Wrap it up

If you are running a small site and do not require complicated functions then the above class will be more than enough to save you time in development. Do not load a large API for small sites, this will most likely hinder performance instead of help it. Remove the commands that you do not use and add ones that you do. Having a small custom class like this makes all your other tasks just that much easier.

PHP, mySQL

9 Responses to “PHP Database Class, The Right Way”

ellisgl says:

September 14, 2009 at 2:36 pm

I would check this out: http://www.developer.com/lang/php/article.php/3604111
Tim says:

September 14, 2009 at 3:18 pm

That is a great article, I will have to try some of that out. Have you had the chance to try it using multiple connections to separate databases? I am curious to see if it is possible to use an abstract class with multiple interfaces to handle simultaneous connections.
Tom says:

September 15, 2009 at 5:49 am

Wouldn’t it be better to use an external library to do this kind of thing? For example the utilities at http://pear.php.net/.
John says:

September 15, 2009 at 6:38 am

When it comes to PHP and Database -> doctrine-project.org
Robert van der Linde says:

September 15, 2009 at 7:17 am

I would also recommend to look at the PDO functionality or at least the mysqli functions. Also, var is deprecated, look at private/protected/public.
Tim says:

September 15, 2009 at 8:44 am

Thank you to everyone for the input, it has been some time I guess. Maybe I should call this an old outdated way?
Kyle says:

September 15, 2009 at 11:30 am

It’s considered a best practice for a class name to start with a capital letter. You’ll want to make those variables private as well.
bcx says:

September 16, 2009 at 4:00 am

Tim, the only thing wrong with this article is the title. Here are some suggestions:

PHP Database Class, The Wrong Way.
PHP Database Class, The Right Way, in 2001 & PHP3.
PHP Database Class, and some other random functions.

You can use your own imagination from here on. Maybe some other people have suggestion?
Sander says:

September 16, 2009 at 4:15 am

Well Tim, using your own database class (that for example extends MySQLi) to add some functions of your own is not outdated. However, the way this article describes _is_ outdated.

I have an additional question: why did you create the clean_input()-function? You usually do not wanna store HTML in your database; you just wanna output it as HTML whenever it is needed. And to this extend you might wanna have a look at http://php.net/htmlspecialchars instead of making your own variation on this with str_replace().