Simple PHP-MySQL Database Connection Class Using mysqli

The mysql extension of PHP, which is the original MySQL API, was deprecated since PHP 5.5.0 and it is totally removed in PHP 7.0.0. As a PHP & MySQL developer, you now have two options for building your websites and applications; either the mysqli extension or the PDO_MySQL extension. You can still use the mysql extension but unless you really need to use it, I will not recommend using it as your websites or applications may stop working if the PHP module on your server gets updated to version 7.0 in the next couple of years, that is if you don't have full control over your hosting server.

Since you are reading this tutorial about mysqli, I will not go into the details about which extension to use and why, but if you would like to explore more information about choosing a MySQL API for your project, you can check this page on the PHP Manual.

Why Use a Class?

Naturally, the first thing you will need for your website or application to communicate with your database is to connect to the database. This can be accomplished in a couple of ways depending on the nature and the functionality of your script. If you are planning to make use of functions to do database related tasks such as reading from the database and writing to the database, and that you will use such functions on multiple scripts frequently, then it would be preferable to build a class to handle the database connection so that you will not have to re-write the same procedure again and again.

Depending on how you want your database connection class to work, it can be quite complex or quite simple. If you are a beginner or trying to find clarifications about properly connecting to a MySQL database, it is best to start with the simplest way. Now, let's see how the database connection class is built.

mysqli Database Connection Class

STEP 1: Define the Class

Our first step is to define the database connection class like the following:

class MyDB {

}

This is how a class is defined in PHP. You start with the keyword class, add your class name (MyDB) next to it and add opening/closing curly brackets. You can use any name for your class as you wish, as long as you remain within PHP's class naming rules.

STEP 2: Define the Database Variable

Next step is to define a database variable in our class that we will be using in defining our database details and handling the database operations.

class MyDB {
  public static $db;
}

The keyword public means that this variable can be accessed outside of this class (i.e., the functions in our script). The keyword static means that we won't need to instantiate the class in our scripts in order to access the $db variable.

STEP 3: Define Database Details

Next step is to define the database details so that our script will know which database to connect to. It is up to you where you store your database user and password details, i.e. in another file that is not placed in the public_html folder, but for the sake of simplicity, we will keep the database connection details on the same file as follows.

class MyDB {
  public static $db;
}

MyDB::$db = new mysqli('localhost', 'db_username', 'db_password', 'db_name');

What we did is that we created a new instance of mysql connection (via mysqli class) using our database details. The first parameter (localhost) is the database server where your database is stored. In most cases (on your local server or on a shared hosting etc.) the database server is localhost. If yours is different, simply change it.

The next three parameters are the database username, database password and database name respectively. Insert your own database information into the correct place.

From this point forward, whenever we will interact with the database in our scripts, we will be using MyDB::$db to handle it.

STEP 4: Check for Database Connection Error

Every once in a while, your database may be inaccessible due to various reasons such as server failure, script malfunction or excessive load. In such cases, rather than displaying a blank page or displaying the default error (which you shouldn't do on a live website for security reasons), it is better to let your visitor know that the database connection failed in plain simple words.

We use connect_error to do this check and display a notification when a database connection failure occurs:

class MyDB {
  public static $db;
}

MyDB::$db = new mysqli('localhost', 'db_username', 'db_password', 'db_name');

if (MyDB::$db->connect_error) {
  exit('Database connection failed, please try again in a few seconds.');
}

You can display any message you want via exit() or even display a custom error page if you prefer.

STEP 5: Set Character Set

This step is optional but my suggestion would be to use it unless you need to use a specific character set on your website or application.

Every database server has its own default character set. Even though you can specify the character set of each column on your database tables when building your database, there may be occasions where data or text is not displayed the way you meant it to on different browsers and devices. You may even never notice that unless your users report it since it is close to impossible to test your website or application on all available web browsers.

Therefore, after instantiating our database connection and checking for the connection error, we set the character set that our scripts will use when displaying data from the database server or inserting data to it.

We use set-charset() function to specify the character set with mysqli:

class MyDB {
  public static $db;
}

MyDB::$db = new mysqli('localhost', 'db_username', 'db_password', 'db_name');

if (MyDB::$db->connect_error) {
  exit('Database connection failed, please try again in a few seconds.');
}

MyDB::$db->set_charset('utf8');

The reason why we used utf8 is because it is the most inclusive character set. Again, unless you want to use a certain character set in your website or application, utf8 is the character set you should be using. You can see a list of character sets here if you need to use a different one.

This is how to connect to a database via a class using the mysqli extension. The mysqli database connection class we built can definitely be improved in some ways but as a basic start it should be enough to handle the database connection in your script. I will continue with other topics such as reading from the database and writing to the database in upcoming posts.

f t g+ in