Download Source Code

Introduction

In this tutorial I want to present Create-Read-Update-Delete (CRUD) operation using Object Oriented Programming (OOP) approach. The significant of the OOP is provides a clear modular structure for programs which makes it good for defining abstract data types where implementation details are hidden and the unit has a clearly defined interface. With PHP, OOP started to become feasible with the release of PHP 4, but really came into its own with PHP 5 that is great leap in the evolution of PHP OOP. In this tutorial I need to use OOP based Database Management System (DBMS). So I want to choose MySQLi (MySQL Improved) DBMS which advantages is it is more secured. The MySQLi Extension (MySQL Improved) driver used in the PHP programming language to provide an interface with MySQL databases. The developers of the PHP programming language recommend using MySQLi when dealing with MySQL server versions 4.1.3 and newer.

Class Declaration

To design your php scripts or code libraries the OOP way, you’ll need to define/create your own classes where the class contains bunch of method, property. When you create a variable inside a class, it is called a ‘property’ and Function that is created inside a class – they are called ‘methods’.

Here I have created a class name Database. Inside the class Database I have declare the connection string property name $connection that’s visibility mode is public by which any methods inside the database class can be accessed.
Also I have declared some public method inside the Database class that need to perform the CRUD operation.

Classes are the blueprints for php objects. A class contains both data (variables) and methods (functions) that form a package called an: ‘object’. Here I have declare an object for the Database class named $obj.

MySQLi Connection

All objects can have a special built-in method called a ‘constructor’. Constructors allow you to initialize your object’s properties when you instantiate an object. If you create a __construct(); on the execution PHP will automatically call the __construct() when you create an object from your class.

To connect the MySQLi server I want to call a constructor method. Following I have create an object name $obj where is send four arguments those are hostname as localhost, username as root, database password as blank and database name is db_student.

Here I have create an __construct() inside the Database class that accept four arguments; such as hostname, username of the database, password for the database and name of the database.

If the connection is ok then “Connection is ok” message is shown on your browser or else thrown an error message due fail connection and your script will be gone die.

Create Database

At first I need to create a database that name was supply from the object($obj) via constructor. Here you need not to go to phpMyAdmin panel. This database will be created automatically through script execution.

You can check this database from phpMyAdmin where you can see the database name db_student is created.

After creating database “Database ‘db_student’ Created succefully!” message is show in your web browser.

Create Database Image

Create Table

Next we need to create table within database to store data. Here I have created a table “student” that’s name is assign in the variable $tablename.

Before create the table make sure how many fields Is required and what‘s type of data will be content within each field. Here I want to create a table name “student” and which contains three fields Roll, Name and Marks. Here, Roll field contains Integer data, Name field contains string data which character length is 50 and Marks field is contains Double value. So the corresponding sql query is to create table is as following that is declare in the $CreateTableSql variable.

Now time to call Create table method with Object and this method only accept $CreateTableSql variable.

Following is the definition of CreateTable method which create a table according to the query that you declare $CreateTableSql variable.

If table is created successfully then “Table has been created successfully” message will be shown or else an error message will be thrown to created table.

You can view the table from the phpMyAdmin. Following is the screenshot for the XAMPP localhost phpMyAdmin

Create Table Image

After creating the table “Table has been created successfully” message will be shown in your web browser. Here you need not to go to your database in the phpMyAdmin to create table manually.

Inset Operation

Now time to perform CRUD operation. At first I want to describe the insert operation.

To implement the insert operation, I have declared an associative array which array index is table column and value is data for the table column. Here each index name should be double quoted and value of the associative array should have following rules:-

  • String values must be quoted
  • Numeric values must not be quoted
  • The word NULL must not be quoted

According to the above rules, as Roll is numeric field so it should not be quoted, Name is string field so it should have quoted and at last Marks is numeric field (basically, Float) so it should not have quoted.

Now time to call insert method which accept two argument one is table name ($tablename) and other is associative array ($InsColumnVal) that you declared previous.

Following is the definition of insert method,

After completing the insert method execution, if perform insert operation successfully then “New record has been inserted successfully!” message will be displayed on your web browser or else error to insert record message will be displayed.

Data Insert Operation within Table

You can see the record from the phpMyAdmin where new record is inserted.

Delete Operation

To perform the delete operation, I have declared an associative array that is to be which table row you want to delete.

For example here I’m using an associative array which Roll is 4 and Name is Zahan; its means delete the data row which Roll is 4 and Name is Zahan. Here same syntax rule is allowed for the associative array that is described earlier in the Insert Operation section.

Now you can call the delete method with the object, where delete method accept two arguments one is table name ($tablename) on which this delete operation will be perform and other is delete column value associative array ($DelColumnVal).

Following is the definition of delete method which visibility mode is public. This delete method only performs the delete operation on the table and it can’t return anything.

After execute the delete method, you will show “Record has been deleted successfully” in your web browser or fail to thrown an error message.

N.B: In this delete method has restriction is that sql query WHERE clause only contains AND expression.

Update Operation

To update table data row I have declared two associative arrays one is $set that accepted set operation value against columns fields and other is $condition that accepted conditional operation value against the column fields. Here same syntax rule is allowed for the associative array that is described earlier in the Insert Operation section.

Now you can call update method which accepted three arguments. First is table name ($tablename) on this which table you want to perform the operation, Second is $set associative array that you declare previous and third is $condition associative array that also declare previous.

Following is the definition of update method which visibility mode is public. This method only update the record within table not return anything.

If record is exist in the table it will be updated successfully and “Record updated successfully” or else “The Record you want to updated in no longer exists”. If any problem is happen to update record then an error will be thrown.

Following is the screenshot for After Update Record and Previous Update Record.

Data Update Operation within Table

N.B: In this update method has restriction is that sql query WHERE clause only contains AND expression.

Fetch Operation

To fetch the data from table you can call fetch method which accept two arguments one is table name ($tablename) on which table you want to perform this operation and other is an array that contains the name of columns which column value you want to fetch.

Following is the definition of fetch method which fetches all rows and returns the result-set as an associative array.

As fetch method return an associative array is return so you can assign it into a variable and display as your wish.

”;

If you display the variable by the print_r() function then the output will be following…

OUTPUT:-

Close Connection

Although PHP automatically closes mysqli connection upon script termination, Instead of you want to close the connection just before script is complete, you can do so by just invoking the destructor method. But destructor method automatically invoked when the object is destroyed or its lifetime is bound to scope and the execution leaves the scope.

In this destructor method mysqli_close() function is call which closes a previously opened database connection.

Conclusion

The object oriented paradigm is an approach to programming that’s intended to encourage the development of maintainable and well structured applications. In this tutorial I am trying to explore CRUD operation using object oriented approach with MySqli database server in php. Hope it will be helpful for you to implement the CRUD operation by the MySqli-PHP. So go ahead to better coding… 🙂

LEAVE A REPLY