CRUD operation using PHP and MySQLi
In this tutorial, today we will learn how to build a simple CRUD Operation (Create, Read, Update, Delete) functionality in PHP using MySQL. We will discuss every step of building the CRUD application. After getting some better knowledge of PHP it's time to making CRUD Operation in PHP with MySQL easy way. Here will describe a simple PHP CRUD application with Bootstrap styles from scratch and step by step including record listing, record inserting, record updating, and record deleting from the database.
CRUD Stands for create, read, update and delete record in the database.
Step 1– Create a Database Table
Now We Open browser type http://localhost/phpmyadmin, create a database named ‘php_crudoperation’. After creating database run the SQL script or import the SQL file.
MySQL Table users
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
);Step 2- Create Config File
After creating the database table, we need to create a connection for connecting to the MySQL database server. So, we 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_crudoperation');
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>Step - Create View Page
After creating the Config.php file, we will create a view page for CRUP operation which shows the user records as the grid view. We also update the edit, view, and delete icons on it.
Creating a view page we add a file named “index.php” in our crud application folder and write 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>Step 4- Create Functionality Page
At this step, we’ll build CRUD application functionality. So, create a new file named “function.php” and Write 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>Step 5– Create Edit Record Page
Same as the create record function we will build the Update record functionality for our CRUD application. Let’s create a file named “edit.php” and write the following code inside it. It will update the existing records in database 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>Step 6– Create View Record Page
0 Comments