pdo in php

Create, Retrieve, Update, Delete (CRUD) in PDO with Multiple Delete

This article will show you how to execute CRUD(Create, Retrieve, Update, Delete) in PHP using PDO. The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. When you use PHP for web development, you can use either mysqli or PDO, but, this page will focus on PDO.

Pre-requisite

Before you proceed below, make sure that you have already:

– Knowledge in MySQL database
– Basic PHP and HTML

Let’s get started


First,create a database name pdodb. Next, copy the sql query below in order to create persontbl.


CREATE TABLE `persontbl` (
`pid` int(12) NOT NULL,
`firstname` varchar(150) NOT NULL,
`lastname` varchar(150) NOT NULL,
`age` int(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `persontbl`
MODIFY `pid` int(12) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
COMMIT;

Next, we’ll create files where we are going to save our php scripts.

config.php


<?php
$host = 'localhost';
$dbname = 'pdodb';
$username = 'root';
$password = '';
$conn = "mysql:host=$host;dbname=$dbname;";
try {
$pdo = new PDO($conn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo $e->getMessage();
}

In our config.php, we’re going to set connections to our database. Make sure to configure the credentials based on your setting.

create.php


<?php
require_once 'config.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>CRUD with PDO</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" >
</head>
<body>
<div class="container">
<?php
if(isset($_POST['submit'])){
//do your own validation here
$sql = "INSERT INTO persontbl(firstname, lastname, age) VALUES(:firstname, :lastname, :age)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':firstname', $_POST['firstname'], PDO::PARAM_STR);
$stmt->bindParam(':lastname', $_POST['lastname'], PDO::PARAM_STR);
$stmt->bindParam(':age', $_POST['age'], PDO::PARAM_STR);
if( ! $stmt->execute()){
echo 'Please check errors';
}else{
echo "<div class='alert alert-success'>Sucessfully added . <a href='index.php'>Go to List</a></div>";
}
}
?>
<h1>Create new</h1><hr>
<div class="row">
<form action="" method="POST" class="col-md-5">
<label for="firstname">Firstname</label>
<input type="text" name="firstname" class="form-control"> <br>
<label for="lastname">Lastname</label>
<input type="text" name="lastname" class="form-control"> <br>
<label for="age">Age</label><br>
<input type="text" name="age" size="10"> <br><br>
<input type="submit" value="Submit" class="btn btn-primary" name="submit">
</form>
</div>
</div>
</body>
</html>

index.php

We’re going to display all data inserted by the user in our index.php.


<?php
require_once 'config.php';
$query = $pdo->prepare("SELECT * FROM persontbl");
$query->execute();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>CRUD with PDO</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" >
</head>
<body>
<div class="container">
<h1>List</h1>
<a href="addnew.php" class="btn btn-primary pull-right">Add New</a> <br><br>
<form action='delete.php' method='POST'>
<?php
echo "<table class='table table-striped'>";
echo "<tr>
<th>#</th>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th></th>
</tr>";
while ($row = $query->fetch()) {
$id = $row['pid'];
echo "<tr>";
echo "<td><input type='checkbox' name='persid[]' value='{$id}'></td>";
echo "<td>{$id}</td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['age'] . "</td>";
echo "<td><a class='btn btn-warning btn-xs' href='edit.php?id={$id}'>Edit</a></td>";
echo "</tr>";
}
echo "</table>";
?>
<input type="submit" value="Delete Seletected" class="btn btn-danger" name="delete">
</form>
</div>
</body>
</html>

delete.php


<?php
require_once 'config.php';
if( count($_POST['persid']) > 0 ){
foreach($_POST['persid'] as $persid){
$where = ['id' => $persid];
$pdo->prepare("DELETE FROM persontbl WHERE pid=:id")->execute($where);
}
}
header('Location: index.php');

Since we want to execute a multiple delete, then we need to loop through the id selected by the user. Take note that we set persid as an array: name='persid[]' in index.php.

edit.php


<?php
require_once 'config.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>EDIT with PDO</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" >
</head>
<body>
<div class="container">
<?php
$id = isset($_GET['id']) ? (int) $_GET['id'] : 0;
if(isset($_POST['update'])){
//do your own validation here
$sql = "UPDATE persontbl SET firstname=:firstname, lastname=:lastname, age=:age
WHERE pid=:id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':firstname', $_POST['firstname'], PDO::PARAM_STR);
$stmt->bindParam(':lastname', $_POST['lastname'], PDO::PARAM_STR);
$stmt->bindParam(':age', $_POST['age'], PDO::PARAM_STR);
$stmt->bindParam(':id', $id);
if( ! $stmt->execute()){
echo 'Please check errors';
}else{
echo "<div class='alert alert-success'>Updated Successfully . <a href='index.php'>Go to List</a></div>";
}
}
$sql = "SELECT * FROM persontbl WHERE pid=:id";
$query = $pdo->prepare($sql);
$query->execute(array(':id' => $id));
$row = $query->fetch();
?>
<h1>EDIT</h1><hr>
<div class="row">
<form action="" method="POST" class="col-md-5">
<label for="firstname">Firstname</label>
<input type="text" name="firstname" value="<?php echo $row['firstname']; ?>" class="form-control"> <br>
<label for="lastname">Lastname</label>
<input type="text" name="lastname" value="<?php echo $row['lastname']; ?>" class="form-control"> <br>
<label for="age">Age</label><br>
<input type="text" name="age" value="<?php echo $row['age']; ?>" size="10"> <br><br>
<input type="submit" value="Update" class="btn btn-primary" name="update">
</form>
</div>
</div>
</body>
</html>

Conclusion

Take note that I did not put additional validation in submission of the form. If you question and suggestions, please comment below.

6 Comments

  1. Right now it sounds like Drupal is the best blogging platform
    out there right now. (from what I’ve read) Is that what you are using on your blog?

  2. I was recommended this web site by my cousin. I’m not sure whether this
    post is written by him as nobody else know such detailed about my difficulty.
    You’re wonderful! Thanks!

    1. Thank you Celesta. You may subscribe for our future tutorials.

  3. Heya i am for the first time here. I found this board and I find It truly useful & it helped me out a
    lot. I hope to give something back and help others like you helped me.

    1. Hey there, thank you for appreciating my post. Checkout for more tutorials. 😀

  4. Fine way of explaining, and pleasant post to get information concerning my presentation topic, which i am going to deliver in school.

Leave a Reply

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