Object Oriented MYSQLi

Today we are going to use object oriented features with mysqli type connection to MYSQL database in this Object Oriented MYSQLi tutorial.
We are going to display all the data in tables present in our company database in a HTML table format on a webpage. We had done this before using a procedural way, but in this tutorial we are going to try it using object oriented features.

It will look something like this:

fig 1

So follow the steps below:

Create a new folder named Connect2 in the htdocs folder which is in the xampp folder located in C drive.
Open a new notepad++ document and save it as index.php in the newly created Connect2 folder in htdocs folder.
Open your browser and type localhost/Connect2 in its address bar. This is an initial output shown below:

fig 2

Now open the index.php file from Connect2 folder and write the following code:

<?php
//create connection

$mysqli=new mysqli('localhost','root','12345','company');

//Check Connection

if(mysqli_connect_errno())
{
	print_f("Connection Failed: %s\n",mysqli_connect_error());
}
?>

The above code is to create connection with MYSQL and check if we are connected to database successfully.
We had tried this code in procedural manner, so we will compare this object oriented code with the procedural code.
To create a connection in a procedural way we used a mysqli_connect() method as shown below:

$connect=mysqli_connect(‘localhost’,’root’,’12345’,’company’);

Whereas in object oriented way to create a connection we have directly made an object of mysqli that takes all information required for making connection viz. server name, username, password and database name.
It is shown below:

$mysqli=new mysqli('localhost','root','12345','company');

The connection is checked using the same function mysqli_connect_errno() method in both the ways.
But here if the connection fails we have used a function print_f() instead of echo to print the error message. It doesn’t matter, you can use anyone function.
The print_f() function statement used above is shown below:

print_f("Connection Failed: %s\n",mysqli_connect_error());

In this print_f() function “Connection Failed:” is a string. The substitution character %s is used to print the message given by mysqli_connect_error() method, %s will be replaced by the message. And \n is used for new line.
With this we successfully established our connection with our company database in MYSQL.

Now we want to fire a query to fetch data from the employees table in company database. Write the following below the check connection code:

$result=$mysqli->query("select * from employees");

To write the query in procedural way we use mysqli_query() method as shown below:

$result=mysqli_query($connect,“select * from employees”);

But in the object oriented way since $mysqli is an object, we need to use query() as the function called using $mysqli object.
This query() function directly takes the query.
It is more simple than the preocedural way.
The fetched data is stored in the $result variable.

Now make sure that you have completed the php tag, because we now have to design the HTML table that should be written outside the PHP tag.
Write the following code to create a HTML table showing the data fetched from the employees table using the select query.

<h1>Employees</h1>
<table width="500" cellpadding="5" cellspacing="5" border="1">
<tr>
<th>ID#</th>
<th>First Name</th>
<th>Last Name</th>
<th>Department</th>
<th>Email</th>
</tr>
<?php while($row=$result->fetch_object()):?>
<tr>
<td><?php echo $row->id;?></td>
<td><?php echo $row->first_name;?></td>
<td><?php echo $row->last_name;?></td>
<td><?php echo $row->department;?></td>
<td><?php echo $row->email;?></td>
</tr>
<?php endwhile;?>
</table>

Here, we have created a HTML table and used a while loop to traverse through the data fetched and stored in the variable $result.
In procedural way we had used the method mysqli_fetch_array() to get data from $result as shown below:

<?php while($row=mysqli_fetch_array($result)):?>

But in object oriented way we know that the select query returns an object per employee and stores it in $result variable making it a object.
So we directly call a method fetch_object() using $result, store it in $row and display its fields one by one.
The details of one employee are displayed in a row.
Now as $row stores an object in it, it is also treated as an object.
So the fields are displayed as $row->id , $row-> first_name , etc. as shown above.
ends the while loop.
Reload the page. The output of the employees table is shown below:

fig 3

Now we completed Employees table so now let’s display Products table.

Write the following query to fetch data from products as well as categories tables.
You need to write this query in the PHP tag, since it is the PHP code.

<?php
$result=$mysqli->query("select products.name,categories.name AS  'category',products.id AS 'prod_id'
 from products LEFT JOIN categories
 ON products.category=categories.id");
?>

This query will select product id and product name from products table and category name from categories table.

Now it’s time to design the Products table. Write the following code outside the PHP tag.

<h1>Products</h1>
<table width="500" cellpadding="5" cellspacing="5" border="1">
<tr>
<th>ID#</th>
<th>Product Name</th>
<th>Category</th>
</tr>
<?php while($row=$result->fetch_object()):?>
<tr>
<td><?php echo $row->prod_id;?></td>
<td><?php echo $row->name;?></td>
<td><?php echo $row->category;?></td>
</tr>
<?php endwhile;?>
</table>

Here, we have done the same thing as explained for employees table above.
The output of the Products table is shown below:

fig 4

Thus we displayed the MYSQL database table contents in the HTML table format on the webpage making programming more simple using object oriented features in this Object Oriented MYSQLi tutorial.