We want to create a PHP5 OOP class which can help us connect to our MYSQL server.
We could not decide if we wanted to use the MYSQL or MYSQLI connector, so we use them both (and can select which one to use).
We should be able to have multiple connections open at the same time, using both connectors.
We should have a single class only containing the database information (hostname and such) so we can have multiple connections at ones.
We want to test the class using both connectors at the same time in the end of this tutorial.

If you are new to OOP development in PHP5, please read one of the other OOP PHP5 tutorials on this page before reading this.

First, we need to create the class that contains the configurations of our databases:

This class will only contain some fields we can use to store our data in and a constructor to feed the fields with data and a deconstructor to clean up the object then we decides to remove it.

The fields are:

hostname: the hostname of your database.
username: the username used to login to your database.
password: the password in clear text to login to your database.
database: the name of the database.
prefix: if you decide to use table prefixes, you should enter the prefix here as: "prefix_".
constructor: this is IMPORTANT: type mysql or mysqli here to select which database connector to use, we decide to use mysqli for you if you can't decide it.

We can use the config class as this in the examples below and in your code:

Constructor:
This function are called every single time we create an object of the db class and takes one argument, the config class object.
If you decide not to use the argument, the variables inside will be assigned some default values, and it is here we select to use the mysqli connector as default.

Destructor:
This function does the clean up and removes everything from the object when we decide not to use it anymore (or the pages that we have build are done loading in the browser).

Before we can use the db class to anything, we need to add some functions to open and close the connections.

Function to open connection: (remember that we wanted to use two connectors here).

This function first look at the selected connector and if we decided to use mysql, it will connect to the database using mysql and in the same way, it can connect to the database using mysqli, it also selects our database using the correct function (depending on if you selected mysql or mysqli as connector).

If you have a fast-moving head, you could see that we have decided to make the openConnection function PUBLIC, the reason for this is that we want to be able to use this class in many ways, we have added a little feature here, so we can use this class to hold the connection open ONES for the hole website or for every single time we want to run a query (I will add an example of what i mean).

Again, this function reads our config object to see which connector we are using and then closes the connection of the right connector, this function is also used public, because we want to be able to call it then needed outside the class.

How much fun do we have now?, we can open and close a connection to a database using both mysql and mysqli connectors, but we can't use the class to anything, the feature we need is to run queries of cause.

But before we adds a function to run a query, we need to think a little, because people on websites can't be trusted, they perform SQL injections if they can, so we need to be sure of that they can't on our classes, what we need is a function to add a " / " char before every " ' " char in the queries, this will stop people from attacking our databases with crappy inputs.

First we changes the prefix as described, next we checks if the connection to the database is open, if not, we need to open it (if the connection is closed here, we know that we have selected to open and close the connection every single time we run a query, if the connection is open here, we know that we have decided to have one connection open for every browser used to access our site, and to save server resources), last we stores the results of the query in the lastQuery variable.

The last thing, we will of cause also handle the database connector here. but we did store the data in the lastQuery variable, this is because we can use this to output the last query used by the user.

Let us add a little bonus function to output the last used query to the browser ( if you need it ):

For every example, we have placed the two classes in a file called: db.class.php

For every config object, you can use: MYSQL or MYSQLI.

1. example: a simple connection to a database, using only one connection for each browser.

<?PHP
// We start by including the classes.
include('db.class.php');
// We need to have 1 config object with all data in.
$config = new config("hostname", "username", "password", "database", "prefix", "connector"); //type in your data here…
// Now we need to have access to the db class, we uses the config object to configure the db object.
$db = new db($config);
// We can now open the connection to the database.
$db->openConnection();
// If your config details are right, we are now connected to a database, lets test the connection before we run queries.
$are_we_online = $db->pingServer();
// The variable $are_we_online should be true (or 1) if we are connected to the server.
echo "Are we online: " . $are_we_online; // prints 0 or 1.
// Let us run a query.
$sql = $db->query("SELECT * FROM {table}");
// The variable $sql will now hold the data returned from the database, we can now work with it.
// Does it have rows ?
$hasRows = $db->hasRows($sql);
echo "Does it have rows: " . $hasRows; // prints 0 or 1 (true or false).
// How many rows does it have.
$countRows = $db->countRows($sql);
echo "How many rows: " . $countRows; // returns the number of rows.
// We can get the data from the fetch_assoc function.
$result = $db->fetchAssoc($sql);
// We can get the data from the fetch_array function.
$result = $db->fetchArray($sql);
We can even print out the latest used query:
echo $db->lastQuery();
// As a last thing, close the connection.
$db->closeConnection();
?>

2. example: connection to a single database using the "one connection pr query"-method, this will slowdown your code a little and generate more resource-use on the server.

<?PHP
// We start by including the classes.
include('db.class.php');
// We need to have 1 config object with all data in.
$config = new config("hostname", "username", "password", "database", "prefix", "connector"); //type in your data here…
// Now we need to have access to the db class, we uses the config object to configure the db object.
$db = new db($config);
// We don't need to start the connection here, because we opens it every single time we runs a query.
// If your config details are right, we are now connected to a database, lets test the connection before we run queries.
$are_we_online = $db->pingServer();
// The variable $are_we_online should be true (or 1) if we are connected to the server.
echo "Are we online: " . $are_we_online; // prints 0 or 1.
// Let us run a query.
$sql = $db->query("SELECT * FROM {table}");
// The variable $sql will now hold the data returned from the database, we can now work with it.
// Does it have rows ?
$hasRows = $db->hasRows($sql);
echo "Does it have rows: " . $hasRows; // prints 0 or 1 (true or false).
// How many rows does it have.
$countRows = $db->countRows($sql);
echo "How many rows: " . $countRows; // returns the number of rows.
// We can get the data from the fetch_assoc function.
$result = $db->fetchAssoc($sql);
// We can get the data from the fetch_array function.
$result = $db->fetchArray($sql);
We can even print out the latest used query:
echo $db->lastQuery();
// We don't need to close the connection here, because we only starts it when needed.
?>

3. example: multiple connections open at ones.

<?PHP
// We start by including the classes.
include('db.class.php');
// We need to have 2 config objects with all data in.
$config1 = new config("hostname", "username", "password", "database", "prefix", "connector"); //type in your data here…
$config2 = new config("hostname", "username", "password", "database", "prefix", "connector"); //type in your data here…
// Now we need to have access to the db class, we uses the config object to configure the db object.
$db1 = new db($config1);
$db2 = new db($config2);
// We can now open the connection to the databases.
$db1->openConnection();
$db2->openConnection();
// If your config details are right, we are now connected to the databases, lets test the connection before we run queries.
$are_we_online_1 = $db1->pingServer();
$are_we_online_2 = $db2->pingServer();
// The variable $are_we_online should be true (or 1) if we are connected to the server.
echo "Are we online width 1: " . $are_we_online_1; // prints 0 or 1.
echo "Are we online width 2: " . $are_we_online_2; // prints 0 or 1.
// Let us run a query.
$sql1 = $db1->query("SELECT * FROM {table}");
$sql2 = $db2->query("SELECT * FROM {table}");
// The variable $sql will now hold the data returned from the database, we can now work with it.
// Does it have rows ?
$hasRows1 = $db1->hasRows($sql1);
$hasRows2 = $db2->hasRows($sql2);
echo "Does it have rows: " . $hasRows1; // prints 0 or 1 (true or false).
echo "Does it have rows: " . $hasRows2; // prints 0 or 1 (true or false).
// How many rows does it have.
$countRows1 = $db1->countRows($sql1);
$countRows2 = $db2->countRows($sql2);
echo "How many rows 1: " . $countRows1; // returns the number of rows.
echo "How many rows 2: " . $countRows2; // returns the number of rows.
// We can get the data from the fetch_assoc function.
$result1 = $db1->fetchAssoc($sql1);
$result2 = $db2->fetchAssoc($sql2);
// We can get the data from the fetch_array function.
$result1 = $db1->fetchArray($sql1);
$result2 = $db2->fetchArray($sql2);
We can even print out the latest used query:
echo $db1->lastQuery();
echo $db2->lastQuery();
// As a last thing, close the connection.
$db1->closeConnection();
$db2->closeConnection();
?>

4. example: using multiple connectors but only have the connection open when needed.

<?PHP
// We start by including the classes.
include('db.class.php');
// We need to have 2 config objects with all data in.
$config1 = new config("hostname", "username", "password", "database", "prefix", "connector"); //type in your data here…
$config2 = new config("hostname", "username", "password", "database", "prefix", "connector"); //type in your data here…
// Now we need to have access to the db class, we uses the config object to configure the db object.
$db1 = new db($config1);
$db2 = new db($config2);
// Because we want only to have an open connection when needed, we don't have to open it here.
// If your config details are right, we are now connected to the databases, lets test the connection before we run queries.
$are_we_online_1 = $db1->pingServer();
$are_we_online_2 = $db2->pingServer();
// The variable $are_we_online should be true (or 1) if we are connected to the server.
echo "Are we online width 1: " . $are_we_online_1; // prints 0 or 1.
echo "Are we online width 2: " . $are_we_online_2; // prints 0 or 1.
// Let us run a query.
$sql1 = $db1->query("SELECT * FROM {table}");
$sql2 = $db2->query("SELECT * FROM {table}");
// The variable $sql will now hold the data returned from the database, we can now work with it.
// Does it have rows ?
$hasRows1 = $db1->hasRows($sql1);
$hasRows2 = $db2->hasRows($sql2);
echo "Does it have rows: " . $hasRows1; // prints 0 or 1 (true or false).
echo "Does it have rows: " . $hasRows2; // prints 0 or 1 (true or false).
// How many rows does it have.
$countRows1 = $db1->countRows($sql1);
$countRows2 = $db2->countRows($sql2);
echo "How many rows 1: " . $countRows1; // returns the number of rows.
echo "How many rows 2: " . $countRows2; // returns the number of rows.
// We can get the data from the fetch_assoc function.
$result1 = $db1->fetchAssoc($sql1);
$result2 = $db2->fetchAssoc($sql2);
// We can get the data from the fetch_array function.
$result1 = $db1->fetchArray($sql1);
$result2 = $db2->fetchArray($sql2);
We can even print out the latest used query:
echo $db1->lastQuery();
echo $db2->lastQuery();
// We don't need to close anything here, because we closes the connection after every query are run.
?>

I hope that this ( little and advanced ) tutorial on a dual connector for mysql can help you in your development.

EDIT: changed the name of the __destruct() functions to be valid, changed mysqli_query to have two arguemnts, changed constructor to use the config class the right way, changed $this->connector to $this->config->connector and changed PingServer function, changed a single variable name.

Replies To: Connect to your database using OOP PHP5 with mysql and mysqli.

the deconstructor of a class is called __destruct(), so __deconstruct() won’t be called. additionally __destruct() is called, when the object is unset … thus using unset() in the deconstructor is pointless (unsetting an already unsetting object) to dangerous (infinite loop). ->closeConnection() is what you would normally put in the destructor.

the config class (besides the fact that class names should start upper-case), never ever make private data (like login credentials) public, use protected or private instead. add accessor method to get the data out.

when you pass the config object to the DB class, why do you rewrite all the data into the DB class? you already have an object for that: the config class.

1) i wanted to store all information in their own variable to make it simple to read ( i think it is ).
2) I know that mysql connectors in PHP5 are outdated, but i wanted to create a function which could handle all versions of mysql servers and because of that we should use both mysql and mysqli. (if you read in the constructor of the db class, the mysqli are default selected).
3) i used the try catch to catch all mysql_error() and mysqli_error() codes and return them to the screen.
4) i have read that the mysql_ecape_string() is outdated and on the php.net site: "This function has been DEPRECATED as of PHP 5.3.0. Relying on this feature is highly discouraged." http://dk2.php.net/m...cape-string.php
5) i could add a function to handle the _affected_rows() that are missing.

Beside your comments, i myself found that $mysqli_query($query) are missing an argument, so i will update the tutorial to take care of that and your comment about the config class.

Thanks for the comment />

This post has been edited by Dormilich: 03 February 2015 - 10:05 AM
Reason for edit:: removed quote

1) although it might be more simple to read, it’s against the principle of OOP (data encapsulation). or in other words, if you have the login data in the DB class, why using a Config class at all?

2) mysqli supports MySQL 4.1+, curently we have 5.1 and I doubt anyone is using MySQL 4.0 and below

3) Errors are not Exceptions, those are two completely different ways of error handling. that’s why the or die(mysql_error()); construct is used so often. actually, if it would throw Exceptions, your PHP would complain about uncaught Exceptions, which just doesn’t happen for mysql/mysqli.

4) you’re right, mysql_escape_string() is deprecated, but I was talking about mysql_real_escape_string().

PS. you still need to fix your destructor. unset() does not belong there.

1) i have changed the constructor to use the config class in the right way.
2) i know of some web hosting companies in the EU which uses mysql server 4.0 />
3) do you want me to add the or die(); and remove the try catches ? i can read out the errors as it is now />
4) i have added the mysql and mysqli _real_escape_string functions.

I hope the results are good now />

This post has been edited by Dormilich: 03 February 2015 - 10:06 AM
Reason for edit:: removed previous quote

I think that the class as it is now is an okay starting point for people who need to connect to a database /> ( i know the code works for me /> )

And i agree that it could be made in another smart way, but this is how i wanted to code it and you can read the article for my result /> if you can code it in a more "OOP PHP5" way, you are free to submit your own classes.. fx as a snippet or a new tutorial on DIC />

This post has been edited by Dormilich: 03 February 2015 - 10:06 AM
Reason for edit:: removed previous quote

I think that the class as it is now is an okay starting point for people who need to connect to a database ( i know the code works for me )

I don’t insist on type hinting, just mentioning it as it can prevent a lot of errors.

Kuggi, on 23 March 2011 - 08:44 AM, said:

And i agree that it could be made in another smart way, but this is how i wanted to code it and you can read the article for my result if you can code it in a more "OOP PHP5" way, you are free to submit your own classes.. fx as a snippet or a new tutorial on DIC

Explanation:
The first part is the configuration. from the DI point-of-view, you only need to guarantee that the methods you use exist, no matter which class provides them. This is one more reason not to use public properties.

final: class cannot be extended. otherwise it should be pretty much self-explaining. The only assumption I use here is that the default values for mysql (host, username, password) are the same as for mysqli (IMHO, a sensible assumption).

Next is the DB class itself. Since you can have either mysql or mysqli the obvious choice is to use a Factory Pattern. The Factory class decides itself (right, there is no user choice!) which to use. If neither of the handlers can handle the database (i.e. wrong password) an Exception is thrown.

Now for the really interesting part. Each of the handler classes deals with its database layer (mysqli OR mysql). This is used to prevent code duplication (no need to test, which layer to use in every method). The interface assures that both classes use the same methods, so that the user does not have to concern itself whether he uses mysql or mysqli.

interface iDBHandler
{
// just an example, certainly more methods are needed in the final interface(s)
public function query($sql);
}