This article will help you learn how to create a simple and easy CRUD application in PHP using MySQL.
CRUD is an acronym for CREATE, READ, UPDATE, DELETE. We perform all these operations to manipulate the data in the database. Using PHP, we are going to create an application that performs all the CRUD operations.
To create and run our application, first, we need to set up our system with the following.
- Local Server:- XAMPP (That’s what we have used)
- Code Editor:- whichever you like ( we are using VS code)
Once you have all that set up, we can start working on our application.
Let’s first begin with our frontend
We have created a landing page( index.php ) to make our application look a bit professional.
<!-- Header -->
<?php include "header.php" ?>
<!-- body -->
<div class="container mt-5">
<h1 class="text-center"> Welcome, Lets Create a PHP CRUD Application!</h1>
<p class="text-center">
We are going to use PHP and MySQL to create a CRUD (Create, Read, Update, Delete) Application.
</p>
<div class="container">
<form action="includes/home.php" method="post">
<div class="from-group text-center">
<input type="submit" class="btn btn-primary mt-2" value="Let's Do it!">
</div>
</form>
</div>
</div>
<!-- Footer -->
<?php include "footer.php" ?>
You can start with the homepage, displaying all our data records in a tabular format. All the data from our user’s table will have an action button on each record to perform the READ, UPDATE and DELETE operation. We will also add an “Add New User” button to CREATE a new user.
Create a folder named “includes”. Then create a file named home.php with the following code add it into the “includes” folder.
<!-- Header -->
<?php include "../header.php"?>
<div class="container">
<h1 class="text-center" >Data to perform CRUD Operations</h1>
<a href="create.php" class='btn btn-outline-dark mb-2'> <i class="bi bi-person-plus"></i> Create New User</a>
<table class="table table-striped table-bordered table-hover">
<thead class="table-dark">
<tr>
<th scope="col">ID</th>
<th scope="col">Username</th>
<th scope="col">Email</th>
<th scope="col"> Password</th>
<th scope="col" colspan="3" class="text-center">CRUD Operations</th>
</tr>
</thead>
<tbody>
<tr>
<?php
$query="SELECT * FROM users"; // SQL query to fetch all table data
$view_users= mysqli_query($conn,$query); // sending the query to the database
// displaying all the data retrieved from the database using while loop
while($row= mysqli_fetch_assoc($view_users)){
$id = $row['id'];
$user = $row['username'];
$email = $row['email'];
$pass = $row['password'];
echo "<tr >";
echo " <th scope='row' >{$id}</th>";
echo " <td > {$user}</td>";
echo " <td > {$email}</td>";
echo " <td >{$pass} </td>";
echo " <td class='text-center'> <a href='view.php?user_id={$id}' class='btn btn-primary'> <i class='bi bi-eye'></i> View</a> </td>";
echo " <td class='text-center' > <a href='update.php?edit&user_id={$id}' class='btn btn-secondary'><i class='bi bi-pencil'></i> EDIT</a> </td>";
echo " <td class='text-center'> <a href='delete.php?delete={$id}' class='btn btn-danger'> <i class='bi bi-trash'></i> DELETE</a> </td>";
echo " </tr> ";
}
?>
</tr>
</tbody>
</table>
</div>
<!-- a BACK button to go to the index page -->
<div class="container text-center mt-5">
<a href="../index.php" class="btn btn-warning mt-5"> Back </a>
<div>
<!-- Footer -->
<?php include "../footer.php" ?>
Header And Footer File
We are also going to create common header and footer pages to add those to other pages when required and avoid rewriting the same code repeatedly.
So, create a file header.php and add the following code
<?php include "db.php" ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<!-- Bootstrap CSS -->
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-eOJMYsd53ii+scO/bJGFsiCZc+5NDVN2yr8+0RDqr0Ql0h+rP48ckxlpbzKgwra6" crossorigin="anonymous">
<!-- Bootstrap Icon -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/font/bootstrap-icons.css">
<title>PHP CRUD System</title>
</head>
<body>
Create a file footer.php and add the following code
<footer class="blockquote-footer fixed-bottom">Get more amazing projects in <cite title="Source Title"><a href="https://codewithbish.com/free-projects-download/" target="_blank">codewithbish.com</a></cite></footer>
</body>
</html>
Now Let’s set up our database
If you know your way around XAMPP, create a database “php_crud” and create a user table with four columns (ID, username, email, and password). Else you can execute a SQL query to create the table.
Creating the Database and User Table
1. first, open the XAMPP server and start 2 services ( Apache and MySQL).
2. Then click on Admin, or you can type in the URL “localhost/phpmyadmin/”
3. Click on either “new” or “Databases,” and enter your database name ( we are using ‘php_crud’ ) and click Create.
4. Now, click on “SQL,” enter the following code and click “Go.”
CREATE TABLE users(
ID int NOT NULL AUTO_INCREMENT,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
PRIMARY KEY (ID)
);
Now, Let’s Work on CRUD
Creating the Database Connection File
Now we are going to connect our MySQL database using a PHP script. Open your code editor, create a file db.php ( you can name it whatever you want ), and add the following code.
<?php
//server with default setting (user 'root' with no password)
$host = 'localhost'; // server
$user = 'root';
$pass = "";
$database = 'php_crud'; //Database Name
// establishing connection
$conn = mysqli_connect($host,$user,$pass,$database);
// for displaying an error msg in case the connection is not established
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
?>
- define the server and database details like database name, server name, username, and password.
- “mysqli_connect” function attempts the connection to MySQL Database.
- In case the connection fails this “mysqli_connect_error()” function display’s an error message.
NOTE: If you have a different username, password, or database name, make sure you replace them with the correct credentials. For example, if you have created a different database name then replace php_crud with your database name.
Creating the Create Page
Let’s build the create function of our php CRUD application. Create a file create.php with the following code and add it into the “includes” folder. It will create a form for the user to insert details like name, email and save the input data into the users’ table.
<!-- Header -->
<?php include "../header.php" ?>
<?php
if(isset($_POST['create']))
{
$user = $_POST['user'];
$email = $_POST['email'];
$pass = $_POST['pass'];
// SQL query to insert user data into the users table
$query= "INSERT INTO users(username, email, password) VALUES('{$user}','{$email}','{$pass}')";
$add_user = mysqli_query($conn,$query);
// displaying proper message for the user to see whether the query executed perfectly or not
if (!$add_user) {
echo "something went wrong ". mysqli_error($conn);
}
else { echo "<script type='text/javascript'>alert('User added successfully!')</script>";
}
}
?>
<h1 class="text-center">Add User details </h1>
<div class="container">
<form action="" method="post">
<div class="form-group">
<label for="user" class="form-label">Username</label>
<input type="text" name="user" class="form-control">
</div>
<div class="form-group">
<label for="email" class="form-label">Email ID</label>
<input type="email" name="email" class="form-control">
</div>
<div class="form-group">
<label for="pass" class="form-label">Password</label>
<input type="password" name="pass" class="form-control">
</div>
<div class="form-group">
<input type="submit" name="create" class="btn btn-primary mt-2" value="submit">
</div>
</form>
</div>
<!-- a BACK button to go to the home page -->
<div class="container text-center mt-5">
<a href="home.php" class="btn btn-warning mt-5"> Back </a>
<div>
<!-- Footer -->
<?php include "../footer.php" ?>
Creating the Read Page
Now that we have some data in our table let’s build the read function of our php CRUD application. Create a read.php file with the following code and add it into the “includes” folder. It will retrieve and show the data that we have stored in our table based on the ID attribute of the user.
<!-- Header -->
<?php include '../header.php'?>
<h1 class="text-center">User Details</h1>
<div class="container">
<table class="table table-striped table-bordered table-hover">
<thead class="table-dark">
<tr>
<th scope="col" >ID</th>
<th scope="col">Username</th>
<th scope="col">Email</th>
<th scope="col"> Password</th>
</tr>
</thead>
<tbody>
<tr>
<?php
// first we check using 'isset() function if the variable is set or not'
//Processing form data when form is submitted
if (isset($_GET['user_id'])) {
$userid = $_GET['user_id'];
// SQL query to fetch the data where id=$userid & storing data in view_user
$query="SELECT * FROM users WHERE id = {$userid} ";
$view_users= mysqli_query($conn,$query);
while($row = mysqli_fetch_assoc($view_users))
{
$id = $row['id'];
$user = $row['username'];
$email = $row['email'];
$pass = $row['password'];
echo "<tr >";
echo " <td >{$id}</td>";
echo " <td > {$user}</td>";
echo " <td > {$email}</td>";
echo " <td >{$pass} </td>";
echo " </tr> ";
}
}
?>
</tr>
</tbody>
</table>
</div>
<!-- a BACK Button to go to pervious page -->
<div class="container text-center mt-5">
<a href="home.php" class="btn btn-warning mt-5"> Back </a>
<div>
<!-- Footer -->
<?php include "../footer.php" ?>
Creating the Update Page
Now let’s build the update function of our php CRUD application. Create a update.php file with the following code and add it into the “includes” folder.
It will retrieve the data that we have stored in our table based on the ID attribute of the user and show it in the form, from which we can update the data and save it back to the users’ table.
<!-- Header -->
<?php include "../header.php"?>
<?php
// checking if the variable is set or not and if set adding the set data value to variable userid
if(isset($_GET['user_id']))
{
$userid = $_GET['user_id'];
}
// SQL query to select all the data from the table where id = $userid
$query="SELECT * FROM users WHERE id = $userid ";
$view_users= mysqli_query($conn,$query);
while($row = mysqli_fetch_assoc($view_users))
{
$id = $row['id'];
$user = $row['username'];
$email = $row['email'];
$pass = $row['password'];
}
//Processing form data when form is submitted
if(isset($_POST['update']))
{
$user = $_POST['user'];
$email = $_POST['email'];
$pass = $_POST['pass'];
// SQL query to update the data in user table where the id = $userid
$query = "UPDATE users SET username = '{$user}' , email = '{$email}' , password = '{$pass}' WHERE id = $userid";
$update_user = mysqli_query($conn, $query);
echo "<script type='text/javascript'>alert('User data updated successfully!')</script>";
}
?>
<h1 class="text-center">Update User Details</h1>
<div class="container ">
<form action="" method="post">
<div class="form-group">
<label for="user" >Username</label>
<input type="text" name="user" class="form-control" value="<?php echo $user ?>">
</div>
<div class="form-group">
<label for="email" >Email ID</label>
<input type="text" name="email" class="form-control" value="<?php echo $email ?>">
</div>
<small id="emailHelp" class="form-text text-muted">We'll never share your email with anyone else.</small>
<div class="form-group">
<label for="pass" >Password</label>
<input type="password" name="pass" class="form-control" value="<?php echo $pass ?>">
</div>
<div class="form-group">
<input type="submit" name="update" class="btn btn-primary mt-2" value="update">
</div>
</form>
</div>
<!-- a BACK button to go to the home page -->
<div class="container text-center mt-5">
<a href="home.php" class="btn btn-warning mt-5"> Back </a>
<div>
<!-- Footer -->
<?php include "../footer.php" ?>
Creating the Delete Page
Finally, we can build the delete function of our php CRUD application. Create a delete.php file with the following code and add it into the “includes” folder.
It will delete the data that we have stored in our table based on the ID attribute of the user.
<!-- Footer -->
<?php include "../header.php" ?>
<?php
if(isset($_GET['delete']))
{
$userid= $_GET['delete'];
// SQL query to delete data from user table where id = $userid
$query = "DELETE FROM users WHERE id = {$userid}";
$delete_query= mysqli_query($conn, $query);
header("Location: home.php");
}
?>
<!-- Footer -->
<?php include "footer.php" ?>
That’s it! You’re all set!
You now know how to create a PHP CRUD Application with MySQL.
Note: We have used Procedural method to create the CRUD Application. But if you want this application in Object-Oriented, then download it from here.
We hope this article helps you understand how to create a CRUD application using PHP and MySQL. If you find it helpful, don’t forget to share this article with your friends.
If you find any mistake or something didn’t work out quite right, do let us know. Thank You for reading!
Sir I need search Button to search my Id, user name, Any of us you type means it will show on grid. Please send me that source code. I am ur subscriber
This code stores empty data or null data in database. I think there would be a problem in add
-user part. So recheck the code do the necessary modifications.