The PHP Database Class

The Database Class will interact with a MySQL database and take care of SQL queries and return data and resultsets we ask for. We start looking at the 6 private class variables and then off with the class functions, there are 10 of them: __construct(), _connect(), fetchQuery(), fetchTuple(), fetchObject(), fetchArray(), fetchNum(), dataSeek(), fetchTableFieldNames() and finally close().

Database class variables

All of the private variables will be set when we create a new instance of our class. When using the new keyword we are calling our public __construct() method which looks like this:

Database __construct method

The __construct function takes 4 parameters: $database, $server, $user and $password. All 4 parameters are required. Inside the function we are just assigning each private class variable with the incoming parameters. We are set the number of queries to 0 and at the very end we are calling the private _connect() function.

The _connect() function is simple. It takes no parameters since it’s using the private class variables we assigned in the __construct() function. The function tries to make a MySQL connection with the saved $this->server, $this->user and $this->password variables and lastly tries to select a database, which is our $this->database variable.

Database _connect() method

By calling the _connect() function in the __construct() function we will get an error as soon as we try to create the instance of the class if our arguments are wrong in some way. The errors are triggered by the trigger_error() method found in PHP and it corresponds to our PHP Error Handler class. To get the Database class working as it should, you must either edit the trigger_error() lines in the Database class or implement the PHP Error Handler class together with the Database class.

Moving forward we end up at the public fetchQuery() function. This function will take a SQL string as it’s parameter, add 1 to the number of total queries executed and try to execute the SQL command we provided. It will then return the result it receives from the MySQL. If we encounter an error we are once again using the trigger_error() built-in PHP method to handle it.

Database fetchQuery() method.

Sometimes we might want to receive a unique result and not a full result set. With the fetchTuple() function we will be able to supply a SQL string and get a non-resultset result back. The function will call our previous function fetchQuery() and get a result set back. It will fetch the first row using the mysql_fetch_row command and return the first coloumn in that row. It will return an actual value instead of a resultset.

Database fetchTuple() method.

Now we have functions to create and connect to a MySQL database (__construct() and _connect(). We have a fetchQery() function which receives a result set and a fethchTuple() function which receives an actual value. What would we need more? Well, a function accessing a whole row and not just one value in one row. This is done by the public fetchObject() and fetchArray() functions. The reason the first is called fetchObject() it due to the fact that we handle the result from the database as objects using the mysql_fetch_object method. As for the fetchArray(), we are using an array instead.

The first function takes one parameter, a $result. This parameter can be empty and if that’s the case we will use the private class variable $this->result which we save everytime we do a function call to fetchQuery() and fetchTuple(). Inside the function we are doing a few controls to make sure we have a $result we can use. If the result is null or no rows can be found in the result we will return null. Otherwise we will return our object holding the information from the database. This function will be used in the Record Class. The fetchArray()function is almost exactly the same as the fetchObject() except for the parameters. Instead of one fetchArray() takes two: a resultset and a constant. This constant have a default value but can be set to MYSQL_ASSOC, MYSQL_NUM or MYSQL_BOTH depending on what type of result you want. You can read more about the different contants at the PHP.net website.

Database fetchObject() and fetchArray() method

Next up is the fetchNum() method. The function takes one optional parameter, a result. Either we can provide a result we have received by using the fetchQuery() function or we can forget about the parameter and the function will take the last result we worked on. It will then return the number of rows found in that result. If something is wrong it will use the trigger_error() method.

Database fetchNum() method

Next is our dataSeek() function. It’s really short and easy. You need to provide two parameters: a result, and row number. The best way of describing the mysql_data_seek method is to take the description of the method from the PHP.net website:

“mysql_data_seek() moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number”

Database dataSeek() method

Now we are almost done with presenting the Database class. We have two more functions to go. The second to last is called fetchTableFieldNames(). The function will return all field names found in a table. It will be used in our Record class. The function  takes a table name as it’s parameter. It will then use the fetchQuery() function inside of the Database class to retrieve a result set. With a for-loop we will go through this result and extract the name of each column/field using the mysq_field_name() method provided by PHP. Each column/field name is saved in a array which we return in the end.

Database fetchTableFieldNames() method.

The final function is the close() function. When we are done using the class we want to close our connection to our MySQL object, this is done by using the mysql_close() PHP command.

Database close() method

And that’s it!

• Download the Database class

Take a look at the code, download the whole PHP file and see if you can find anything to improve and change.

Related PHP classes using the Database class:

• The PHP Paging Class
• The PHP Record Class
• The PHP Forms Class