In this article, I will show you how you can create a simple CRUD application using HTML, CSS, PHP, MySQL, and jQuery.
What is CRUD?
To whom those who don’t know what CRUD is – CRUD stands for Create, Read, Update, and Delete which are four fundamental operations that are considered necessary to implement a persistent storage application. Persistent storage encompasses data storage devices that maintain stored data even when the device is powered off, such as hard disks or solid-state drives. In contrast, volatile memory, including random access memory and internal caching, stores data temporarily and loses it when power is cut off.
Now, let us create a very basic CRUD application.
Let’s start by creating a database named ‘crud’ in MySQL. After that, create a table named person by executing this query-
CREATE TABLE users (
user_name VARCHAR(255),
phone VARCHAR(12),
type VARCHAR(20),
email VARCHAR(255),
address TEXT,
added_date DATE
);This query will create a table named users with the following columns:
- user_name: VARCHAR(255) – This column will store the user’s name, allowing up to 255 characters.
- phone: VARCHAR(12) – This column will store the user’s phone number, allowing up to 12 characters.
- type: VARCHAR(20) – This column will store the type of user, allowing up to 20 characters.
- email: VARCHAR(255) – This column will store the user’s email address, allowing up to 255 characters.
- address: TEXT – This column will store the user’s address, allowing for larger amounts of text.
- added_date: DATE – This column will store the date when the user was added to the database
Make a folder named simple crud inside htdocs in your xampp directory. If you are using wamp, make the folder inside www directory.
Make two folders named CSS and js inside the crud folder. These will keep the CSS file and the jQuery file respectively.
Create a file named connection.php inside the crud folder and add the code to it.
<?php
try{
$conn = mysqli_connect("localhost", "root", "", "crud");
if(!$conn){
throw new Exception("No database found");
}
}catch(Exception $e){
$e->getMessage();
}
?>Now, create index.php and add this code-
<?php
include_once("connection.php");
$res = mysqli_query($conn, "SELECT * FROM person");
$data = array();
while($single = mysqli_fetch_assoc($res)){
$data[] = $single;
}
if(isset($_POST['add_record'])){
$username = mysqli_real_escape_string($conn, $_POST['username']);
$userphone = mysqli_real_escape_string($conn, $_POST['userphone']);
$usertype = mysqli_real_escape_string($conn, $_POST['usertype']);
$usermail = mysqli_real_escape_string($conn, $_POST['usermail']);
$address = mysqli_real_escape_string($conn, $_POST['address']);
$added_date = date('Y-m-d');
$isInserted = mysqli_query($conn, "INSERT INTO person (user_name, phone, type, email, address, added_date) VALUES ('$username', '$userphone', '$usertype', '$usermail', '$address', '$added_date')");
header("Refresh:0");
}
if(isset($_POST['update_record'])){
$id = $_POST['person_id'];
$username = mysqli_real_escape_string($conn, $_POST['username_update']);
$userphone = mysqli_real_escape_string($conn, $_POST['userphone_update']);
$usertype = mysqli_real_escape_string($conn, $_POST['usertype_update']);
$usermail = mysqli_real_escape_string($conn, $_POST['usermail_update']);
$address = mysqli_real_escape_string($conn, $_POST['address_update']);
$updated_date = date('Y-m-d');
$isUpdated = mysqli_query($conn, "UPDATE person SET user_name = '$username', phone = '$userphone', type = '$usertype', email = '$usermail', address = '$address', updated_date = '$updated_date' WHERE id = $id");
if($isUpdated){
$updateMessage = "Record Updated Successfully!";
}
header("Refresh:0");
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="css/style.css" rel="stylesheet" type="text/css" />
<title>Simple Crud</title>
<!-- Styles -->
<style>
</style>
</head>
<body>
<div id="parent">
<header>
<div id="logo_box">Simple Crud </div>
<div id="header_links_box" class="links">
</div>
</header>
<div class="form_box">
<span id="messages">
<?php
if(isset($updateMessage)){
set_time_limit(5);
echo $updateMessage;
}
?>
</span>
<button type="button" id="add_button" class="buttons">Add</button> <button type="button" class="buttons" id="view_button">View</button>
<br /><br />
</div>
<div class="form_box" id="crud_add_box">
<form action="" method="POST">
<input placeholder="Enter name" class="halfinput" type="text" name="username">
<input placeholder="Enter phone number" class="halfinput" type="text" name="userphone">
<select class="halfinput dropdown" name="usertype">
<option value="">Select One</option>
<option value="student">Student</option>
<option value="professional">Professional</option>
</select>
<input placeholder="Enter email" class="halfinput" type="text" name="usermail">
<textarea placeholder="Enter address" id="address" name="address"></textarea>
<br /><br />
<button type="submit" class="buttons" id="submit" name="add_record">Submit</button>
</form>
</div>
<div class="form_box" id="crud_update_box">
<form action="" method="POST">
<input type="hidden" id="person_id" name="person_id" value=""/>
<input placeholder="Enter name" class="halfinput" type="text" id="username_update" name="username_update">
<input placeholder="Enter phone number" class="halfinput" type="text" id="phone_update" name="userphone_update">
<select class="halfinput dropdown" name="usertype_update" id="usertype_update">
<option value="">Select One</option>
<option value="student">Student</option>
<option value="professional">Professional</option>
</select>
<input placeholder="Enter email" class="halfinput" id="email_update" type="text" name="usermail_update">
<textarea placeholder="Enter address" name="address_update" id="address_update"></textarea>
<br /><br />
<button type="submit" class="buttons" id="update" name="update_record">Submit</button>
</form>
</div>
<div class="table_box" id="crud_view_box">
<table class="mytable">
<thead>
<tr>
<th>S.No</th>
<th>Name</th>
<th>Phone</th>
<th>Email</th>
<th>Type</th>
<th>Address</th>
<th>Action</th>
</tr>
<tbody>
<?php
if($data != null){
$sno = 1;
foreach ($data as $key => $val) {
?>
<tr>
<td><?php echo $sno;?></td>
<td><?php echo $val['user_name'];?></td>
<td><?php echo $val['phone'];?></td>
<td><?php echo $val['email'];?></td>
<td><?php echo $val['type'];?></td>
<td><?php echo $val['address'];?></td>
<td>
<button type="button" id="update_button_<?php echo $val['id']?>" class="buttons update_button">Update</button>
<button type="button" id="delete_button_<?php echo $val['id']?>" class="buttons delete_button">Delete</button>
</td>
</tr>
<?php
$sno++;
}
}
?>
</tbody>
</thead>
</table>
</div>
</div>
<script src="js/jquery.js"></script>
<script>
$(document).ready(function(){
$("#add_button").on("click", function(){
$("#crud_view_box").css("display", "none");
$("#crud_update_box").css("display", "none");
$("#crud_add_box").css("display", "block");
});
$("#view_button").on("click", function(){
$("#crud_view_box").css("display", "block");
$("#crud_update_box").css("display", "none");
$("#crud_add_box").css("display", "none");
});
$(".delete_button").on("click", function(){
var confirm = window.confirm("Are you sure you want to delete this?");
if(confirm){
var person_id = this.id.split("_")[2];
$.ajax({
url: "ajax_part.php",
method: "post",
data: {id: person_id, operation:"delete"},
success:function(result, status){
if(result == "deleted"){
alert("Deleted Successfully");
location.reload();
}
}
});
}
});
$(".update_button").on("click", function(){
var person_id = this.id.split("_")[2];
$.ajax({
url: "ajax_part.php",
method: "post",
data: {id: person_id, operation:"update"},
success:function(result, status){
var res = $.parseJSON(result);
$("#crud_update_box").css("display", "block");
$("#person_id").val(res['id']);
$("#username_update").val(res['user_name']);
$("#phone_update").val(res['phone']);
$("#email_update").val(res['email']);
$("#usertype_update").val(res['type']);
$("#address_update").val(res['address']);
$("#crud_add_box").css("display", "none");
$("#crud_view_box").css("display", "none");
}
});
});
});
</script>
</body>
</html>
The index file contains the basic structure of the application, the backend logic, and the jQuery code to perform the operations.
Now add some styling to this page. Create a file named ‘style.css’ and add the following code to it.
body{
margin:0px;
}
header{
background-color: black;
padding:10px;
width:100%;
}
header a{
color:white;
text-decoration:none;
margin-left:40px;
}
header a:first-of-type{
margin-left:0px;
}
header a:hover{
color:#000000;
background-color:#ffffff;
}
#header_links_box{
width:50%;
margin:auto;
display:inline-block;
text-align:center;
}
.form_box{
width:54%;
margin:auto;
margin-top:40px;
}
.form_box input{
width:100%;
margin-bottom:10px;
height:27px;
}
.form_box textarea{
width:91%;
height:70px;
}
.table_box{
width:74%;
margin:auto;
margin-top:40px;
}
.buttons{
background-color:cornflowerblue;
color:#ffffff;
padding:10px 14px;
border:none;
}
#posts_box{
width:54%;
margin:auto;
margin-top: 40px;
min-height:400px;
padding: 40px;
}
.blog_post_link{
text-decoration:none;
}
#logo_box{
color:#ffffff;
width:20%;
font-size:20px;
display:inline-block;
}
#add_button{
background-color: blue;
color:#ffffff;
padding:10px 14px;
border:none;
}
#view_button{
background-color: green;
color:#ffffff;
padding:10px 14px;
border:none;
}
.buttons:hover{
cursor:pointer;
}
.update_button{
width:67px;
background-color:orange;
color:#ffffff;
padding:10px 14px;
border:none;
text-decoration:none;
}
.delete_button{
width:67px;
background-color:red;
color:#ffffff;
padding:10px 14px;
border:none;
text-decoration:none;
}
#adminlogin{
background-color:cornflowerblue;
color:#ffffff;
padding:10px 14px;
border:none;
}
h2{
color:red;
list-style: none;
}
#admin_header_box{
display:inline-block;
width:25%;
text-align:right;
}
#online_dot{
display:inline-block;
background-color: lightgreen;
height:10px;width:10px;
border-radius:100%;
}
.halfinput{
width:44% !important;
}
.dropdown{
padding:7px;
width:46% !important;
}
#crud_add_box{
display:none;
}
#crud_update_box{
display:none;
}
.mytable{
width: 100%;
border-collapse: collapse;
overflow-x:auto;
}
.mytable th,td{
border:1px solid grey;
padding:7px 14px;
}
#parent{
width:100%;
}
@media only screen and (max-width: 600px){
.form_box{
width:80%;
margin:auto;
margin-top:40px;
}
#posts_box{
width:100%;
margin:auto;
margin-top:40px;
}
#logo_box{
color:#ffffff;
width:100%;
font-size:20px;
display:inline-block;
}
}