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";
}

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");
 }

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");
 }

}

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 click here.

Adnan Shawkat

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]