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.
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', 'arif@gmail.com', '01722589654', 'Dhaka,Bangladesh'), (2, 'Iqbal Hossain', 'iqbal@gmail.com', '01755847591', 'Thakur Gao, Bangladesh'), (8, 'Monzur ', 'mon@zur.com', '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> <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"> </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"> </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 .
An enthusiastic of work with over 8+ years of experience. I’m extensively experienced in WordPress, Codeigniter, Laravel.
##Visit my GitHub page (https://github.com/ashawkat)
##Visit my blogging page where I spread out my knowledge around all other people (http://w3programmers.com/author/tanim)
If you want to hire me, you can contact me via email or skype.
Skype: ashawkat89
Email: contact@adnanshawkat.me
On line 55 of the “show.php” file you have,
Edit
but there is no “edit.php” file. You meant to put this, right?
Edit
very nice tuts dude
Previous comment didn’t work..
What I meant to put was “td>Edit</a"
Instead of "edit.php?id=$id" it should be "update.php?id=$id"
I’m extreamely sorry & yes it should be update.php
No worries! I was just trying to recreate your example but I was getting some errors.
It’s a great tutorial!
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 ..
thank you for the source file and the knowledge, so help me much..
#(y)
Thank’s Again Boz 😀
Excellent
Thanks for your efforts
thanks for your nice comment !! will update many things shortly !!
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 !!
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. 🙂
looking forward for your quest on cms .. 🙂 will defenitely help you in any need ..
it gives some errors… what i can do???
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 ..
how to call other functions?…. what is the correct code?
plz tell me
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 ..
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.
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.
It is made of bootstrap 2 but firefox and ie didn’t support the button check the files on chrome..
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.
thnkuuuu so much dear for your help and response.
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 ..
Very Nice Tutorial
Thanks ..It was much needed… Keep sharing the codes…!!
Show error show.php 0n line 48 when no record is there
See my comment for fix http://www.w3programmers.com/crud-with-pdo-and-oop-php/#comment-24046
Great tutorial. Everything i very clear.
One thing that I have never seen before, could you please explain how the “echo @ <<<show" works. ?
/Christian
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.thanks for the suggestion though.
it is great code …Thank you so much
Warning: extract() expects parameter 1 to be array, boolean given
if get empty
undefined index
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.
function __autoload($class){
include_once($class.”.php”);
}
Where is class.php
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.
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
I genuinely prize your work, Geat post.
Pingback: DPsense Survey Tool – DPsense L.C.
Bro please provide link of this tutorial source code ..
previos one is corrupted…or link broken.
nice work,can you code evrything dynamically just like this
about pagination,login,fileupload,one time password,
Assalamu Alaykum Brother, I tried to have the Source Codes zip, but there wasn’t any…..where can I be able to get it?
As Salam o Alykum,
Nice simple, clean and tidy code.
But where is the download link, i cannot find in githu either?
Can you teach on weekend?
Please let me know?
Salman
zaibtabs@gmail.com
Yes Very Nice tutorial Boss Go On….