In this tutorial, we will create a Complete PDO CRUD using PDO. This code has several functionalities that can manipulate data through database servers using the PDO query. The system uses a PDO query to (Create, Read, Update, Delete) a specific data to a database server with high data protection to avoid data injection tools.
We will be using PDO as a query scripting it an acronym for PHP Data Objects. It is a lean, clean, and consistent way to access databases. This means developers can write portable code much more accessible.
Table of Contents
Getting started:
First, you have to download & install XAMPP or any local server that runs PHP scripts. Here’s the link for the XAMPP server https://www.apachefriends.org/index.html.
And, this is the link for the bootstrap that I used for the layout design https://getbootstrap.com/.
Creating Database
Open your database web server, then create a database name in it db_pdo_crud; after that, click Import, then locate the database file inside the folder of the application then click ok.
Creating the database connection
Open your any kind of text editor(notepadd++, etc..). Then just copy/paste the code below then name it conn.php.
<?php
$db_username = 'root';
$db_password = '';
$conn = new PDO( 'mysql:host=localhost;dbname=db_pdo_crud', $db_username, $db_password );
if(!$conn){
die("Fatal Error: Connection Failed!");
}
?>Creating The Interface
This is where we will create a simple form for our application. To create the forms simply copy and write it into you text editor, then save it as index.php.
<!DOCTYPE html>
<html lang="en">
<head>
<link rel="stylesheet" type="text/css" href="css/bootstrap.css">
<meta charset="UTF-8" name="viewport" content="width=device-width, initial-scale=1"/>
</head>
<body>
<nav class="navbar navbar-default">
<div class="container-fluid">
<a href="https://sourcecodester.com" class="navbar-brand">Sourcecodester</a>
</div>
</nav>
<div class="col-md-3"></div>
<div class="col-md-6 well">
<h3 class="text-primary">PHP - Complete PDO CRUD</h3>
<hr style="border-top:1px dotted #ccc;" />
<div class="col-md-3">
<form method="POST" action="add.php">
<div class="form-group">
<label>Firstname</label>
<input class="form-control" type="text" name="firstname"/>
</div>
<div class="form-group">
<label>Lastname</label>
<input class="form-control" type="text" name="lastname"/>
</div>
<div class="form-group">
<label>Address</label>
<input class="form-control" type="text" name="address"/>
</div>
<div class="form-group">
<button class="btn btn-primary form-control" type="submit" name="save">Save</button>
</div>
</form>
</div>
<div class="col-md-9">
<table class="table table-bordered">
<thead class="alert-info">
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Address</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
require 'conn.php';
$sql = $conn->prepare("SELECT * FROM `user`");
$sql->execute();
while($fetch = $sql->fetch()){
?>
<tr>
<td><?php echo $fetch['firstname']?></td>
<td><?php echo $fetch['lastname']?></td>
<td><?php echo $fetch['address']?></td>
<td><button class="btn btn-warning btn-sm" data-toggle="modal" data-target="#update<?php echo $fetch['user_id']?>">Edit</button> | <a class="btn btn-danger btn-sm" href="delete.php?id=<?php echo $fetch['user_id']?>">Delete</a></td>
</tr>
<div class="modal fade" id="update<?php echo $fetch['user_id']?>" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<form method="POST" action="update.php">
<div class="modal-header">
<h3 class="modal-title">Update User</h3>
</div>
<div class="modal-body">
<div class="col-md-2"></div>
<div class="col-md-8">
<div class="form-group">
<label>Firstname</label>
<input class="form-control" type="text" value="<?php echo $fetch['firstname']?>" name="firstname"/>
<input type="hidden" value="<?php echo $fetch['user_id']?>" name="user_id"/>
</div>
<div class="form-group">
<label>Lastname</label>
<input class="form-control" type="text" value="<?php echo $fetch['lastname']?>" name="lastname"/>
</div>
<div class="form-group">
<label>Address</label>
<input class="form-control" type="text" value="<?php echo $fetch['address']?>" name="address"/>
</div>
<div class="form-group">
<button class="btn btn-warning form-control" type="submit" name="update">Update</button>
</div>
</div>
</div>
<br style="clear:both;"/>
<div class="modal-footer">
<button class="btn btn-danger" data-dismiss="modal">Close</button>
</div>
</form>
</div>
</div>
</div>
<?php
}
?>
</tbody>
</table>
</div>
</div>
Creating the Main Function
This code contains the main function of the application. This code can manipulate a data when the button is clicked. To do that write these block of codes inside the text editor and save it as shown below.
add.php
<?php
require_once 'conn.php';
if(ISSET($_POST['save'])){
try{
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address = $_POST['address'];
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO `user` (`firstname`, `lastname`, `address`) VALUES ('$firstname', '$lastname', '$address')";
$conn->exec($sql);
}catch(PDOException $e){
echo $e->getMessage();
}
$conn = null;
header('location:index.php');
}
?>update.php
<?php
require_once 'conn.php';
if(ISSET($_POST['update'])){
try{
$user_id = $_POST['user_id'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address = $_POST['address'];
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "UPDATE `user`SET `firstname` = '$firstname', `lastname` = '$lastname', `address` = '$address' WHERE `user_id` = '$user_id'";
$conn->exec($sql);
}catch(PDOException $e){
echo $e->getMessage();
}
$conn = null;
header('location:index.php');
}
?>delete.php
<?php
if(ISSET($_GET['id'])){
require_once 'conn.php';
$id = $_GET['id'];
$sql = $conn->prepare("DELETE from `user` WHERE `user_id`='$id'");
$sql->execute();
header('location:index.php');
}
?>There you have it we successfully created a Complete PDO CRUD using PDO. I hope that this simple tutorial help you to what you are looking for. For more updates and tutorials just kindly visit this site. Enjoy Coding!!!
Is there a way to have a textarea instead of traditonnal input to update the database?