আগামী ৩০ নভেম্বর-২০১৭ তারিখ থেকে শুরু হচ্ছে পাঁচ মাস ব্যাপী Professional Web Design and Development with HTML, CSS PHP,MySQL,JavaScript, AJAX, JQUERY, Bootstrap and Live Project কোর্সের ৮৭ তম ব্যাচ এবং ২৪ নভেম্বর-২০১৭ তারিখ থেকে শুরু হচ্ছে চার মাস ব্যাপী Zend PHP-7 Certified PHP Engineering (Advance PHP) কোর্সের ৩৫ তম ব্যাচ। প্রত্যেকটি কোর্স এর ফী নির্ধারণ করা হয়েছে ৩০,০০০/= আগ্রহীদেরকে অতিসত্বর মাসুদ আলম স্যার এর সাথে যোগাযোগ করতে অনুরোধ করা যাচ্ছে। স্যার এর মোবাইল: 01722 81 75 91

CRUD Using OOP PHP and MYSQLI

We are about to create a basic CRUD using OOP. We are going to use OOP because it is easy to modify and add attribute to an object by create a new method in the corresponding class. In this tutorial we are using MYSQLI for database management system. MYSQLI is OOP based DBMS. It is an advance DBMS and much more secured. Any references go to MYSQLI section in PHP.net .

Here a single CRUD class will hold all the methods and properties of a CRUD. We keep the design and the processing separate here. So that the all the code looks neat and clean.

Database creation

Before start working on Crud class lets create a database for OOP Crud. This is a basic database with minimal data in it. Name this database “oop_crud” and create a table named “user”.

CREATE TABLE IF NOT EXISTS `user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(100) NOT NULL,

`email` varchar(150) NOT NULL,

`address` varchar(255) NOT NULL,

`mob` varchar(15) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

Now insert few user into it.

INSERT INTO `user` (`id`, `name`, `email`, `address`, `mob`) VALUES

(1, 'Masud', 'masud.eden@gmail.com', '', '01722817591'),

(2, 'Fahad', 'f@had.com', 'Dhaka,Bangladesh', '9879879'),

(3, 'Vimrul', 'vim@rul.com', 'lkdjf ajs flkaj ', '98679879'),

(8, 'sdfsdf', 'sdfsdsdf', 'dsfdsf sdf dsf', '5656456'),

(9, 'Fahad Billah', 'f@had.com', 'Dhaka,Bangladesh', '9879879');

Crud class

First a CRUD class should be created. Create a php file named “Crud_class.php” which will hold only Crud class in it.

Properties

Inside the class lets declare 2 properties initially, $mysqli and $data for database and user respectively.

$mysqli – handle all data for database

$data – handle all data for user.

These properties are declared as public also can be declared as private as no other class method gonna use it so far.

Constructor Methods

Constructor method is the method which is called first when an object is instantiate. It can take argument as parameter as well. Here we made this method public as we are going to instantiate object from anywhere as well as using constructor method.

“public function __construct($host,$username,$password,$db_name)”

In this constructor we are using 4 argument as parameter which we sent from object creation. Like below

“$obj=new Crud(“localhost”,”root”,””,”oop_crud”);”  this automatically use __construct() method. No need to write __construct(“localhost”,”root”,””,”oop_crud”) to use this method.

Every time an object created database connection is made and ready to use.

Destructor Methods

“Every Beginning Has an End” yap we need to close the database connection for security purpose. That can be done by calling destructor method. When an object stops executing it automatically calls destructor method which contain “$this->mysqli->close()”. Here “$this->” refers to the mysqli object we created earlier. So there won’t be any database connection active when no object is executing.

Initial portion of our Crud class is done.

C of Crud

The first portion of CRUD is C for create. In our create portion we will work on inserting data to database in a OOP way.

Create an “insert.php” page that will hold the design portion of our data insert form.


<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

Insert Data</pre>
<form action="insert.php" method="post">
<table width="500" border="1" cellpadding="5">
<tbody>
<tr>
<th scope="row" width="154">Name</th>
<td width="314"><input type="text" name="name" size="30" /></td>
</tr>
<tr>
<th scope="row">Email</th>
<td><input type="text" name="email" size="30" /></td>
</tr>
<tr>
<th scope="row">Address</th>
<td><textarea cols="20" name="address" rows="5"></textarea></td>
</tr>
<tr>
<th scope="row">Mobile</th>
<td><input type="text" name="mobile" size="30" /></td>
</tr>
<tr>
<th scope="row"></th>
<td><input type="submit" name="submit" value="Save" /></td>
</tr>
</tbody>
</table>
</form>
<pre>

When the form data submitted primary data handling will be done in the “insert.php” page as we can see action=”insert.php” here.

Every time a new user inserted (by form submission) a new object will be created and assign it to $obj.

include('Crud_class.php');

if(isset($_REQUEST['submit'])){

$obj=new Crud("localhost","root","","oop_crud");

extract($_REQUEST);

$obj->insert($name,$email,$address,$mobile);

}

Here we can see when instantiating new object we are passing 4 parameters.

$obj=new Crud(“localhost”,”root”,””,”oop_crud”);

These parameter will be handled by constructor method. Here parameters are database’s login info (host, username, password, database name).

Now our new object is connected to database. Here extract() let us use form’s field name as variable. Only add “$” before field name.

Back to Crud class we need to create a method name “insert” which will handle all gathered data to insert into database. We need to pass submitted data from “insert.php” page through the parameter of this method. As we are currently getting name, email address, address, mobile no. from the “insert.php” page form we are going to use $name,$email,$address,$mob as parameter.

Now create a query to insert data into “user” table and assign it to a variable “$query”.

$query=”INSERT INTO user SET name=’$name’, email=’$email’, address=’$address’, mob=’$mob'”;

As we mentioned MYSQLI is oop base DBMS use query() method in the following way.

$result= $this->mysqli->query($query) or die(mysqli_connect_errno().”Data cannot inserted”);

Here query($query) is a method of “mysqli” object which we instantiated in the constructor method of the CRUD passing $query as parameter. Using “$this->” refers to the CRUD class. Then assign it to $result. If not then die(mysqli_connect_errno().”Data cannot inserted”).

If query successfully executed and $result is true than redirect to the “read.php” page where we can see list of users. Currently we have no read.php though.

if($result){

header(‘location:read.php’);

}

So the insert() method is like this.

public function insert($name,$email,$address,$mob){

$query="INSERT INTO user SET name='$name', email='$email', address='$address', mob='$mob'";

$result= $this->mysqli->query($query) or die(mysqli_connect_errno()."Data cannot inserted");

if($result){

header('location:read.php');

}

}
 

R of CRUD 

R stands for read in CRUD. As we inserted data into database in OOP way now we need to see the output similarly. Lets create read() method inside CRUD class first then call it from “read.php” page which we did not create yet.

public function read(){

$query="SELECT * FROM user";

$result= $this->mysqli->query($query);

$num_result=$result->num_rows;

if($num_result>0){

while($rows=$result->fetch_assoc()){

$this->data[]=$rows;

}

return $this->data;

}

}
 

Create a variable $query and assign a query in it which select all data from user table.

$query=”SELECT * FROM user”;

Like insert() method call query method of mysqli object and assign it to $result.

$result= $this->mysqli->query($query);

num_rows determine number of rows in $result.

$num_result=$result->num_rows;

If any user found in the table run while loop and assign user data to $data which we created at the beginning of the CRUD class for handling user side data. After finishing the loop return data.

Now create a page named read.php. Include Crud_class.php at the beginning of the page.

include(‘Crud_class.php’);

Now create a new object “$obj” of CRUD class like insert.php page. And call read method of “$obj”.

Which will return $data variable with all user info.

Create a table with 6 column and 1 row which contain column name. Rest of the row of the table will be created automatically according to no. of user. So we are going to put rest of the table tag inside a loop.

We are going to use foreach loop as we are handling an array of string. Aliasing $obj->data as $val and used as loop condition so when no user available table row creation stops.

Here extract() helps us to use database column name as variable. Now put all the variable inside the corresponding box of the table.

Last column of the table contain edit/delete link which will be used to edit/delete the data of that row. Delete and edit are hyperlinked to delete.php and edit.php respectively containing id of the user. We did not create delete.php and edit.php yet.

Table creation is finished now we are able to see all user info from database and link to edit/delete user. Read portion of CRUD is finished. 

U of CRUD

U stands for update. This way a user info can be updated. We need to create a page name edit.php .

In this page total procedure occurs in 2 steps. First fetch the user data according to the user id we passed from other page and show properly in this page. And second after editing submit data which will be updated into the database.

At the beginning of the page include Crud_class.php and create a new object $obj.

Now check if id of the user which is going to be updated is sent via url. If true fetch all data of the id from the database.

$result=$obj->mysqli->query(“SELECT * FROM user WHERE id=’$id'”);

Here mysqli is the properties of “$obj” which contain the “mysqli” database object (from constructor of CRUD class). So the chain here is $obj object calling its properties mysqli which calling its method query(). Inside the query method select query is sent. Total query assigned into “$result”. Now call fetch_assoc() method to fetch data and save it to $rows.  Create a form like “insert.php”. Extract $rows and use as default value of those fields. So when the page load it shows earlier values. Here a user can edit any information.

</pre>
<form action="edit.php" method="post">
<table width="500" border="1" cellpadding="5">
<tbody>
<tr>
<th scope="row" width="154">Name</th>
<td width="314"><input type="text" name="name" size="30" value="<?php echo $name; ?>" /></td>
</tr>
<tr>
<th scope="row">Email</th>
<td><input type="text" name="email" size="30" value="<?php echo $email; ?>" /></td>
</tr>
<tr>
<th scope="row">Address</th>
<td><textarea cols="20" name="address" rows="5"><?php echo $address; ?></textarea></td>
</tr>
<tr>
<th scope="row">Mobile</th>
<td><input type="text" name="mobile" size="30" value="<?php echo $mob; ?>" /></td>
</tr>
<tr>
<th scope="row"></th>
<td>
<input type="hidden" name="id" value="<?php echo $id; ?>" />

<input type="submit" name="submit" value="Save" /></td>
</tr>
</tbody>
</table>
</form>
<pre>
 

After editing when user submit data it is checked by if(isset($_REQUEST[‘submit’])) . if true then extract $_REQUEST. Now call $obj objects method update() and use form field name as parameter.

if(isset($_REQUEST[‘submit’])){

extract($_REQUEST);

$obj->update($id,$name,$email,$address,$mobile);

}

Now lets create corresponding method for update() in CRUD class.

public function update($id,$name,$email,$address,$mobile){

$query="UPDATE user SET name='$name', email='$email', address='$address',mobile='$mobile' WHERE id='$id'";

$result= $this->mysqli->query($query) or die(mysqli_connect_errno()."Data cannot inserted");

if($result){

header('location:read.php');

}

}

Inside update method create a update query with respect to the user ID. Assign it to $query.

Call query method under mysqli object which is auto created when database connection is made from edit.php page.  Assign it to $result and then check if updated successfully redirect to read.php .

Update part of CRUD ends here.

D of CRUD

D stands for Delete of CRUD. We mentioned about delete earlier in “read.php” page. There we created a link Edit/Delete for each user. If Delete clicked it send to “delete.php” with corresponding user id.

Before delete.

After delete. (deleting id 3)

Now lets create “delete.php” page. Include “Crud_class.php” at the top. Create an object $obj like other pages.

$obj=new Crud(“localhost”,”root”,””,”oop_crud”);

Check if “id” sent from read page using isset. Now call delete() method of $obj object using id as parameter.

$obj->delete($_REQUEST[‘id’]);

Back into “Crud_class.php” create a delete() method into Crud class.

Create a delete query with respect to the user id and assign it into $query.

public function delete($id){

$query="DELETE FROM user WHERE id='$id'";

$result= $this->mysqli->query($query) or die(mysqli_connect_errno()."Data cannot inserted");

if($result){

header('location:read.php');

}

}

Now send this $query to query method() of mysqli object created at the top of “delete.php” when new object $obj instantiated(through the constructor).

Voilà!!! The user has been delete.

So we come to the end of the CRUD. All four part of the CRUD are described and hope you all understand properly.

Download the source file from link below.

I am Fahad Billah. A great enthusiast of web development. I wanted to work on something which is platform independent. So I thought PHP would be the best language that serves my purpose. Anyone can reach a website from anywhere no matter what OS/device he or she uses. I wanted to be a great web application developer in future. I am currently a CSE student and getting ready for my ZCE test.

27 comments on “CRUD Using OOP PHP and MYSQLI
    • Dude your Zip file is complete and best I have ever seen. It explains everything so clearly.. I tried to learn this Mysqli using OOP from so many places but nothing helped.. You!! are the mannnn!!

  1. Yeah it seems that your source code i svery interesting.
    Can u please send the all source code by email?
    I cannot have a web accesee to your facebook id.
    regards
    P SAVE

  2. Thanks for the great tutorial.

    i followed everything and works fine except for the edit.php. After editing, the record was not updated but rather a new record was added.

    I’m still new but i guess it has something to do with the form action=insert.php.

    Thanks

  3. there’s something wrong with your update . It doesn’t update rather it adds a new contact. But all in all great tutorial 🙂 what framework did you use ?

  4. Put update query like this in crud class:
    $query=”UPDATE user SET name=’$name’, email=’$email’, address=’$address’,mob=’$mobile’ WHERE id=’$id'”;

    and change action=”edit.php” in edit.php page.

    then one can successfully update. 🙂

  5. This is awesome dude…
    You made my day..I was looking for opp and mysqli technique to insert,update,delete data.This was my forth day in row failure.Finally Finally Finally Yes YeS Yes Yes thanks alot

  6. its a great tutorial. i haven’t tried the code yet but just reading through your article. its make sense to me and i believe to all others. you have stressed the basic concept óf oop using php particularly adopting crud.. keep it up with this great effort… thank you

Leave a Reply

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