PHP MySQL CRUD Application
In this tutorial you'll learn how to build a CRUD application with PHP and MySQL.
What is CRUD
CRUD is an acronym for Create, Read, Update, and Delete. CRUD operations are basic data manipulation for database. We've already learned how to perform create (i.e. insert), read (i.e. select), update and delete operations in previous chapters. In this tutorial we'll create a simple PHP application to perform all these operations on a MySQL database table at one place.
Well, let's start by creating the table which we'll use in all of our example.
Creating the Database Table
Execute the following SQL query to create a table named employees inside your MySQL database. We will use this table for all of our future operations.
CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
address VARCHAR(255) NOT NULL,
salary INT(10) NOT NULL
);
Creating the Config File
After creating the table, we need create a PHP script in order to connect to the MySQL database server. Let's create a file named "config.php" and put the following code inside it.
We'll later include this config file in other pages using the PHP require_once() function.
Example
Procedural
Object Oriented
PDO
Download
<?php
/* Database credentials. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'demo');
/* Attempt to connect to MySQL database */
$link = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>
<?php
/* Database credentials. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'demo');
/* Attempt to connect to MySQL database */
$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
?>
<?php
/* Database credentials. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'demo');
/* Attempt to connect to MySQL database */
try{
$pdo = new PDO("mysql:host=" . DB_SERVER . ";dbname=" . DB_NAME, DB_USERNAME, DB_PASSWORD);
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
?>
If you've downloaded the Object Oriented or PDO code examples using the download button, please remove the text "-oo-format" or "-pdo-format" from file names before testing the code.
Note: Replace the credentials according to your MySQL server setting before testing this code, for example, replace the database name 'demo' with your own database name, replace username 'root' with your own database username, specify database password if there's any.
Creating the Landing Page
First we will create a landing page for our CRUD application that contains a data grid showing the records from the employees database table. It also has action icons for each record displayed in the grid, that you may choose to view its details, update it, or delete it.
We'll also add a create button on the top of the data grid that can be used for creating new records in the employees table. Create a file named "index.php" and put the following code in it:
Example
Procedural
Object Oriented
PDO
Download
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Dashboard</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
table tr td:last-child{
width: 120px;
}
</style>
<script>
$(document).ready(function(){
$('[data-toggle="tooltip"]').tooltip();
});
</script>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="mt-5 mb-3 clearfix">
<h2 class="pull-left">Employees Details</h2>
<a href="create.php" class="btn btn-success pull-right"><i class="fa fa-plus"></i> Add New Employee</a>
</div>
<?php
// Include config file
require_once "config.php";
// Attempt select query execution
$sql = "SELECT * FROM employees";
if($result = mysqli_query($link, $sql)){
if(mysqli_num_rows($result) > 0){
echo '<table class="table table-bordered table-striped">';
echo "<thead>";
echo "<tr>";
echo "<th>#</th>";
echo "<th>Name</th>";
echo "<th>Address</th>";
echo "<th>Salary</th>";
echo "<th>Action</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";
while($row = mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['address'] . "</td>";
echo "<td>" . $row['salary'] . "</td>";
echo "<td>";
echo '<a href="read.php?id='. $row['id'] .'" class="mr-3" title="View Record" data-toggle="tooltip"><span class="fa fa-eye"></span></a>';
echo '<a href="update.php?id='. $row['id'] .'" class="mr-3" title="Update Record" data-toggle="tooltip"><span class="fa fa-pencil"></span></a>';
echo '<a href="delete.php?id='. $row['id'] .'" title="Delete Record" data-toggle="tooltip"><span class="fa fa-trash"></span></a>';
echo "</td>";
echo "</tr>";
}
echo "</tbody>";
echo "</table>";
// Free result set
mysqli_free_result($result);
} else{
echo '<div class="alert alert-danger"><em>No records were found.</em></div>';
}
} else{
echo "Oops! Something went wrong. Please try again later.";
}
// Close connection
mysqli_close($link);
?>
</div>
</div>
</div>
</div>
</body>
</html>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Dashboard</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
table tr td:last-child{
width: 120px;
}
</style>
<script>
$(document).ready(function(){
$('[data-toggle="tooltip"]').tooltip();
});
</script>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="mt-5 mb-3 clearfix">
<h2 class="pull-left">Employees Details</h2>
<a href="create.php" class="btn btn-success pull-right"><i class="fa fa-plus"></i> Add New Employee</a>
</div>
<?php
// Include config file
require_once "config.php";
// Attempt select query execution
$sql = "SELECT * FROM employees";
if($result = $mysqli->query($sql)){
if($result->num_rows > 0){
echo '<table class="table table-bordered table-striped">';
echo "<thead>";
echo "<tr>";
echo "<th>#</th>";
echo "<th>Name</th>";
echo "<th>Address</th>";
echo "<th>Salary</th>";
echo "<th>Action</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";
while($row = $result->fetch_array()){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['address'] . "</td>";
echo "<td>" . $row['salary'] . "</td>";
echo "<td>";
echo '<a href="read.php?id='. $row['id'] .'" class="mr-3" title="View Record" data-toggle="tooltip"><span class="fa fa-eye"></span></a>';
echo '<a href="update.php?id='. $row['id'] .'" class="mr-3" title="Update Record" data-toggle="tooltip"><span class="fa fa-pencil"></span></a>';
echo '<a href="delete.php?id='. $row['id'] .'" title="Delete Record" data-toggle="tooltip"><span class="fa fa-trash"></span></a>';
echo "</td>";
echo "</tr>";
}
echo "</tbody>";
echo "</table>";
// Free result set
$result->free();
} else{
echo '<div class="alert alert-danger"><em>No records were found.</em></div>';
}
} else{
echo "Oops! Something went wrong. Please try again later.";
}
// Close connection
$mysqli->close();
?>
</div>
</div>
</div>
</div>
</body>
</html>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Dashboard</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
table tr td:last-child{
width: 120px;
}
</style>
<script>
$(document).ready(function(){
$('[data-toggle="tooltip"]').tooltip();
});
</script>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="mt-5 mb-3 clearfix">
<h2 class="pull-left">Employees Details</h2>
<a href="create.php" class="btn btn-success pull-right"><i class="fa fa-plus"></i> Add New Employee</a>
</div>
<?php
// Include config file
require_once "config.php";
// Attempt select query execution
$sql = "SELECT * FROM employees";
if($result = $pdo->query($sql)){
if($result->rowCount() > 0){
echo '<table class="table table-bordered table-striped">';
echo "<thead>";
echo "<tr>";
echo "<th>#</th>";
echo "<th>Name</th>";
echo "<th>Address</th>";
echo "<th>Salary</th>";
echo "<th>Action</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";
while($row = $result->fetch()){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['address'] . "</td>";
echo "<td>" . $row['salary'] . "</td>";
echo "<td>";
echo '<a href="read.php?id='. $row['id'] .'" class="mr-3" title="View Record" data-toggle="tooltip"><span class="fa fa-eye"></span></a>';
echo '<a href="update.php?id='. $row['id'] .'" class="mr-3" title="Update Record" data-toggle="tooltip"><span class="fa fa-pencil"></span></a>';
echo '<a href="delete.php?id='. $row['id'] .'" title="Delete Record" data-toggle="tooltip"><span class="fa fa-trash"></span></a>';
echo "</td>";
echo "</tr>";
}
echo "</tbody>";
echo "</table>";
// Free result set
unset($result);
} else{
echo '<div class="alert alert-danger"><em>No records were found.</em></div>';
}
} else{
echo "Oops! Something went wrong. Please try again later.";
}
// Close connection
unset($pdo);
?>
</div>
</div>
</div>
</div>
</body>
</html>
Once employees table is populated with some records the landing page i.e. the CRUD data grid may look something like the picture shown below:
Tip: We've used the Bootstrap framework to make this CRUD application layout quickly and beautifully. Bootstrap is the most popular and powerful front-end framework for faster and easier responsive web development. Please, checkout the Bootstrap tutorial section to learn more about this framework.
Creating the Create Page
In this section we'll build the Create functionality of our CRUD application.
Let's create a file named "create.php" and put the following code inside it. It will generate a web form that can be used to insert records in the employees table.
Example
Procedural
Object Oriented
PDO
Download
<?php
// Include config file
require_once "config.php";
// Define variables and initialize with empty values
$name = $address = $salary = "";
$name_err = $address_err = $salary_err = "";
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
// Validate name
$input_name = trim($_POST["name"]);
if(empty($input_name)){
$name_err = "Please enter a name.";
} elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))){
$name_err = "Please enter a valid name.";
} else{
$name = $input_name;
}
// Validate address
$input_address = trim($_POST["address"]);
if(empty($input_address)){
$address_err = "Please enter an address.";
} else{
$address = $input_address;
}
// Validate salary
$input_salary = trim($_POST["salary"]);
if(empty($input_salary)){
$salary_err = "Please enter the salary amount.";
} elseif(!ctype_digit($input_salary)){
$salary_err = "Please enter a positive integer value.";
} else{
$salary = $input_salary;
}
// Check input errors before inserting in database
if(empty($name_err) && empty($address_err) && empty($salary_err)){
// Prepare an insert statement
$sql = "INSERT INTO employees (name, address, salary) VALUES (?, ?, ?)";
if($stmt = mysqli_prepare($link, $sql)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "sss", $param_name, $param_address, $param_salary);
// Set parameters
$param_name = $name;
$param_address = $address;
$param_salary = $salary;
// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
// Records created successfully. Redirect to landing page
header("location: index.php");
exit();
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
mysqli_stmt_close($stmt);
}
// Close connection
mysqli_close($link);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Create Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<h2 class="mt-5">Create Record</h2>
<p>Please fill this form and submit to add employee record to the database.</p>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<div class="form-group">
<label>Name</label>
<input type="text" name="name" class="form-control <?php echo (!empty($name_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $name; ?>">
<span class="invalid-feedback"><?php echo $name_err;?></span>
</div>
<div class="form-group">
<label>Address</label>
<textarea name="address" class="form-control <?php echo (!empty($address_err)) ? 'is-invalid' : ''; ?>"><?php echo $address; ?></textarea>
<span class="invalid-feedback"><?php echo $address_err;?></span>
</div>
<div class="form-group">
<label>Salary</label>
<input type="text" name="salary" class="form-control <?php echo (!empty($salary_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $salary; ?>">
<span class="invalid-feedback"><?php echo $salary_err;?></span>
</div>
<input type="submit" class="btn btn-primary" value="Submit">
<a href="index.php" class="btn btn-secondary ml-2">Cancel</a>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
<?php
// Include config file
require_once "config.php";
// Define variables and initialize with empty values
$name = $address = $salary = "";
$name_err = $address_err = $salary_err = "";
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
// Validate name
$input_name = trim($_POST["name"]);
if(empty($input_name)){
$name_err = "Please enter a name.";
} elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))){
$name_err = "Please enter a valid name.";
} else{
$name = $input_name;
}
// Validate address
$input_address = trim($_POST["address"]);
if(empty($input_address)){
$address_err = "Please enter an address.";
} else{
$address = $input_address;
}
// Validate salary
$input_salary = trim($_POST["salary"]);
if(empty($input_salary)){
$salary_err = "Please enter the salary amount.";
} elseif(!ctype_digit($input_salary)){
$salary_err = "Please enter a positive integer value.";
} else{
$salary = $input_salary;
}
// Check input errors before inserting in database
if(empty($name_err) && empty($address_err) && empty($salary_err)){
// Prepare an insert statement
$sql = "INSERT INTO employees (name, address, salary) VALUES (?, ?, ?)";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("sss", $param_name, $param_address, $param_salary);
// Set parameters
$param_name = $name;
$param_address = $address;
$param_salary = $salary;
// Attempt to execute the prepared statement
if($stmt->execute()){
// Records created successfully. Redirect to landing page
header("location: index.php");
exit();
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
$stmt->close();
}
// Close connection
$mysqli->close();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Create Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<h2 class="mt-5">Create Record</h2>
<p>Please fill this form and submit to add employee record to the database.</p>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<div class="form-group">
<label>Name</label>
<input type="text" name="name" class="form-control <?php echo (!empty($name_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $name; ?>">
<span class="invalid-feedback"><?php echo $name_err;?></span>
</div>
<div class="form-group">
<label>Address</label>
<textarea name="address" class="form-control <?php echo (!empty($address_err)) ? 'is-invalid' : ''; ?>"><?php echo $address; ?></textarea>
<span class="invalid-feedback"><?php echo $address_err;?></span>
</div>
<div class="form-group">
<label>Salary</label>
<input type="text" name="salary" class="form-control <?php echo (!empty($salary_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $salary; ?>">
<span class="invalid-feedback"><?php echo $salary_err;?></span>
</div>
<input type="submit" class="btn btn-primary" value="Submit">
<a href="index.php" class="btn btn-secondary ml-2">Cancel</a>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
<?php
// Include config file
require_once "config.php";
// Define variables and initialize with empty values
$name = $address = $salary = "";
$name_err = $address_err = $salary_err = "";
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
// Validate name
$input_name = trim($_POST["name"]);
if(empty($input_name)){
$name_err = "Please enter a name.";
} elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))){
$name_err = "Please enter a valid name.";
} else{
$name = $input_name;
}
// Validate address
$input_address = trim($_POST["address"]);
if(empty($input_address)){
$address_err = "Please enter an address.";
} else{
$address = $input_address;
}
// Validate salary
$input_salary = trim($_POST["salary"]);
if(empty($input_salary)){
$salary_err = "Please enter the salary amount.";
} elseif(!ctype_digit($input_salary)){
$salary_err = "Please enter a positive integer value.";
} else{
$salary = $input_salary;
}
// Check input errors before inserting in database
if(empty($name_err) && empty($address_err) && empty($salary_err)){
// Prepare an insert statement
$sql = "INSERT INTO employees (name, address, salary) VALUES (:name, :address, :salary)";
if($stmt = $pdo->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bindParam(":name", $param_name);
$stmt->bindParam(":address", $param_address);
$stmt->bindParam(":salary", $param_salary);
// Set parameters
$param_name = $name;
$param_address = $address;
$param_salary = $salary;
// Attempt to execute the prepared statement
if($stmt->execute()){
// Records created successfully. Redirect to landing page
header("location: index.php");
exit();
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
unset($stmt);
}
// Close connection
unset($pdo);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Create Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<h2 class="mt-5">Create Record</h2>
<p>Please fill this form and submit to add employee record to the database.</p>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<div class="form-group">
<label>Name</label>
<input type="text" name="name" class="form-control <?php echo (!empty($name_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $name; ?>">
<span class="invalid-feedback"><?php echo $name_err;?></span>
</div>
<div class="form-group">
<label>Address</label>
<textarea name="address" class="form-control <?php echo (!empty($address_err)) ? 'is-invalid' : ''; ?>"><?php echo $address; ?></textarea>
<span class="invalid-feedback"><?php echo $address_err;?></span>
</div>
<div class="form-group">
<label>Salary</label>
<input type="text" name="salary" class="form-control <?php echo (!empty($salary_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $salary; ?>">
<span class="invalid-feedback"><?php echo $salary_err;?></span>
</div>
<input type="submit" class="btn btn-primary" value="Submit">
<a href="index.php" class="btn btn-secondary ml-2">Cancel</a>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
The same "create.php" file will display the HTML form and process the submitted form data. It will also perform basic validation on user inputs (line no-11 to 37) before saving the data.
Creating the Read Page
Now it's time to build the Read functionality of our CRUD application.
Let's create a file named "read.php" and put the following code inside it. It will simply retrieve the records from the employees table based the id attribute of the employee.
Example
Procedural
Object Oriented
PDO
Download
<?php
// Check existence of id parameter before processing further
if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){
// Include config file
require_once "config.php";
// Prepare a select statement
$sql = "SELECT * FROM employees WHERE id = ?";
if($stmt = mysqli_prepare($link, $sql)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "i", $param_id);
// Set parameters
$param_id = trim($_GET["id"]);
// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
$result = mysqli_stmt_get_result($stmt);
if(mysqli_num_rows($result) == 1){
/* Fetch result row as an associative array. Since the result set
contains only one row, we don't need to use while loop */
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
// Retrieve individual field value
$name = $row["name"];
$address = $row["address"];
$salary = $row["salary"];
} else{
// URL doesn't contain valid id parameter. Redirect to error page
header("location: error.php");
exit();
}
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
mysqli_stmt_close($stmt);
// Close connection
mysqli_close($link);
} else{
// URL doesn't contain id parameter. Redirect to error page
header("location: error.php");
exit();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>View Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<h1 class="mt-5 mb-3">View Record</h1>
<div class="form-group">
<label>Name</label>
<p><b><?php echo $row["name"]; ?></b></p>
</div>
<div class="form-group">
<label>Address</label>
<p><b><?php echo $row["address"]; ?></b></p>
</div>
<div class="form-group">
<label>Salary</label>
<p><b><?php echo $row["salary"]; ?></b></p>
</div>
<p><a href="index.php" class="btn btn-primary">Back</a></p>
</div>
</div>
</div>
</div>
</body>
</html>
<?php
// Check existence of id parameter before processing further
if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){
// Include config file
require_once "config.php";
// Prepare a select statement
$sql = "SELECT * FROM employees WHERE id = ?";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("i", $param_id);
// Set parameters
$param_id = trim($_GET["id"]);
// Attempt to execute the prepared statement
if($stmt->execute()){
$result = $stmt->get_result();
if($result->num_rows == 1){
/* Fetch result row as an associative array. Since the result set
contains only one row, we don't need to use while loop */
$row = $result->fetch_array(MYSQLI_ASSOC);
// Retrieve individual field value
$name = $row["name"];
$address = $row["address"];
$salary = $row["salary"];
} else{
// URL doesn't contain valid id parameter. Redirect to error page
header("location: error.php");
exit();
}
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
$stmt->close();
// Close connection
$mysqli->close();
} else{
// URL doesn't contain id parameter. Redirect to error page
header("location: error.php");
exit();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>View Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<h1 class="mt-5 mb-3">View Record</h1>
<div class="form-group">
<label>Name</label>
<p><b><?php echo $row["name"]; ?></b></p>
</div>
<div class="form-group">
<label>Address</label>
<p><b><?php echo $row["address"]; ?></b></p>
</div>
<div class="form-group">
<label>Salary</label>
<p><b><?php echo $row["salary"]; ?></b></p>
</div>
<p><a href="index.php" class="btn btn-primary">Back</a></p>
</div>
</div>
</div>
</div>
</body>
</html>
<?php
// Check existence of id parameter before processing further
if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){
// Include config file
require_once "config.php";
// Prepare a select statement
$sql = "SELECT * FROM employees WHERE id = :id";
if($stmt = $pdo->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bindParam(":id", $param_id);
// Set parameters
$param_id = trim($_GET["id"]);
// Attempt to execute the prepared statement
if($stmt->execute()){
if($stmt->rowCount() == 1){
/* Fetch result row as an associative array. Since the result set
contains only one row, we don't need to use while loop */
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// Retrieve individual field value
$name = $row["name"];
$address = $row["address"];
$salary = $row["salary"];
} else{
// URL doesn't contain valid id parameter. Redirect to error page
header("location: error.php");
exit();
}
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
unset($stmt);
// Close connection
unset($pdo);
} else{
// URL doesn't contain id parameter. Redirect to error page
header("location: error.php");
exit();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>View Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<h1 class="mt-5 mb-3">View Record</h1>
<div class="form-group">
<label>Name</label>
<p><b><?php echo $row["name"]; ?></b></p>
</div>
<div class="form-group">
<label>Address</label>
<p><b><?php echo $row["address"]; ?></b></p>
</div>
<div class="form-group">
<label>Salary</label>
<p><b><?php echo $row["salary"]; ?></b></p>
</div>
<p><a href="index.php" class="btn btn-primary">Back</a></p>
</div>
</div>
</div>
</div>
</body>
</html>
Creating the Update Page
Similarly, we can build the Update functionality of our CRUD application.
Let's create a file named "update.php" and put the following code inside it. It will update the existing records in the employees table based the id attribute of the employee.
Example
Procedural
Object Oriented
PDO
Download
<?php
// Include config file
require_once "config.php";
// Define variables and initialize with empty values
$name = $address = $salary = "";
$name_err = $address_err = $salary_err = "";
// Processing form data when form is submitted
if(isset($_POST["id"]) && !empty($_POST["id"])){
// Get hidden input value
$id = $_POST["id"];
// Validate name
$input_name = trim($_POST["name"]);
if(empty($input_name)){
$name_err = "Please enter a name.";
} elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))){
$name_err = "Please enter a valid name.";
} else{
$name = $input_name;
}
// Validate address address
$input_address = trim($_POST["address"]);
if(empty($input_address)){
$address_err = "Please enter an address.";
} else{
$address = $input_address;
}
// Validate salary
$input_salary = trim($_POST["salary"]);
if(empty($input_salary)){
$salary_err = "Please enter the salary amount.";
} elseif(!ctype_digit($input_salary)){
$salary_err = "Please enter a positive integer value.";
} else{
$salary = $input_salary;
}
// Check input errors before inserting in database
if(empty($name_err) && empty($address_err) && empty($salary_err)){
// Prepare an update statement
$sql = "UPDATE employees SET name=?, address=?, salary=? WHERE id=?";
if($stmt = mysqli_prepare($link, $sql)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "sssi", $param_name, $param_address, $param_salary, $param_id);
// Set parameters
$param_name = $name;
$param_address = $address;
$param_salary = $salary;
$param_id = $id;
// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
// Records updated successfully. Redirect to landing page
header("location: index.php");
exit();
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
mysqli_stmt_close($stmt);
}
// Close connection
mysqli_close($link);
} else{
// Check existence of id parameter before processing further
if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){
// Get URL parameter
$id = trim($_GET["id"]);
// Prepare a select statement
$sql = "SELECT * FROM employees WHERE id = ?";
if($stmt = mysqli_prepare($link, $sql)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "i", $param_id);
// Set parameters
$param_id = $id;
// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
$result = mysqli_stmt_get_result($stmt);
if(mysqli_num_rows($result) == 1){
/* Fetch result row as an associative array. Since the result set
contains only one row, we don't need to use while loop */
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
// Retrieve individual field value
$name = $row["name"];
$address = $row["address"];
$salary = $row["salary"];
} else{
// URL doesn't contain valid id. Redirect to error page
header("location: error.php");
exit();
}
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
mysqli_stmt_close($stmt);
// Close connection
mysqli_close($link);
} else{
// URL doesn't contain id parameter. Redirect to error page
header("location: error.php");
exit();
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Update Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<h2 class="mt-5">Update Record</h2>
<p>Please edit the input values and submit to update the employee record.</p>
<form action="<?php echo htmlspecialchars(basename($_SERVER['REQUEST_URI'])); ?>" method="post">
<div class="form-group">
<label>Name</label>
<input type="text" name="name" class="form-control <?php echo (!empty($name_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $name; ?>">
<span class="invalid-feedback"><?php echo $name_err;?></span>
</div>
<div class="form-group">
<label>Address</label>
<textarea name="address" class="form-control <?php echo (!empty($address_err)) ? 'is-invalid' : ''; ?>"><?php echo $address; ?></textarea>
<span class="invalid-feedback"><?php echo $address_err;?></span>
</div>
<div class="form-group">
<label>Salary</label>
<input type="text" name="salary" class="form-control <?php echo (!empty($salary_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $salary; ?>">
<span class="invalid-feedback"><?php echo $salary_err;?></span>
</div>
<input type="hidden" name="id" value="<?php echo $id; ?>"/>
<input type="submit" class="btn btn-primary" value="Submit">
<a href="index.php" class="btn btn-secondary ml-2">Cancel</a>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
<?php
// Include config file
require_once "config.php";
// Define variables and initialize with empty values
$name = $address = $salary = "";
$name_err = $address_err = $salary_err = "";
// Processing form data when form is submitted
if(isset($_POST["id"]) && !empty($_POST["id"])){
// Get hidden input value
$id = $_POST["id"];
// Validate name
$input_name = trim($_POST["name"]);
if(empty($input_name)){
$name_err = "Please enter a name.";
} elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))){
$name_err = "Please enter a valid name.";
} else{
$name = $input_name;
}
// Validate address address
$input_address = trim($_POST["address"]);
if(empty($input_address)){
$address_err = "Please enter an address.";
} else{
$address = $input_address;
}
// Validate salary
$input_salary = trim($_POST["salary"]);
if(empty($input_salary)){
$salary_err = "Please enter the salary amount.";
} elseif(!ctype_digit($input_salary)){
$salary_err = "Please enter a positive integer value.";
} else{
$salary = $input_salary;
}
// Check input errors before inserting in database
if(empty($name_err) && empty($address_err) && empty($salary_err)){
// Prepare an update statement
$sql = "UPDATE employees SET name=?, address=?, salary=? WHERE id=?";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("sssi", $param_name, $param_address, $param_salary, $param_id);
// Set parameters
$param_name = $name;
$param_address = $address;
$param_salary = $salary;
$param_id = $id;
// Attempt to execute the prepared statement
if($stmt->execute()){
// Records updated successfully. Redirect to landing page
header("location: index.php");
exit();
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
$stmt->close();
}
// Close connection
$mysqli->close();
} else{
// Check existence of id parameter before processing further
if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){
// Get URL parameter
$id = trim($_GET["id"]);
// Prepare a select statement
$sql = "SELECT * FROM employees WHERE id = ?";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("i", $param_id);
// Set parameters
$param_id = $id;
// Attempt to execute the prepared statement
if($stmt->execute()){
$result = $stmt->get_result();
if($result->num_rows == 1){
/* Fetch result row as an associative array. Since the result set
contains only one row, we don't need to use while loop */
$row = $result->fetch_array(MYSQLI_ASSOC);
// Retrieve individual field value
$name = $row["name"];
$address = $row["address"];
$salary = $row["salary"];
} else{
// URL doesn't contain valid id. Redirect to error page
header("location: error.php");
exit();
}
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
$stmt->close();
// Close connection
$mysqli->close();
} else{
// URL doesn't contain id parameter. Redirect to error page
header("location: error.php");
exit();
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Update Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<h2 class="mt-5">Update Record</h2>
<p>Please edit the input values and submit to update the employee record.</p>
<form action="<?php echo htmlspecialchars(basename($_SERVER['REQUEST_URI'])); ?>" method="post">
<div class="form-group">
<label>Name</label>
<input type="text" name="name" class="form-control <?php echo (!empty($name_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $name; ?>">
<span class="invalid-feedback"><?php echo $name_err;?></span>
</div>
<div class="form-group">
<label>Address</label>
<textarea name="address" class="form-control <?php echo (!empty($address_err)) ? 'is-invalid' : ''; ?>"><?php echo $address; ?></textarea>
<span class="invalid-feedback"><?php echo $address_err;?></span>
</div>
<div class="form-group">
<label>Salary</label>
<input type="text" name="salary" class="form-control <?php echo (!empty($salary_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $salary; ?>">
<span class="invalid-feedback"><?php echo $salary_err;?></span>
</div>
<input type="hidden" name="id" value="<?php echo $id; ?>"/>
<input type="submit" class="btn btn-primary" value="Submit">
<a href="index.php" class="btn btn-secondary ml-2">Cancel</a>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
<?php
// Include config file
require_once "config.php";
// Define variables and initialize with empty values
$name = $address = $salary = "";
$name_err = $address_err = $salary_err = "";
// Processing form data when form is submitted
if(isset($_POST["id"]) && !empty($_POST["id"])){
// Get hidden input value
$id = $_POST["id"];
// Validate name
$input_name = trim($_POST["name"]);
if(empty($input_name)){
$name_err = "Please enter a name.";
} elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))){
$name_err = "Please enter a valid name.";
} else{
$name = $input_name;
}
// Validate address address
$input_address = trim($_POST["address"]);
if(empty($input_address)){
$address_err = "Please enter an address.";
} else{
$address = $input_address;
}
// Validate salary
$input_salary = trim($_POST["salary"]);
if(empty($input_salary)){
$salary_err = "Please enter the salary amount.";
} elseif(!ctype_digit($input_salary)){
$salary_err = "Please enter a positive integer value.";
} else{
$salary = $input_salary;
}
// Check input errors before inserting in database
if(empty($name_err) && empty($address_err) && empty($salary_err)){
// Prepare an update statement
$sql = "UPDATE employees SET name=:name, address=:address, salary=:salary WHERE id=:id";
if($stmt = $pdo->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bindParam(":name", $param_name);
$stmt->bindParam(":address", $param_address);
$stmt->bindParam(":salary", $param_salary);
$stmt->bindParam(":id", $param_id);
// Set parameters
$param_name = $name;
$param_address = $address;
$param_salary = $salary;
$param_id = $id;
// Attempt to execute the prepared statement
if($stmt->execute()){
// Records updated successfully. Redirect to landing page
header("location: index.php");
exit();
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
unset($stmt);
}
// Close connection
unset($pdo);
} else{
// Check existence of id parameter before processing further
if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){
// Get URL parameter
$id = trim($_GET["id"]);
// Prepare a select statement
$sql = "SELECT * FROM employees WHERE id = :id";
if($stmt = $pdo->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bindParam(":id", $param_id);
// Set parameters
$param_id = $id;
// Attempt to execute the prepared statement
if($stmt->execute()){
if($stmt->rowCount() == 1){
/* Fetch result row as an associative array. Since the result set
contains only one row, we don't need to use while loop */
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// Retrieve individual field value
$name = $row["name"];
$address = $row["address"];
$salary = $row["salary"];
} else{
// URL doesn't contain valid id. Redirect to error page
header("location: error.php");
exit();
}
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
unset($stmt);
// Close connection
unset($pdo);
} else{
// URL doesn't contain id parameter. Redirect to error page
header("location: error.php");
exit();
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Update Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<h2 class="mt-5">Update Record</h2>
<p>Please edit the input values and submit to update the employee record.</p>
<form action="<?php echo htmlspecialchars(basename($_SERVER['REQUEST_URI'])); ?>" method="post">
<div class="form-group">
<label>Name</label>
<input type="text" name="name" class="form-control <?php echo (!empty($name_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $name; ?>">
<span class="invalid-feedback"><?php echo $name_err;?></span>
</div>
<div class="form-group">
<label>Address</label>
<textarea name="address" class="form-control <?php echo (!empty($address_err)) ? 'is-invalid' : ''; ?>"><?php echo $address; ?></textarea>
<span class="invalid-feedback"><?php echo $address_err;?></span>
</div>
<div class="form-group">
<label>Salary</label>
<input type="text" name="salary" class="form-control <?php echo (!empty($salary_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $salary; ?>">
<span class="invalid-feedback"><?php echo $salary_err;?></span>
</div>
<input type="hidden" name="id" value="<?php echo $id; ?>"/>
<input type="submit" class="btn btn-primary" value="Submit">
<a href="index.php" class="btn btn-secondary ml-2">Cancel</a>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
Creating the Delete Page
Finally, we will build the Delete functionality of our CRUD application.
Let's create a file named "delete.php" and put the following code inside it. It will delete the existing records from the employees table based the id attribute of the employee.
Example
Procedural
Object Oriented
PDO
Download
<?php
// Process delete operation after confirmation
if(isset($_POST["id"]) && !empty($_POST["id"])){
// Include config file
require_once "config.php";
// Prepare a delete statement
$sql = "DELETE FROM employees WHERE id = ?";
if($stmt = mysqli_prepare($link, $sql)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "i", $param_id);
// Set parameters
$param_id = trim($_POST["id"]);
// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
// Records deleted successfully. Redirect to landing page
header("location: index.php");
exit();
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
mysqli_stmt_close($stmt);
// Close connection
mysqli_close($link);
} else{
// Check existence of id parameter
if(empty(trim($_GET["id"]))){
// URL doesn't contain id parameter. Redirect to error page
header("location: error.php");
exit();
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Delete Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<h2 class="mt-5 mb-3">Delete Record</h2>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<div class="alert alert-danger">
<input type="hidden" name="id" value="<?php echo trim($_GET["id"]); ?>"/>
<p>Are you sure you want to delete this employee record?</p>
<p>
<input type="submit" value="Yes" class="btn btn-danger">
<a href="index.php" class="btn btn-secondary">No</a>
</p>
</div>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
<?php
// Process delete operation after confirmation
if(isset($_POST["id"]) && !empty($_POST["id"])){
// Include config file
require_once "config.php";
// Prepare a delete statement
$sql = "DELETE FROM employees WHERE id = ?";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("i", $param_id);
// Set parameters
$param_id = trim($_POST["id"]);
// Attempt to execute the prepared statement
if($stmt->execute()){
// Records deleted successfully. Redirect to landing page
header("location: index.php");
exit();
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
$stmt->close();
// Close connection
$mysqli->close();
} else{
// Check existence of id parameter
if(empty(trim($_GET["id"]))){
// URL doesn't contain id parameter. Redirect to error page
header("location: error.php");
exit();
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Delete Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<h2 class="mt-5 mb-3">Delete Record</h2>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<div class="alert alert-danger">
<input type="hidden" name="id" value="<?php echo trim($_GET["id"]); ?>"/>
<p>Are you sure you want to delete this employee record?</p>
<p>
<input type="submit" value="Yes" class="btn btn-danger">
<a href="index.php" class="btn btn-secondary ml-2">No</a>
</p>
</div>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
<?php
// Process delete operation after confirmation
if(isset($_POST["id"]) && !empty($_POST["id"])){
// Include config file
require_once "config.php";
// Prepare a delete statement
$sql = "DELETE FROM employees WHERE id = :id";
if($stmt = $pdo->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bindParam(":id", $param_id);
// Set parameters
$param_id = trim($_POST["id"]);
// Attempt to execute the prepared statement
if($stmt->execute()){
// Records deleted successfully. Redirect to landing page
header("location: index.php");
exit();
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
// Close statement
unset($stmt);
// Close connection
unset($pdo);
} else{
// Check existence of id parameter
if(empty(trim($_GET["id"]))){
// URL doesn't contain id parameter. Redirect to error page
header("location: error.php");
exit();
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Delete Record</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<h2 class="mt-5 mb-3">Delete Record</h2>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<div class="alert alert-danger">
<input type="hidden" name="id" value="<?php echo trim($_GET["id"]); ?>"/>
<p>Are you sure you want to delete this employee record?</p>
<p>
<input type="submit" value="Yes" class="btn btn-danger">
<a href="index.php" class="btn btn-secondary ml-2">No</a>
</p>
</div>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
Creating the Error Page
At the end, let's create one more file "error.php". This page will be displayed if request is invalid i.e. if id parameter is missing from the URL query string or it is not valid.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Error</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
.wrapper{
width: 600px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<h2 class="mt-5 mb-3">Invalid Request</h2>
<div class="alert alert-danger">Sorry, you've made an invalid request. Please <a href="index.php" class="alert-link">go back</a> and try again.</div>
</div>
</div>
</div>
</div>
</body>
</html>
After a long journey finally we've finished our CRUD application with PHP and MySQL. We recommend you to check out PHP & MySQL database tutorial section from the beginning, if you haven't already covered, for a better understanding of each and every part of this tutorial.
