আগামী ১৫ ডিসেম্বর -২০১৭ তারিখ থেকে শুরু হচ্ছে পাঁচ মাস ব্যাপী 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 with PDO and OOP PHP

In this article We are going to talk about the CRUD system with “PDO” in OOP PHP. Before we are going to take the tour on this article if you don’t know anything about PDO please just follow the link so that you can learn something from that page about PDO.

Learn PDO

This is the easiest method i think to implement a crud system with pdo. For this you should create  the sample table that i’ve created here just copy paste this table.

CREATE TABLE IF NOT EXISTS `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
`mobile` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`address` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`id`, `name`, `email`, `mobile`, `address`) VALUES
(1, 'Arif Billah', '[email protected]', '01722589654', 'Dhaka,Bangladesh'),
(2, 'Iqbal Hossain', '[email protected]', '01755847591', 'Thakur Gao, Bangladesh'),
(8, 'Monzur ', '[email protected]', '876868', 'Dhaka,Bangladesh');

Now it’s time for our oopCrud class with pdo. Just copy & paste the code given below & name it oopCrud.php

<?php

class oopCrud{
 private $host="localhost";
 private $user="root";
 private $db="primax";
 private $pass="";
 private $conn;

 public function __construct(){

 $this->conn = new PDO("mysql:host=".$this->host.";dbname=".$this->db,$this->user,$this->pass);
 }

 public function showData($table){

 $sql="SELECT * FROM $table";
 $q = $this->conn->query($sql) or die("failed!");

 while($r = $q->fetch(PDO::FETCH_ASSOC)){
 $data[]=$r;
 }
 return $data;
 }

 public function getById($id,$table){

 $sql="SELECT * FROM $table WHERE id = :id";
 $q = $this->conn->prepare($sql);
 $q->execute(array(':id'=>$id));
 $data = $q->fetch(PDO::FETCH_ASSOC);
 return $data;
 }

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

$sql = "UPDATE $table
 SET name=:name,email=:email,mobile=:mobile,address=:address
 WHERE id=:id";
 $q = $this->conn->prepare($sql);
 $q->execute(array(':id'=>$id,':name'=>$name,
':email'=>$email,':mobile'=>$mobile,':address'=>$address));
 return true;

 }

 public function insertData($name,$email,$mobile,$address,$table){

 $sql = "INSERT INTO $table SET name=:name,email=:email,mobile=:mobile,address=:address";
 $q = $this->conn->prepare($sql);
 $q->execute(array(':name'=>$name,':email'=>$email,
':mobile'=>$mobile,':address'=>$address));
 return true;
 }

 public function deleteData($id,$table){

 $sql="DELETE FROM $table WHERE id=:id";
 $q = $this->conn->prepare($sql);
 $q->execute(array(':id'=>$id));
 return true;
 }
}

?>

Now it’s time for to explain what we’ve done in here. First of all we create a class called oopCrud & have our hostname, username, password & dbname tobe private as we don’t want to share them with the viewers or others. Now what we see in the constructor method is we’re loading the db in this method so that we can do our works fine by just autoloading the db with the loading of class.

Now for the method first we can see that we’ve created a method called “public function showData($table)” here we are going to fetch all the data through PDO & then shown it to the view page.

public function showData($table){

 $sql="SELECT * FROM $table";
 $q = $this->conn->query($sql) or die("failed!");

 while($r = $q->fetch(PDO::FETCH_ASSOC)){
 $data[]=$r;
 }
 return $data;
 }

Then there is a method called “public function getById($id,$table)” this method just gets the element by the id we called in the edit page.

public function getById($id,$table){

 $sql="SELECT * FROM $table WHERE id = :id";
 $q = $this->conn->prepare($sql);
 $q->execute(array(':id'=>$id));
 $data = $q->fetch(PDO::FETCH_ASSOC);
 return $data;
 }

Then the other method called “public function update($id,$name,$email,$mobile,$address,$table)” here we are taking the values which are to be edited.

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

$sql = "UPDATE $table
 SET name=:name,email=:email,mobile=:mobile,address=:address
 WHERE id=:id";
 $q = $this->conn->prepare($sql);
 $q->execute(array(':id'=>$id,':name'=>$name,
':email'=>$email,':mobile'=>$mobile,':address'=>$address));
 return true;

 }

Then method called “public function insertData($name,$email,$mobile,$address,$table)” this is for the insertion of the data.

public function insertData($name,$email,$mobile,$address,$table){

 $sql = "INSERT INTO $table SET name=:name,email=:email,mobile=:mobile,address=:address";
 $q = $this->conn->prepare($sql);
 $q->execute(array(':name'=>$name,':email'=>$email,
':mobile'=>$mobile,':address'=>$address));
 return true;
 }

Then the last but the least method called “public function deleteData($id,$table)” this is for the deletion of a row according to ID we’ve sent for deletion.

public function deleteData($id,$table){

 $sql="DELETE FROM $table WHERE id=:id";
 $q = $this->conn->prepare($sql);
 $q->execute(array(':id'=>$id));
 return true;
 }

Now it’s time for our view part. First off all we are going to make the data show then insert and after that the edit part. And all these things we are going to do with “Twitter Bootstrap“. So we are going to learn here also how to use this amazing bootstrap thing.

Now first off all create a php file named show.php & copy-paste the code below:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
 <title>Show Table</title>
 <link rel="stylesheet" type="text/css" href="css/bootstrap.css">
 <script type="text/javascript" src="js/bootstrap.js"></script>
 </head>

<?php

function __autoload($class){
 include_once($class.".php");
}
 $obj=new oopCrud;

if(isset($_REQUEST['status'])){
 echo"Your Data Successfully Updated";
}

if(isset($_REQUEST['status_insert'])){
 echo"Your Data Successfully Inserted";
}

if(isset($_REQUEST['del_id'])){
if($obj->deleteData($_REQUEST['del_id'],"students")){

 echo"Your Data Successfully Deleted";
}
}
?>
<div class="container">
 <div class="btn-group">
 <button class="btn"><a href="show.php">Home</a></button>
 <button class="btn"><a href="insert.php">Insert</a></button>
</div>
 <h3 >All The Data</h3>
 <table width="750" border="1" class="table-striped">
 <tr class="success">
 <th scope="col">Name</th>
 <th scope="col">Email</th>
 <th scope="col">Mobile</th>
 <th scope="col">Address</th>
 <th scope="col">Action</th>
 </tr>
 <?php
 foreach($obj->showData("students") as $value){
 extract($value);
 echo <<<show
 <tr class="success">
 <td>$name</td>
 <td>$email</td>
 <td>$mobile</td>
 <td>$address</td>
 <td><button class="btn"><a href="update.php?id=$id">Edit</a>
</button>&nbsp;&nbsp;<button class="btn"><a href="show.php?del_id=$id">Delete</a></button></td>
 </tr>
show;
 }
 ?>
 <tr class="success">
 <th scope="col" colspan="5" align="right">
 <div class="btn-group">
 <button class="btn"><a href="insert.php">Insert New Data</a></button>
 </div>
 </th>

 </tr>
 </table>
</div>

<body>
</body>
</html>

In this page we can see what is that we’re creating an object of oopCrud class & autoloading the oopCrud.php which is the core file where all our methods are loaded within the class. Now how we did that is when we creating an object there we are calling the classname which is “oopCrud” and our file name is also the same as the class name that’s why this ” function __autoload($class) ” function is taking the class name & including the php file at once. Now we’ve included the css & js files from the bootstrap in the <head> tag so that we can use the css by only calling the class name as you can see in the div or table. Now as you can see we’ve use “ foreach($obj->showData(“students”) as $value)” this foreach & uses the heredoc to write the html code within php code it’s easy isn’t it. by the foreach we’re assigning the values that we’ve collected from the db. One thing in this part is that you are also seeing that we’ve also done the deleting part in here we’re just calling the method via object see the code below:

if(isset($_REQUEST['del_id'])){
if($obj->deleteData($_REQUEST['del_id'],"students")){

 echo"Your Data Successfully Deleted";
}

Show Table

Now it’s time for some insertion in the db. Create a page & name it insert.php & copy-paste the code below:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
 <title>Insert Data</title>
 <link rel="stylesheet" type="text/css" href="css/bootstrap.css">
 </head>

<body>
 <?php
function __autoload($class){
 include_once($class.".php");

}
 $obj=new oopCrud;

if(isset($_REQUEST['insert'])){
 extract($_REQUEST);
 if($obj->insertData($name,$email,$mobile,$address,"students")){
 header("location:show.php?status_insert=success");
 }

}
echo @<<<show
<div class="container">
 <div class="container">
 <div class="btn-group">
 <button class="btn"><a href="show.php">Home</a></button>
 </div>
 <h3>Insert Your Data</h3>
 <form action="insert.php" method="post">
 <table width="400" class="table-borderd">
 <tr>
 <th scope="row">Id</th>
 <td><input type="text" name="id" value="$id" readonly="readonly"></td>
 </tr>
 <tr>
 <th scope="row">Name</th>
 <td><input type="text" name="name" value="$name"></td>
 </tr>
 <tr>
 <th scope="row">Email</th>
 <td><input type="text" name="email" value="$email"></td>
 </tr>
 <tr>
 <th scope="row">Mobile</th>
 <td><input type="text" name="mobile" value="$mobile"></td>
 </tr>
 <tr>
 <th scope="row">Address</th>
 <td><textarea rows="5" cols="20" name="address">$address</textarea></td>
 </tr>
 <tr>
 <th scope="row">&nbsp;</th>
 <td><input type="submit" name="insert" value="Insert" class="btn"></td>
 </tr>
 </table>
 </form>
</div>
show;
?>

</body>
</html>

Now as you can see there we’ve done some same things that we’ve done in show.php file. The difference here is that we are inserting the data when we click submit the php checks if the button is isset & call the method via an object ok see the code below:

if(isset($_REQUEST['insert'])){
 extract($_REQUEST);
 if($obj->insertData($name,$email,$mobile,$address,"students")){
 header("location:show.php?status_insert=success");
 }

Insert Data

Now for the update of the data create a page & name it update.php & copy-paste the code below:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
 <title>Edit Data</title>
 <link rel="stylesheet" type="text/css" href="css/bootstrap.css">
 </head>

<body>
 <?php
function __autoload($class){
 include_once($class.".php");

}
 $obj=new oopCrud;

if(isset($_REQUEST['update'])){
 extract($_REQUEST);
 if($obj->update($id,$name,$email,$mobile,$address,"students")){
 header("location:show.php?status=success");
 }

}

extract($obj->getById($_REQUEST['id'],"students"));
echo <<<show
<div class="container">
 <div class="btn-group">
 <button class="btn"><a href="show.php">Home</a></button>
 </div>
 <h3>Edit Your Data</h3>
 <form action="update.php" method="post">
 <table width="500" border="1">
 <tr>
 <th scope="row">Id</th>
 <td><input type="text" name="id" value="$id" readonly="readonly"></td>
 </tr>
 <tr>
 <th scope="row">Name</th>
 <td><input type="text" name="name" value="$name"></td>
 </tr>
 <tr>
 <th scope="row">Email</th>
 <td><input type="text" name="email" value="$email"></td>
 </tr>
 <tr>
 <th scope="row">Mobile</th>
 <td><input type="text" name="mobile" value="$mobile"></td>
 </tr>
 <tr>
 <th scope="row">Address</th>
 <td><textarea rows="5" cols="20" name="address">$address</textarea></td>
 </tr>
 <tr>
 <th scope="row">&nbsp;</th>
 <td><input type="submit" name="update" value="Update" class="btn"></td>
 </tr>
 </table>
 </form>
</div>
show;
?>

</body>
</html>

It’s nothing else then the other parts. Just taking the values of the current ID by getby() method & seeing to the proper field then when we clicking the update button we’re again calling the method via the object.

if(isset($_REQUEST['update'])){
 extract($_REQUEST);
 if($obj->update($id,$name,$email,$mobile,$address,"students")){
 header("location:show.php?status=success");
 }

}

Edit Data

That’s it. This is where our tutorial ends. If it’s not so friendly please do leave a comment in here so that i can do better than this in the future. Thank you guys for reading this article & stay tuned because I’m going to make a paginataion article on PDO.

To download the full source code .

An enthusiastic of work with over 2+ years of experience. Extensively experienced on wordpress, Codeigniter, Laravel.

##Visit my github page (https://github.com/ashawkat)
##Visit my blogging page where I spread out my knowledges around all other people (http://w3programmers.com/author/tanim)

If you want to hire me then you can contact me via email or skype.

Skype: ashawkat89
Email: [email protected]

44 comments on “CRUD with PDO and OOP PHP
  1. Thanks a lot for your attention. It was my second tutorial. What i am learning is sharing here. So there may be some errors but hope that it’ll be good after some tuts .. 🙂
    stay tuned so that you can have more & great tuts from me ..

  2. Really good tutorial.
    Now to make it perfect you shlould add “order by” and pagination 😀

    well…. actually also a “search” feature would be nice

    anyway good job !!

  3. Hi
    Really good tutorial. However I have a question:
    How do I use your CRUD as a backend script to update, delete, create etc. content for the frontend of my homepage (website). Bascally what I am askiing is how do I turn CRUD into a CMS?

    • actually for a cms u need to know about what is cms? after knowing that what is in there fully featured dynamic site ! every bit of work in cms is a crud. a page creation or update or delete member update or delete everything. u just need to think about how cms really works that’s all .. 🙂

      • Ok… thanks… I will look into ‘what is a CMS?’ as per your suggestion. Fingers crossed it will work out. I’ll probably come back, if you don’t mind, if I get stuck. 🙂

  4. hlo, there is a code for crud in php code by me….. there is a problem in it.. plz help me to solve this…. i have a customers.php file in which i had declare all things.. and for the unit testing i have test.php.
    the insert function is working.. how the other functions will work???
    ——————-
    customers.php
    ——————-
    name = $name;
    $this-> desc = $desc;
    $this -> con = mysql_connect(“localhost”, “root”, “”);
    $this -> db = mysql_select_db(‘cust’);
    }

    public function insertData($desc_tbl)
    {

    $sql = ” insert into $desc_tbl values (NULL,'”.$this->name.”‘,'”.$this->desc.”‘);”;
    $res = mysql_query($sql);
    mysql_query($res);
    echo “Record Inserted”;

    }

    public function deleteData($id)
    {
    $sql = ” delete from desc_tbl where id=’$id'”;
    $res = mysql_query($sql);
    mysql_query($res);
    echo “Record Deleted”;
    }

    public function fetchData($id)
    {
    $sql = ” select * from desc_tbl where id=’$id'”;
    $res = mysql_query($sql);
    mysql_query($res[$id]);
    echo “Data is”;
    }

    public function updateData($name, $desc, $id)
    {
    $sql = ” update desc_tbl set name=’$name’ , desc=’$desc’ where id=’$id'”;
    $res = mysql_query($sql);
    mysql_query($res);
    echo “Record Updated”;
    }

    }
    ?>

    ————–
    test.php
    ————-
    insertData(“desc_tbl”);

    $c =new Customers($name,$desc,$id);

    echo $c->deleteData(“desc_tbl”);

    ?>
    plz help me
    Thanks

    • just call the functions like you’ve called insert function that’s it. for more info please read the full article carefully how it can do it’s work. it’ll give you the idea to implement the code i think. thank you ..

    • look at the code below:
      if(isset($_REQUEST[‘update’])){
      extract($_REQUEST);
      if($obj->update($id,$name,$email,$mobile,$address,”students”)){
      header(“location:show.php?status=success”);
      }
      }

      think that you’re going to update a name or info on your database. so you’ve created update function by which you’re going to update the fetched data. goto the edit page where you’re going to take the request of updating data. when you click submit for update you’ll grab the request by $_REQUEST[] method then just call your method or function of that class like given in the above code. that’ll do the tricks. thank you ..

  5. dear, plz check that code which i had sent you.. there are 2 files customers.php and test.php. in that case, only the insert function is working not 3 others. plz help me to solve this issue.
    i again sent you the code.
    customers.php
    ——————
    name = $name;
    $this-> desc = $desc;
    $this -> con = mysql_connect(“localhost”, “root”, “”);
    $this -> db = mysql_select_db(‘cust’);
    }

    public function insertData($desc_tbl)
    {

    $sql = ” insert into $desc_tbl values (NULL,'”.$this->name.”‘,'”.$this->desc.”‘);”;
    $res = mysql_query($sql);
    mysql_query($res);
    echo “Record Inserted”;

    }

    public function deleteData($id)
    {
    $sql = ” delete from desc_tbl where id=’$id'”;
    $res = mysql_query($sql);
    mysql_query($res);
    echo “Record Deleted”;
    }

    public function fetchData($id,$name,$desc)
    {
    $sql = ” select * from desc_tbl where id=’$id’, name=’$name’, desc=’$desc'”;
    $res = mysql_query($sql);
    mysql_query($res[$id]);
    echo “Data is”;
    }

    public function updateData($desc_tbl,$name,$desc,$id)
    {
    $sql = ” update desc_tbl set name=’$name’ , desc=’$desc’ where id=’$id'”;
    $res = mysql_query($sql);
    mysql_query($res);
    echo “Record Updated”;
    }

    }
    ?>

    test.php
    ———–
    insertData(“desc_tbl”);

    $c =new Customers($name,$desc,$id);
    echo $c->deleteData(“desc_tbl”);

    $c =new Customers($name,$desc,$id);
    echo $c->fetchData(“desc_tbl”,$id,$name,$desc);

    $c =new Customers($name,$desc,$id);
    echo $c->updateData(“desc_tbl”, $name,$desc,$id);

    ?>

    Thanks

    • no this is not the correct code .. you need to fetch the information first then you’ve to call the functions. please do check my code again if necessary please download the code that i’ve given you that’ll give you the proper idea. I’m so busy to debug your code right now. Although if you’re unable to solve it after downloading my code & again viewing it let me know hope I can help you if I got any time.

  6. Hi Tanim, Thank you very much for your tutorial. I have got now a good understanding related to pdo and crud class. I’ve downloaded the files. But buttons are not working. Can you please tell me what’s the problem, since i dont have a good background in it.

      • Yes its working in chrome. So it means i need to add class btn to anchors to make it work or add button inside anchor. Well anyway i will find it. But Thank you very much again for your response and a great tutorial. I love to see your other tutorials.

  7. hey thanks dear for ur support nd response.
    now plz tell me… how to show table detail from database?
    tell me the query.. nd the location where i will add that query
    Thanks

    and my new working code is
    customers.php

    name = $name;
    $this-> desc = $desc;
    $this -> con = mysql_connect(“localhost”, “root”, “”);
    $this -> db = mysql_select_db(‘cust’);
    }

    public function insertData($desc_tbl)
    {

    $sql = ” insert into $desc_tbl values (NULL,'”.$this->name.”‘,'”.$this->desc.”‘);”;
    $res = mysql_query($sql);
    mysql_query($res);
    echo “Record Inserted”;

    }

    public function deleteData($id)
    {
    $sql = ” delete from desc_tbl”;
    $res = mysql_query($sql);
    mysql_query($res);
    echo “Record Deleted”;
    }

    public function fetchData($id,$name,$desc)
    {
    $sql = ” select * from desc_tbl”;
    $res = mysql_query($sql);
    mysql_query($res[$id]);
    echo “Data is”;

    }

    public function updateData($desc_tbl,$name,$desc,$id)
    {
    $sql = ” update desc_tbl set name=’$name’ , desc=’$desc'”;
    $res = mysql_query($sql);
    mysql_query($res);
    echo “Record Updated”;
    }

    }
    ?>

    test.php

    insertData(“desc_tbl”);

    //$c =new Customers($name,$desc,$id);
    //echo $c->deleteData(“desc_tbl”);

    ?>

    • In the below code you can see that you have to just create a new file called showdata.php & there you’re gonna fetch the data by this function:

      public function showData($table){

      $sql=”SELECT * FROM $table”;
      $q = $this->conn->query($sql) or die(“failed!”);

      while($r = $q->fetch(PDO::FETCH_ASSOC)){
      $data[]=$r;
      }
      return $data;
      }

      in here let’s see we’re calling the method to give all the data in the table so that we can show it in the view page. that’s it. First of all autoload the class file then create an object then call the method that’s it.

      in your code you’ve named your method fetchdata() so just call that method to do the magic …

      thank you for reading my article so much ..

  8. Great tutorial. Everything i very clear.
    One thing that I have never seen before, could you please explain how the “echo @ <<<show" works. ?

    /Christian

  9. Nice code for small app. Still getting into the code.

    I found in showData() method it is giving error for $data array when it returns null or 0.

    So adding $data = array(); before while loop might rid off the error.

  10. Hi,
    I have some problems with your code on server. If i run this on my laptop it’s working but if i put on a webserver i get an warning like this:
    Warning: Cannot modify header information – headers already sent by (output started at /home3/sobe01/public_html/crock/edit.php:13) in /home3/sobe01/public_html/crock/classes/Redirect.php on line 16

    Please help me.
    Thanks.

  11. To download the full source code click here.
    I have done that.

    But if I open “show.php” I get to see two link to Home and Insert.
    And below ALL The Data,

    and below that the table with the data and the links to Edit and Delete,
    and below that the link to Insert New Dates.

    But I can click until I have a ounce weighing, or in English “when pigs fly”,
    but nothing happens all links are dead

    I see in show. php on line 13 this: include_once($class.”.php”);

    I understand that there is something include, but not what because php only is no scrips.

  12. I have looking again on other site’s at pdo but safe is that not as it stands here, easy to work with though. but you can fill in what you want everything is accepted.
    So nothing is done against SQL injection

  13. Pingback: DPsense Survey Tool – DPsense L.C.

Leave a Reply

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