Throughout php crud operation using mysqli tutorial, we will show you step by step how to build a simple PHP MySQL CRUD Operation (Create, Read, Update, Delete) functionality and also how to validate add & update form data on server-side in php mysql crud app.
After getting better knowledge in php now its time to making CRUD Operation in PHP with mysqli. Here will demonstrate simple PHP CRUD application with Bootstrap styles from scratch and step by step including record listing, record inserting, record updating and record deleting.
CRUD Meaning: CRUD is an acronym that comes from the world of computer programming and refers to the four functions that are considered necessary to implement a persistent storage application: create, read, update and delete.
This php crud operation step by step tutorial will implement a simple user crud operation app with validation. Using this crud app, you can learn how to insert, read, update and delete data from database in php.
PHP MySQL CRUD Example
Use to Follow the following steps to build a basic crud operation using php and mysql;
- Step 1: Create Database Table
- Step 2: Create Config File
- Step 3: Create Landing Page
- Step 4: Add Create Form Page
- Step 5: Create Edit Page
- Step 6: Create Read File
- Step 7: Create Delete File
Create Database Table
Copy the blow SQL query to create a user table inside your MySQL database.
CREATE TABLE users (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL,
phone_number VARCHAR(15) NOT NULL,
address VARCHAR(255) NOT NULL
);Create Config File
After creating the table, we need to create a connection for connecting to the MySQL database server. So, create a file named “config.php” and put the following code inside it.
<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'php_crud');
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>We will use this connection in other pages using the PHP require_once() function.
Create Landing Page
Now, we will create a landing page for CRUP operation which is show the user records as grid view. We alse update the edit, view and delete icons on it.
Creating landing page we add a file named “index.php” in our crud folder and put the following code in it.
<!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>
<style type="text/css">
.wrapper {
width: 1200px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<h2 class="text-center">PHP CRUD Tutorial Example with <a href="https://codingdriver.com/">Coding Driver</a></h2>
<div class="col-md-12">
<div class="page-header clearfix">
<h2 class="pull-left">Users</h2>
<a href="create.php" class="btn btn-success pull-right">Add New User</a>
</div>
<?php
// Include config file
require_once "config.php";
// select all users
$data = "SELECT * FROM users";
if($users = mysqli_query($conn, $data)){
if(mysqli_num_rows($users) > 0){
echo "<table class='table table-bordered table-striped'>
<thead>
<tr>
<th>#</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Phone Number</th>
<th>Address</th>
<th>Action</th>
</tr>
</thead>
<tbody>";
while($user = mysqli_fetch_array($users)) {
echo "<tr>
<td>" . $user['id'] . "</td>
<td>" . $user['first_name'] . "</td>
<td>" . $user['last_name'] . "</td>
<td>" . $user['email'] . "</td>
<td>" . $user['phone_number'] . "</td>
<td>" . $user['address'] . "</td>
<td>
<a href='read.php?id=". $user['id'] ."' title='View User' data-toggle='tooltip'><span class='glyphicon glyphicon-eye-open'></span></a>
<a href='edit.php?id=". $user['id'] ."' title='Edit User' data-toggle='tooltip'><span class='glyphicon glyphicon-pencil'></span></a>
<a href='delete.php?id=". $user['id'] ."' title='Delete User' data-toggle='tooltip'><span class='glyphicon glyphicon-trash'></span></a>
</td>
</tr>";
}
echo "</tbody>
</table>";
mysqli_free_result($users);
} else{
echo "<p class='lead'><em>No records found.</em></p>";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
}
// Close connection
mysqli_close($conn);
?>
</div>
</div>
</div>
</div>
</body>
</html>You can insert some test data directly in your database table and after your can see your user listing same as below picture.
Add Create Page
In this step we we’ll build CRUD application Create functionality. So, create a new file named as “create.php” and put the following code inside it.
<?php
require_once "config.php";
$first_name = $last_name = $email = $phone_number = $address = "";
$first_name_error = $last_name_error = $email_error = $phone_number_error = $address_error = "";
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$firstName = trim($_POST["first_name"]);
if (empty($firstName)) {
$first_name_error = "First Name is required.";
} elseif (!filter_var($firstName, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))) {
$first_name_error = "First Name is invalid.";
} else {
$firstName = $firstName;
}
$lastName = trim($_POST["last_name"]);
if (empty($lastName)) {
$last_name_error = "Last Name is required.";
} elseif (!filter_var($firstName, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))) {
$last_name_error = "Last Name is invalid.";
} else {
$lastName = $lastName;
}
$email = trim($_POST["email"]);
if (empty($email)) {
$email_error = "Email is required.";
} elseif (!filter_var($firstName, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))) {
$email_error = "Please enter a valid email.";
} else {
$email = $email;
}
$phoneNumber = trim($_POST["phone_number"]);
if(empty($phoneNumber)){
$phone_number_error = "Phone Number is required.";
} else {
$phoneNumber = $phoneNumber;
}
$address = trim($_POST["address"]);
if(empty($address)){
$address_error = "Address is required.";
} else {
$address = $address;
}
if (empty($first_name_error_err) && empty($last_name_error) && empty($email_error) && empty($phone_number_error) && empty($address_error) ) {
$sql = "INSERT INTO `users` (`first_name`, `last_name`, `email`, `phone_number`, `address`) VALUES ('$firstName', '$lastName', '$email', '$phoneNumber', '$address')";
if (mysqli_query($conn, $sql)) {
header("location: index.php");
} else {
echo "Something went wrong. Please try again later.";
}
}
mysqli_close($conn);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Create User</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
<style type="text/css">
.wrapper {
width: 1200px;
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 User</h2>
</div>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<div class="form-group <?php echo (!empty($first_name_error)) ? 'has-error' : ''; ?>">
<label>First Name</label>
<input type="text" name="first_name" class="form-control" value="">
<span class="help-block"><?php echo $first_name_error;?></span>
</div>
<div class="form-group <?php echo (!empty($last_name_error)) ? 'has-error' : ''; ?>">
<label>Last Name</label>
<input type="text" name="last_name" class="form-control" value="">
<span class="help-block"><?php echo $last_name_error;?></span>
</div>
<div class="form-group <?php echo (!empty($email_error)) ? 'has-error' : ''; ?>">
<label>Email</label>
<input type="email" name="email" class="form-control" value="">
<span class="help-block"><?php echo $email_error;?></span>
</div>
<div class="form-group <?php echo (!empty($phone_number_error)) ? 'has-error' : ''; ?>">
<label>Phone Number</label>
<input type="number" name="phone_number" class="form-control" value="">
<span class="help-block"><?php echo $phone_number_error;?></span>
</div>
<div class="form-group <?php echo (!empty($address_error)) ? 'has-error' : ''; ?>">
<label>Address</label>
<textarea name="address" class="form-control"></textarea>
<span class="help-block"><?php echo $address_error;?></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>The “create.php” file will display the HTML form and process the submitted form data with validations.
Create Edit Page
Same as create function we will build the Update functionality for our CRUD application.
Let’s create a file named as “edit.php” and put the following code inside it. It will update the existing records which we want to update.
<?php
require_once "config.php";
$first_name = $last_name = $email = $phone_number = $address = "";
$first_name_error = $last_name_error = $email_error = $phone_number_error = $address_error = "";
if (isset($_POST["id"]) && !empty($_POST["id"])) {
$id = $_POST["id"];
$firstName = trim($_POST["first_name"]);
if (empty($firstName)) {
$first_name_error = "First Name is required.";
} elseif (!filter_var($firstName, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))) {
$first_name_error = "First Name is invalid.";
} else {
$firstName = $firstName;
}
$lastName = trim($_POST["last_name"]);
if (empty($lastName)) {
$last_name_error = "Last Name is required.";
} elseif (!filter_var($firstName, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))) {
$last_name_error = "Last Name is invalid.";
} else {
$lastName = $lastName;
}
$email = trim($_POST["email"]);
if (empty($email)) {
$email_error = "Email is required.";
} elseif (!filter_var($firstName, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))) {
$email_error = "Please enter a valid email.";
} else {
$email = $email;
}
$phoneNumber = trim($_POST["phone_number"]);
if (empty($phoneNumber)){
$phone_number_error = "Phone Number is required.";
} else {
$phoneNumber = $phoneNumber;
}
$address = trim($_POST["address"]);
if (empty($address)) {
$address_error = "Address is required.";
} else {
$address = $address;
}
if (empty($first_name_error_err) && empty($last_name_error) &&
empty($email_error) && empty($phone_number_error) && empty($address_error) ) {
$sql = "UPDATE `users` SET `first_name`= '$firstName', `last_name`= '$lastName', `email`= '$email', `phone_number`= '$phoneNumber', `address`= '$address' WHERE id='$id'";
if (mysqli_query($conn, $sql)) {
header("location: index.php");
} else {
echo "Something went wrong. Please try again later.";
}
}
mysqli_close($conn);
} else {
if (isset($_GET["id"]) && !empty(trim($_GET["id"]))) {
$id = trim($_GET["id"]);
$query = mysqli_query($conn, "SELECT * FROM users WHERE ID = '$id'");
if ($user = mysqli_fetch_assoc($query)) {
$firstName = $user["first_name"];
$lastName = $user["last_name"];
$email = $user["email"];
$phoneNumber = $user["phone_number"];
$address = $user["address"];
} else {
echo "Something went wrong. Please try again later.";
header("location: edit.php");
exit();
}
mysqli_close($conn);
} else {
echo "Something went wrong. Please try again later.";
header("location: edit.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: 1200px;
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 User</h2>
</div>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<input type="hidden" name="id" value="<?php echo $id; ?>"/>
<div class="form-group <?php echo (!empty($first_name_error)) ? 'has-error' : ''; ?>">
<label>First Name</label>
<input type="text" name="first_name" class="form-control" value="<?php echo $firstName; ?>">
<span class="help-block"><?php echo $first_name_error;?></span>
</div>
<div class="form-group <?php echo (!empty($last_name_error)) ? 'has-error' : ''; ?>">
<label>Last Name</label>
<input type="text" name="last_name" class="form-control" value="<?php echo $lastName; ?>">
<span class="help-block"><?php echo $last_name_error;?></span>
</div>
<div class="form-group <?php echo (!empty($email_error)) ? 'has-error' : ''; ?>">
<label>Email</label>
<input type="email" name="email" class="form-control" value="<?php echo $email; ?>">
<span class="help-block"><?php echo $email_error;?></span>
</div>
<div class="form-group <?php echo (!empty($phone_number_error)) ? 'has-error' : ''; ?>">
<label>Phone Number</label>
<input type="number" name="phone_number" class="form-control" value="<?php echo $phoneNumber; ?>">
<span class="help-block"><?php echo $phone_number_error;?></span>
</div>
<div class="form-group <?php echo (!empty($address_error)) ? 'has-error' : ''; ?>">
<label>Address</label>
<textarea name="address" class="form-control"><?php echo $address; ?></textarea>
<span class="help-block"><?php echo $address_error;?></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>Create Read File
To showing the user details we build the Read functionality for our CRUD application.
So, create a file “read.php” and put the following code inside it. It will fetch the records from the users table based the id of the user and show here.
<!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: 1200px;
margin: 0 auto;
}
</style>
</head>
<body>
<?php
if (isset($_GET["id"]) && !empty(trim($_GET["id"]))) {
require_once "config.php";
$id = trim($_GET["id"]);
$query = mysqli_query($conn, "SELECT * FROM users WHERE ID = '$id'");
if ($user = mysqli_fetch_assoc($query)) {
$firstName = $user["first_name"];
$lastName = $user["last_name"];
$email = $user["email"];
$phoneNumber = $user["phone_number"];
$address = $user["address"];
} else {
header("location: read.php");
exit();
}
mysqli_close($conn);
} else {
header("location: read.php");
exit();
}
?>
<div class="wrapper">
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="page-header">
<h1> User View</h1>
</div>
<div class="form-group">
<label>First Name</label>
<p class="form-control-static"><?php echo $firstName ?></p>
</div>
<div class="form-group">
<label>Last Name</label>
<p class="form-control-static"><?php echo $lastName ?></p>
</div>
<div class="form-group">
<label>Email</label>
<p class="form-control-static"><?php echo $email ?></p>
</div>
<div class="form-group">
<label>Phone Number</label>
<p class="form-control-static"><?php echo $phoneNumber ?></p>
</div>
<div class="form-group">
<label>Address</label>
<p class="form-control-static"><?php echo $address ?></p>
</div>
<p><a href="index.php" class="btn btn-primary">Back</a></p>
</div>
</div>
</div>
</div>
</body>
</html>Create Delete File
In the final step, we will build the Delete functionality for our PHP CRUD operation.
So, create a “delete.php” file and put the following code inside it. It will delete the existing records from the users table based the id attribute of the user.
<?php
if (isset($_POST["id"]) && !empty($_POST["id"])) {
require_once "config.php";
$id = $_POST["id"];
$query = "DELETE FROM users WHERE id = '$id'";
if (mysqli_query($conn, $query)) {
header("location: index.php");
} else {
echo "Something went wrong. Please try again later.";
}
mysqli_close($conn);
} else {
if (empty(trim($_GET["id"]))) {
echo "Something went wrong. Please try again later.";
header("location: index.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>Finally our PHP Mysql CRUD operation is completed. If you have any question or queries please comment below.
Great tutorial. Thank you kindly. It works fine on my machine.
Just one question though; when you delete a user, the user ID in the table overview is not reset to #1. So if you added 4 users and then deleted 3 out of 4, the ID is always the ID of the last remaining user. Is this intentional?
Cheers,
Peter
great ideas .
full of inspirational knowledge.
continue inspiring upcoming programmers
Thanks dear please follow us on social networks and what you want new please comment here
thanks you !
Thanks so much for this, first time am actually understanding how php and html work together with mysql.