crud operations in php is an acronym for Select, Insert, Update, and Delete. CRUD operations are simply data manipulation for MySQL database. I have a already learned how to perform create (i.e. insert data), read (i.e. select data), edit with update as wel as soft and hard delete operations in previous best Articles. In this best post I will make a easy to use PHP based web application to perform all these insert, update and delete operations on a MySQL database table at single platform.
Good Luck, let’s start by making the Database table which I will use in all of our best example step by step.
Creating the Database Table
Execute the following SQL query to create a table named products inside your MySQL database. We will use this table for all of our future operations.
CREATE TABLE products (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
description VARCHAR(255) NOT NULL,
price INT(10) NOT NULL
);
PHP MySQL CRUD Application
crud operations in php
- PHP Jquery Ajax CRUD Example Tutorial From Scratch
- Reusable PHP CRUD DATABASE FUNCTIONS
- PHP OOPS – MySQL connecting Insert update delete select Fetch Data
Creating the Config File
<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'pakainfo');
$link = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>
Creating the Landing Page
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Dashboard</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.js"></script>
<style type="text/css">
.wrapper{
width: 650px;
margin: 0 auto;
}
.page-header h2{
margin-top: 0;
}
table tr td:last-child a{
margin-right: 15px;
}
</style>
<script type="text/javascript">
$(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="page-header clearfix">
<h2 class="pull-left">products Details</h2>
<a href="create.php" class="btn btn-success pull-right">Add New product</a>
</div>
<?php
// Include config file
require_once "config.php";
// Attempt select query execution
$sql = "SELECT * FROM products";
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>description</th>";
echo "<th>price</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['description'] . "</td>";
echo "<td>" . $row['price'] . "</td>";
echo "<td>";
echo "<a href='read.php?id=". $row['id'] ."' title='View Record' data-toggle='tooltip'><span class='glyphicon glyphicon-eye-open'></span></a>";
echo "<a href='update.php?id=". $row['id'] ."' title='Update Record' data-toggle='tooltip'><span class='glyphicon glyphicon-pencil'></span></a>";
echo "<a href='delete.php?id=". $row['id'] ."' title='Delete Record' data-toggle='tooltip'><span class='glyphicon glyphicon-trash'></span></a>";
echo "</td>";
echo "</tr>";
}
echo "</tbody>";
echo "</table>";
// Free result set
mysqli_free_result($result);
} else{
echo "<p class='lead'><em>No records were found.</em></p>";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
// Close connection
mysqli_close($link);
?>
</div>
</div>
</div>
</div>
</body>
</html>
Creating the Create Page
<?php
require_once "config.php";
$name = $description = $price = "";
$name_err = $description_err = $price_err = "";
if($_SERVER["REQUEST_METHOD"] == "POST"){
$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;
}
$input_description = trim($_POST["description"]);
if(empty($input_description)){
$description_err = "Please enter an description.";
} else{
$description = $input_description;
}
$input_price = trim($_POST["price"]);
if(empty($input_price)){
$price_err = "Please enter the price amount.";
} elseif(!ctype_digit($input_price)){
$price_err = "Please enter a positive integer value.";
} else{
$price = $input_price;
}
if(empty($name_err) && empty($description_err) && empty($price_err)){
$sql = "INSERT INTO products (name, description, price) VALUES (?, ?, ?)";
if($stmt = mysqli_prepare($link, $sql)){
mysqli_stmt_bind_param($stmt, "sss", $param_name, $param_description, $param_price);
$param_name = $name;
$param_description = $description;
$param_price = $price;
if(mysqli_stmt_execute($stmt)){
header("location: index.php");
exit();
} else{
echo "Something went wrong. Please try again later.";
}
}
mysqli_stmt_close($stmt);
}
mysqli_close($link);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Create Record</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
<style type="text/css">
.wrapper{
width: 500px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="page-header">
<h2>Create Record</h2>
</div>
<p>Please fill this form and submit to add product record to the database.</p>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<div class="form-group <?php echo (!empty($name_err)) ? 'has-error' : ''; ?>">
<label>Name</label>
<input type="text" name="name" class="form-control" value="<?php echo $name; ?>">
<span class="help-block"><?php echo $name_err;?></span>
</div>
<div class="form-group <?php echo (!empty($description_err)) ? 'has-error' : ''; ?>">
<label>description</label>
<textarea name="description" class="form-control"><?php echo $description; ?></textarea>
<span class="help-block"><?php echo $description_err;?></span>
</div>
<div class="form-group <?php echo (!empty($price_err)) ? 'has-error' : ''; ?>">
<label>price</label>
<input type="text" name="price" class="form-control" value="<?php echo $price; ?>">
<span class="help-block"><?php echo $price_err;?></span>
</div>
<input type="submit" class="btn btn-primary" value="Submit">
<a href="index.php" class="btn btn-default">Cancel</a>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
Creating the Read Page
<?php
if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){
require_once "config.php";
$sql = "SELECT * FROM products WHERE id = ?";
if($stmt = mysqli_prepare($link, $sql)){
mysqli_stmt_bind_param($stmt, "i", $param_id);
$param_id = trim($_GET["id"]);
if(mysqli_stmt_execute($stmt)){
$result = mysqli_stmt_get_result($stmt);
if(mysqli_num_rows($result) == 1){
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
$name = $row["name"];
$description = $row["description"];
$price = $row["price"];
} else{
header("location: error.php");
exit();
}
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
mysqli_stmt_close($stmt);
mysqli_close($link);
} else{
header("location: error.php");
exit();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>View Record</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
<style type="text/css">
.wrapper{
width: 500px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="page-header">
<h1>View Record</h1>
</div>
<div class="form-group">
<label>Name</label>
<p class="form-control-static"><?php echo $row["name"]; ?></p>
</div>
<div class="form-group">
<label>description</label>
<p class="form-control-static"><?php echo $row["description"]; ?></p>
</div>
<div class="form-group">
<label>price</label>
<p class="form-control-static"><?php echo $row["price"]; ?></p>
</div>
<p><a href="index.php" class="btn btn-primary">Back</a></p>
</div>
</div>
</div>
</div>
</body>
</html>
Creating the Update Page
<?php
require_once "config.php";
$name = $description = $price = "";
$name_err = $description_err = $price_err = "";
if(isset($_POST["id"]) && !empty($_POST["id"])){
$id = $_POST["id"];
$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;
}
$input_description = trim($_POST["description"]);
if(empty($input_description)){
$description_err = "Please enter an description.";
} else{
$description = $input_description;
}
$input_price = trim($_POST["price"]);
if(empty($input_price)){
$price_err = "Please enter the price amount.";
} elseif(!ctype_digit($input_price)){
$price_err = "Please enter a positive integer value.";
} else{
$price = $input_price;
}
if(empty($name_err) && empty($description_err) && empty($price_err)){
$sql = "UPDATE products SET name=?, description=?, price=? WHERE id=?";
if($stmt = mysqli_prepare($link, $sql)){
mysqli_stmt_bind_param($stmt, "sssi", $param_name, $param_description, $param_price, $param_id);
$param_name = $name;
$param_description = $description;
$param_price = $price;
$param_id = $id;
if(mysqli_stmt_execute($stmt)){
header("location: index.php");
exit();
} else{
echo "Something went wrong. Please try again later.";
}
}
mysqli_stmt_close($stmt);
}
mysqli_close($link);
} else{
if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){
$id = trim($_GET["id"]);
$sql = "SELECT * FROM products WHERE id = ?";
if($stmt = mysqli_prepare($link, $sql)){
mysqli_stmt_bind_param($stmt, "i", $param_id);
$param_id = $id;
if(mysqli_stmt_execute($stmt)){
$result = mysqli_stmt_get_result($stmt);
if(mysqli_num_rows($result) == 1){
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
$name = $row["name"];
$description = $row["description"];
$price = $row["price"];
} else{
header("location: error.php");
exit();
}
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
mysqli_stmt_close($stmt);
mysqli_close($link);
} else{
header("location: error.php");
exit();
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Update Record</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
<style type="text/css">
.wrapper{
width: 500px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="page-header">
<h2>Update Record</h2>
</div>
<p>Please edit the input values and submit to update the record.</p>
<form action="<?php echo htmlspecialchars(basename($_SERVER['REQUEST_URI'])); ?>" method="post">
<div class="form-group <?php echo (!empty($name_err)) ? 'has-error' : ''; ?>">
<label>Name</label>
<input type="text" name="name" class="form-control" value="<?php echo $name; ?>">
<span class="help-block"><?php echo $name_err;?></span>
</div>
<div class="form-group <?php echo (!empty($description_err)) ? 'has-error' : ''; ?>">
<label>description</label>
<textarea name="description" class="form-control"><?php echo $description; ?></textarea>
<span class="help-block"><?php echo $description_err;?></span>
</div>
<div class="form-group <?php echo (!empty($price_err)) ? 'has-error' : ''; ?>">
<label>price</label>
<input type="text" name="price" class="form-control" value="<?php echo $price; ?>">
<span class="help-block"><?php echo $price_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-default">Cancel</a>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
Creating the Delete Page
<?php
if(isset($_POST["id"]) && !empty($_POST["id"])){
require_once "config.php";
$sql = "DELETE FROM products WHERE id = ?";
if($stmt = mysqli_prepare($link, $sql)){
mysqli_stmt_bind_param($stmt, "i", $param_id);
$param_id = trim($_POST["id"]);
if(mysqli_stmt_execute($stmt)){
header("location: index.php");
exit();
} else{
echo "Oops! Something went wrong. Please try again later.";
}
}
mysqli_stmt_close($stmt);
mysqli_close($link);
} else{
if(empty(trim($_GET["id"]))){
header("location: error.php");
exit();
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>View Record</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
<style type="text/css">
.wrapper{
width: 500px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="page-header">
<h1>Delete Record</h1>
</div>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<div class="alert alert-danger fade in">
<input type="hidden" name="id" value="<?php echo trim($_GET["id"]); ?>"/>
<p>Are you sure you want to delete this record?</p><br>
<p>
<input type="submit" value="Yes" class="btn btn-danger">
<a href="index.php" class="btn btn-default">No</a>
</p>
</div>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
Creating the Error Page
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Error</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
<style type="text/css">
.wrapper{
width: 750px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="page-header">
<h1>Invalid Request</h1>
</div>
<div class="alert alert-danger fade in">
<p>Sorry, you've made an invalid request. Please <a href="index.php" class="alert-link">go back</a> and try again.</p>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
Summary
You can also read about AngularJS, ASP.NET, VueJs, PHP.
I hope you get an idea about crud operations in php.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.