simple PDO crud operation in php

on


In this tutorial, we will learn PHP CRUD(create, read, update, delete.) operation using PDO extension. 
 
We will learn the Simple CRUD operation with a live demo.

      

Step 1: 

First, create a database like ‘pdodb.’
After creating the database, then create a table like pdotable.
Add values like name, email and password to the table.
structure for table

 

CREATE TABLE IF NOT EXISTS `pdotable` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `name` varchar(110) NOT NULL,
  `email` varchar(110) NOT NULL,
  `password` varchar(110) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

          

Step 2:

Create a database connection file to connect the database.

 Config.php

<?php
define('db_host','localhost');
define('db_user','root');
define('db_data','pdodb');
define('db_pass','');

try{
$conn=new PDO('mysql:host='.db_host.';dbname='.db_data,db_user,db_pass);
}
catch(PDOException $e){
exit('error'.$e->getMessage());
}
?>

 

Step 3:
Now, we will insert Record into the database. So we will need to create a file insert.php file.

 

Insert.php
Inserting data into database using PDO

         PDO Code for inserting data into database 

<?php
include'config.php';

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

$name=$_POST['txtname'];
$email=$_POST['txtemail'];
$password=$_POST['txtpass'];

if(empty($name) || empty($email) || empty($password)){

if(empty($name)){
echo"<span style='color:red'>Name is empty</span><br>";
}

if(empty($email)){
echo "<span style='color:red'>Email is empty</span><br>";
}

if(empty($password)){
echo "<span style='color:red'>Password is empty</span><br>";
}

}else{
$q="insert into pdotable(name,email,password)values(:n,:e,:p)";
$query=$conn->prepare($q);
$query->bindParam(':n',$name,PDO::PARAM_STR);
$query->bindParam(':e',$email,PDO::PARAM_STR);
$query->bindParam(':p',$password,PDO::PARAM_STR);
$query->execute();
}
}
echo"<a href='select.php'>Display data</a>";
?>

<form action=""method="POST">
Name:<br>
<input type="text"name="txtname"><br>

Email:<br>
<input type="email"name="txtemail"><br>

Password:<br>
<input type="password"name="txtpass"><br>

<input type="submit"name="submit"value="insert"><br>
</form>

         Live demo 



                          
Step 4:
In this step, we will fetch data from the database and display it on a page.
We will fetch all records whatever you insert into the database.

 

Select.php
<table border='0' cellspacing='10px' cellpadding='10px'>
<thead>
<th>Name</th>
<th>Email</th>
<th>Password</th>
<th>action</th>
</thead>
<tbody>
<?php
include'config.php';
$q="select *from pdotable";
$query=$conn->prepare($q);
$query->execute();

$results=$query->fetchAll(PDO::FETCH_OBJ);
if($query->rowCount()>0){
foreach($results as $result){
?>

<tr>
<td><?php echo htmlentities($result->name);?></td>
<td><?php echo htmlentities($result->email);?></td>
<td><?php echo htmlentities($result->password);?></td>
<td><a href="edit.php?id=<?php echo htmlentities($result->id);?>">edit</a>|
<td><a href="delete.php?id=<?php echo htmlentities($result->id);?>">delete</a></td>
</tr>
<?php
}
}
echo"<a href='insert.php'>New record insert</a>";
?>
</tbody>
</table>

 

    Live demo 



 
Step 5:
Now, we will edit data from the database.
We will fetch all records and edit then change into the database.

You may also like 



In this, first, we'll select data from a database and create an edit file in the same page.
We will edit data by ID wise.
 
edit.php
PDO edit data

          PDO Code for editing data into database 

<?php 
include'config.php';

if(isset($_POST['edit'])){

$id=$_GET['id'];
$name=$_POST['txtname'];
$email=$_POST['txtemail'];
$password=$_POST['txtpass'];

if(empty($name) || empty($email) || empty($password)){

if(empty($name)){
echo"<span style='color:red'>Name is empty</span><br>";
}

if(empty($email)){
echo "<span style='color:red'>Email is empty</span><br>";
}

if(empty($password)){
echo "<span style='color:red'>Password is empty</span><br>";
}
}else{
$q="update pdotable set name=:n,email=:e,password=:p where id=:id";
$query=$conn->prepare($q);
$query->bindParam(':n',$name,PDO::PARAM_STR);
$query->bindParam(':e',$email,PDO::PARAM_STR);
$query->bindParam(':p',$password,PDO::PARAM_STR);
$query->bindParam('id',$id,PDO::PARAM_STR);

$query->execute();

echo"<script>alert('record update sucessfuly')</script>";
echo"<script>window.location.href='select.php'</script>";
}
}
?>

<?php 
$id=$_GET['id'];
$q="select *from pdotable where id=:id";
$query=$conn->prepare($q);
$query->bindParam(':id',$id,PDO::PARAM_STR);
$query->execute();
$results=$query->fetchAll(PDO::FETCH_OBJ);
if($query->rowCount()>0){
foreach($results as $result){
?>

<form action=""method="POST">
<td>Name</td>
<td><input type="text"name="txtname"value="<?php echo htmlentities($result->name);?>"></td><br>

<td>Email</td>
<input type="email"name="txtemail"value="<?php echo htmlentities($result->email);?>"></td><br>

<td>Password</td>
<input type="password"name="txtpass"value="<?php echo htmlentities($result->password);?>"></td><br>

<?php
}
}
?>
<td><input type="submit"name="edit"value="update"></td>
</form>

 

    Live demo 



Step 6:
After edit the data same page, we'll create a delete link.
When users click on the delete option, then the record will be deleted.
We will create a deleted page to delete records and without a redirect, we will include the header function to redirect the same page without going to the deleted a page.
The following code shows the delete.php file.
 
Delete.php

          PDO Code for deleting data into database 

<?php 
include'config.php';

if(isset($_POST['edit'])){

$id=$_GET['id'];
$name=$_POST['txtname'];
$email=$_POST['txtemail'];
$password=$_POST['txtpass'];

if(empty($name) || empty($email) || empty($password)){

if(empty($name)){
echo"<span style='color:red'>Name is empty</span><br>";
}

if(empty($email)){
echo "<span style='color:red'>Email is empty</span><br>";
}

if(empty($password)){
echo "<span style='color:red'>Password is empty</span><br>";
}
}else{
$q="update pdotable set name=:n,email=:e,password=:p where id=:id";
$query=$conn->prepare($q);
$query->bindParam(':n',$name,PDO::PARAM_STR);
$query->bindParam(':e',$email,PDO::PARAM_STR);
$query->bindParam(':p',$password,PDO::PARAM_STR);
$query->bindParam('id',$id,PDO::PARAM_STR);

$query->execute();

echo"<script>alert('record update sucessfuly')</script>";
echo"<script>window.location.href='select.php'</script>";
}
}
?>

<?php 
$id=$_GET['id'];
$q="select *from pdotable where id=:id";
$query=$conn->prepare($q);
$query->bindParam(':id',$id,PDO::PARAM_STR);
$query->execute();
$results=$query->fetchAll(PDO::FETCH_OBJ);
if($query->rowCount()>0){
foreach($results as $result){
?>

<form action=""method="POST">
<td>Name</td>
<td><input type="text"name="txtname"value="<?php echo htmlentities($result->name);?>"></td><br>

<td>Email</td>
<input type="email"name="txtemail"value="<?php echo htmlentities($result->email);?>"></td><br>

<td>Password</td>
<input type="password"name="txtpass"value="<?php echo htmlentities($result->password);?>"></td><br>

<?php
}
}
?>
<td><input type="submit"name="edit"value="update"></td>
</form>
 

    Live demo 








 

Location: India

Comments

Post a Comment