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

by Sam  


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.



Leave a Reply

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


  1. blog says:

    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. Celesta says:

    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. Sam says:

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

  3. amazon.co.uk says:

    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. Sam says:

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

  4. cà phê hạt rang sẵn says:

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

  5. 안전한놀이터 says:

    Greetings! I’ve been reading your website for a while now and finally got
    the courage to go ahead and give you a shout
    out from New Caney Texas! Just wanted to mention keep up the excellent job!

  6. Anonymous says:

    Very nice blog post. I absolutely love this site. Keep writing!

  7. Hemorrhoids Management says:

    I think this is among the most vital info for me.
    And i am glad reading your article. But want to remark on some
    general things, The site style is wonderful, the articles is
    really excellent : D. Good job, cheers

Need of a Website?

Maybe you're planning to build a landing page for your website. You might also have a plan to create a web application. I can offer you with an affordable but with good quality services. Don't hesitate to submit your specifications by clicking the button below.

Get Quote