March 17, 2012 learn_php

Using MySQLi with PHP with an OO approach

Using MySQLi with PHP with an OO approach

Since I have been talking about a database connection while using PHP and MySQL, I have decided to write about the new library that has been incorparated within PHP. This library happens to be named MySQLi. The (i) stands for improved. This improved version of the library can be used from versions MySQL 4 and up. I will explain how to use this new library using an object oriented approach, but at the same time, will provide how to go at it, with a procedural process.

Connect to DB

To connect to a MySQL server, the following syntax is to be used:

Notice that we have used the error suppresor. In my recommendation, this error suppresor should only be used when you are able to catch an error on your own, and display an error message to the user. This suppresor has a warning in Warning


Currently the “@” error-control operator prefix will even disable error reporting for critical errors that will terminate script execution. Among other things, this means that if you use “@” to suppress errors from a certain function and either it isn’t available or has been mistyped, the script will die right there with no indication as to why.

Carrying on with our topic, as you can see, we have instantiated the mysqli class. Since this instantiation returns an object, we can now invoke the methods of this class. In turn, utilizing the procedural way, this returns a resource, thus representing the database connection. This means that each time you will call a mysqli function, this same resource is needed, which will indicate, what connection you are refering to.

Displaying an error

Since we have suppresed the error in this case, we have a function that tells us if the connection was successful or not. This function can be invoked the same way for both procedural and object oriented way. In my opinion, it ‘looks’ like a standalone function. To display an error to the user, we do it like this:

And that’s how simple it is to display an error to the user, and exit the process, since our logic will not run correctly due to the missing database connection.

Changing databses

If at a certain point, you need to change the databse, you can do so with the following:

Running a query

Let’s create a query to retrieve all the users and their information to display it on the screen. To do so, we would setup a string with our query, and send it to the function like this:

Remember that when using the OOP way, your returned result will be an object, as to the procedural way, you will get another resource. If the above query is not successful for any reason, our result will be a simple ‘FALSE’.

Get the results

Obviously, if we have just queried a database, it means that we need this data. If you would like to know how many records this query has returned, then we can go ahead and use the attribute num_rows. We achieve this with a simple line of code:

Now that we know how many results are, we can go ahead and loop through the results, and display them on the screen. Let’s go ahead and use a while loop. The fetch_assoc function will return an array for each record that was found, and each will have each key as their attributes, and each value in the array.

If you wanted to grab the results as an object, you could just use the fetch_object function.

Close connection

Although PHP automatically closes your connection upon script termination, if you want to close the connection before your script is done, you can do so by just invoking the close function. This is done by doing the following. First though, you should ‘free’ up the result identifier, which will free up the memory. Then use the close function, to close the connection.

There you have it folks. This is as easy as 1, 2, 3.

7 thoughts on “Using MySQLi with PHP with an OO approach

Leave a Reply

Your email address will not be published. Required fields are marked *

The qTranslate Editor has disabled itself because it hasn't been tested with your Wordpress version yet. This is done to prevent Wordpress from malfunctioning. You can reenable it by clicking here (may cause data loss! Use at own risk!). To remove this message permanently, please update qTranslate to the corresponding version.