HomePHPPHP MySQL CRUD Application

PHP MySQL CRUD Application

What is Crud

CRUD stands for Create, Read, Update, and Delete. It is an acronym that represents the four basic operations that can be performed on data in a database or any persistent storage system. These operations are fundamental to most database interactions and form the basis for many web applications and software systems. Here’s a brief overview of each operation:

  1. Create (C): This operation involves the creation of new records or entries in a database. It corresponds to the process of adding new data to the system.
  2. Read (R): This operation involves retrieving or reading existing data from the database. It corresponds to querying and fetching information from the database without making any changes to the data.
  3. Update (U): This operation involves modifying existing data in the database. It allows you to update or edit the values of specific fields within a record.
  4. Delete (D): This operation involves removing data from the database. It allows you to delete records or entries, effectively removing them from the system.

CRUD operations are commonly associated with database management systems (DBMS) and are used in various applications, such as web development, mobile app development, and other software systems where data storage and manipulation are essential. These operations provide a simple and standardized way to interact with and manage data in a persistent storage system.

Creating the Database Table

CREATE TABLE `tblusers` (
  `ID` int(10) NOT NULL,
  `FirstName` varchar(200) DEFAULT NULL,
  `LastName` varchar(200) DEFAULT NULL,
  `MobileNumber` bigint(10) DEFAULT NULL,
  `Email` varchar(200) DEFAULT NULL,
  `Address` mediumtext DEFAULT NULL,
  `CreationDate` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Creating a database table involves defining the structure of the table, including the columns and their data types. The specific syntax for creating a table depends on the database management system (DBMS) you are using, as different database systems have their own SQL (Structured Query Language) syntax. I’ll provide a generic example using SQL that should give you an idea, but keep in mind that you might need to adjust it based on the DBMS you’re using.

Creating the Config File

Creating a configuration file depends on the context and the purpose of the configuration. Typically, configuration files store settings or parameters that a program or application uses. The format and structure of a configuration file can vary, and the file extension might be different depending on the technology you’re working with. Below, I’ll provide a simple example of a configuration file in a hypothetical format.

Let’s create a file named “database.php” and put the following code inside it.

<?php
$con=mysqli_connect("localhost", "root", "mysql", "curd-blog");
if(mysqli_connect_errno())
{
echo "Connection Fail".mysqli_connect_error();
}
?>

Let’s say you are creating a configuration file for a web application, and you want to store database connection details and some other settings. You might use a file in JSON (JavaScript Object Notation) format:

Creating the index Page

Creating a list page typically involves fetching data from a data source (such as a database) and displaying it in a user-friendly format, often as a list. Below is a simple example of creating a list page using HTML and PHP.

Let’s create a file named “index.php” and put the following code inside it.

<?php
//database connection  file
include('Database.php');
include('delete.php');
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Curd operation Php Mysql</title>
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Varela+Round">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.1/css/all.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="index.css">
</head>
<body>
<div class="container-xl">
    <div class="table-responsive">
        <div class="table-wrapper">
            <div class="table-title">
                <div class="row">
                    <div class="col-sm-5">
                        <h2 style="color:red;font-weight: bold;">User Employee <b>Data</b></h2>
                    </div>
                       <div class="col-sm-7" align="right">
                        <a href="insert.php" class="btn btn-danger" ><span><i class="fa-solid fa-plus"></i> Add New List</span></a>
                                        
                    </div>
                </div>
            </div>
            <table class="table table-striped table-hover">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>Name</th>                       
                        <th>Email</th>
                        <th>Mobile Number</th>
                        <th>Address</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody>
                     <?php
$ret=mysqli_query($con,"select * from employee");
$cnt=1;
$row=mysqli_num_rows($ret);
if($row>0){
while ($row=mysqli_fetch_array($ret)) {
?>
<!--Fetch the Records -->
                    <tr>
                        <td><?php echo $cnt;?></td>
                        <td><?php  echo $row['FirstName'];?> <?php  echo $row['LastName'];?></td>
                        <td><?php  echo $row['Email'];?></td>                        
                        <td><?php  echo $row['MobileNumber'];?></td>
                        <td><?php  echo $row['Address'];?></td>
                       
                        
                        <td>
  <a href="read.php?viewid=<?php echo htmlentities ($row['id']);?>" class="view" title="View" data-toggle="tooltip"><i class="material-icons"></i></a>
                            <a href="edit.php?editid=<?php echo htmlentities ($row['id']);?>" class="edit" title="Edit" data-toggle="tooltip"><i class="material-icons"></i></a>
                            <a href="index.php?delid=<?php echo ($row['id']);?>" class="delete" title="Delete" data-toggle="tooltip" onclick="return confirm('Do you really want to Delete ?');"><i class="material-icons"></i></a>
                        </td>
                    </tr>
                    <?php 
$cnt=$cnt+1;
} } else {?>
<tr>
    <th style="text-align:center; color:red;" colspan="6">No Record Found</th>
</tr>
<?php } ?>                 
                
                </tbody>
            </table>
       
        </div>
    </div>
</div>     
</body>
</html>

Let’s create a file named “index.css” and put the following code inside it.

body {
    color: #566787;
    background: lightblue;
    font-family: 'Roboto', sans-serif;
}
.table-responsive {
    margin: 30px 0;
}
.table-wrapper {
    min-width: 1000px;
    background: #fff;
    padding: 20px;
    border-radius: 5px;
    box-shadow: 0 1px 1px rgba(0,0,0,.05);
}
.table-title {
    font-size: 15px;
    padding-bottom: 10px;
    margin: 0 0 10px;
    min-height: 45px;
}
.table-title h2 {
    margin: 5px 0 0;
    font-size: 24px;
}
.table-title select {
    border-color: #ddd;
    border-width: 0 0 1px 0;
    padding: 3px 10px 3px 5px;
    margin: 0 5px;
}
.table-title .show-entries {
    margin-top: 7px;
}
.search-box {
    position: relative;
    float: right;
}
.search-box .input-group {
    min-width: 200px;
    position: absolute;
    right: 0;
}
.search-box .input-group-addon, .search-box input {
    border-color: #ddd;
    border-radius: 0;
}
.search-box .input-group-addon {
    border: none;
    border: none;
    background: transparent;
    position: absolute;
    z-index: 9;
}
.search-box input {
    height: 34px;
    padding-left: 28px;     
    box-shadow: none !important;
    border-width: 0 0 1px 0;
}
.search-box input:focus {
    border-color: #3FBAE4;
}
.search-box i {
    color: #a0a5b1;
    font-size: 19px;
    position: relative;
    top: 8px;
}
table.table tr th, table.table tr td {
    border-color: #e9e9e9;
}
table.table th i {
    font-size: 13px;
    margin: 0 5px;
    cursor: pointer;
}
table.table td:last-child {
    width: 130px;
}
table.table td a {
    color: #a0a5b1;
    display: inline-block;
    margin: 0 5px;
}
table.table td a.view {
    color: #5c60e7;
}
table.table td a.edit {
    color: #db15b0;
}
table.table td a.delete {
    color: #E34724;
}
table.table td i {
    font-size: 19px;
}    
.pagination {
    float: right;
    margin: 0 0 5px;
}
.pagination li a {
    border: none;
    font-size: 13px;
    min-width: 30px;
    min-height: 30px;
    padding: 0 10px;
    color: #999;
    margin: 0 2px;
    line-height: 30px;
    border-radius: 30px !important;
    text-align: center;
}
.pagination li a:hover {
    color: #666;
}   
.pagination li.active a {
    background: #03A9F4;
}
.pagination li.active a:hover {        
    background: #0397d6;
}
.pagination li.disabled i {
    color: #ccc;
}
.pagination li i {
    font-size: 16px;
    padding-top: 6px
}
.hint-text {
    float: left;
    margin-top: 10px;
    font-size: 13px;
}
.table-striped tbody tr:nth-of-type(odd) {
    background-color: rgb(161 125 125 / 21%);
}
.table-hover tbody tr:hover {
    color: #212529;
    background-color: rgb(235 227 227);
}

Creating the insert Page

Let’s create a file named “insert.php” and put the following code inside it. It will generate a web form that can be used to insert records in the employee table.

Let’s create a file named “insert.css” and put the following code inside it.

<?php 
//Databse Connection file
include('Database.php');
if(isset($_POST['submit']))
  {
  	//getting the post values
    $fname=$_POST['fname'];
    $lname=$_POST['lname'];
    $mobile=$_POST['mobile'];
    $email=$_POST['email'];
    $add=$_POST['address'];
   
  // Query for data insertion
     $query=mysqli_query($con, "insert into employee(FirstName,LastName, MobileNumber, Email, Address) value('$fname','$lname', '$mobile', '$email', '$add' )");
    if ($query) {
    echo "<script>alert('You have successfully inserted the data');</script>";
    echo "<script type='text/javascript'> document.location ='index.php'; </script>";
  }
  else
    {
      echo "<script>alert('Something Went Wrong. Please try again');</script>";
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto:400,700">
<title>PHP Crud Operation Php Mysql</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.1/css/all.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="insert.css">
</head>
<body>
<div class="signup-form-employee">
    <form  method="POST">
		<h2 style="color:red;font-weight: bold;">Add Employee Data</h2>
        <div class="form-group">
			<div class="row">
				<div class="col"><input type="text" class="form-control" name="fname" placeholder="First Name" required="true"></div>
				<div class="col"><input type="text" class="form-control" name="lname" placeholder="Last Name" required="true"></div>
			</div>        	
        </div>
        <div class="form-group">
            <input type="text" class="form-control" name="mobile" placeholder="Enter your Mobile Number" required="true" maxlength="10" pattern="[0-9]+">
        </div>
        <div class="form-group">
        	<input type="email" class="form-control" name="email" placeholder="Enter your Email id" required="true">
        </div>
		
		<div class="form-group">
            <textarea class="form-control" name="address" placeholder="Enter Your Address" required="true"></textarea>
        </div>        
      
		<div class="form-group">
            <button type="submit" class="btn btn-danger btn-lg btn-block" name="submit">Submit</button>
		</div>
    </form>
	<div class="text-center" style="color:#000;"><button class="btn btn-danger "><a href="index.php"style="text-decoration: none;"><i class="fa-solid fa-backward"></i> Back Data List</a></button></div>
</div>
</body>
</html>

Let’s create a file named “insert.css” and put the following code inside it.

body {
	color: #fff;
	font-family: 'Roboto', sans-serif;
}
.form-control {
    height: 40px;
    border: 2px solid #fff;
    box-shadow: none;
    color: #969fa4;
}
.form-control, .btn {        
	border-radius: 3px;
}
.form-control:focus {
    color: #495057;
    background-color: #fff;
    border-color: #dc3545;
    outline: 0;
    border: 2px solid #dc3545 !important;
	box-shadow:none !important;
}
.signup-form-employee {
	width: 900px;
	margin: 0 auto;
	padding: 30px 0;
  	font-size: 15px;
}
.signup-form-employee h2 {
	color: #636363;
	margin: 0 0 15px;
	position: relative;
	text-align: center;
}
	
.signup-form-employee h2:before {
	left: 0;
}
.signup-form-employee h2:after {
	right: 0;
}
.signup-form .hint-text {
	color: #999;
	margin-bottom: 30px;
	text-align: center;
}
.signup-form-employee form {
    color: #999;
    border-radius: 15px;
    margin-bottom: 15px;
    background: #f2f3f7;
    box-shadow: 1px 0px 8px 2px lightgray;
    padding: 30px;
}
.signup-form-employee .form-group {
	margin-bottom: 20px;
}
.signup-form-employee input[type="checkbox"] {
	margin-top: 3px;
}
.signup-form-employee .btn {        
	font-size: 16px;
	font-weight: bold;		
	min-width: 140px;
	outline: none !important;
}
.signup-form-employee .row div:first-child {
	padding-right: 10px;
}
.signup-form-employee .row div:last-child {
	padding-left: 10px;
}    	
.signup-form-employee a {
	color: #fff;
	text-decoration: underline;
}
.signup-form-employee a:hover {
	text-decoration: none;
}
.signup-form-employee form a {
	color: #5cb85c;
	text-decoration: none;
}	
.signup-form-employee form a:hover {
	text-decoration: underline;
}  

Creating the Read Page

Let’s create a file named “read.php” and put the following code inside it. It will simply retrieve the records from the employee table based the id attribute of the employee.

<?php
include('Database.php');
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Crud Operation Using PHP and MySQLi</title>
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Varela+Round">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.1/css/all.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
<style>
body {
    color: #566787;
    background: lightblue;
    font-family: 'Roboto', sans-serif;
}
.table-responsive {
    margin: 30px 0;
}
.table-wrapper {
    min-width: 1000px;
    background: #fff;
    padding: 20px;
    box-shadow: 0 1px 1px rgba(0,0,0,.05);
}
.table-title {
    font-size: 15px;
    padding-bottom: 10px;
    margin: 0 0 10px;
    min-height: 45px;
}
.table-title h2 {
    margin: 5px 0 0;
    font-size: 24px;
}
.table-title select {
    border-color: #ddd;
    border-width: 0 0 1px 0;
    padding: 3px 10px 3px 5px;
    margin: 0 5px;
}
.table-title .show-entries {
    margin-top: 7px;
}
table.table tr th, table.table tr td {
    border-color: #e9e9e9;
}
table.table th i {
    font-size: 13px;
    margin: 0 5px;
    cursor: pointer;
}
table.table td:last-child {
    width: 130px;
}
table.table td a {
    color: #a0a5b1;
    display: inline-block;
    margin: 0 5px;
}
table.table td a.view {
    color: #03A9F4;
}
table.table td a.edit {
    color: #FFC107;
}
table.table td a.delete {
    color: #E34724;
}
table.table td i {
    font-size: 19px;
}    
.hint-text {
    float: left;
    margin-top: 10px;
    font-size: 13px;
}
</style>
</head>
<body>
<div class="container-xl">
    <div class="table-responsive">
        <div class="table-wrapper">
            <div class="table-title">
                <div class="row">
                    <div class="col-sm-5">
                        <h2 style="color:red;font-weight: bold;">Employee <b>Data</b></h2>
                    </div>
                    <div class="col-sm-7" align="right">
                        <a href="index.php" style="color:#fff; text-decoration:none;" class="btn btn-danger" ><span><i class="fa-solid fa-backward"></i> Back View Employee Data</span></a>
                                        
                    </div>
                     
                </div>
            </div>
<table cellpadding="0" cellspacing="0" border="0" class="display table table-bordered" id="hidden-table-info">
               
<tbody>
<?php
$vid=$_GET['viewid'];
$ret=mysqli_query($con,"select * from employee where ID =$vid");
$cnt=1;
while ($row=mysqli_fetch_array($ret)) {
?>
 <tr>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Email</th>
    <th>Mobile Number</th>
    <th>Address</th>
  </tr>
  <tr>
  <td><?php  echo $row['FirstName'];?></td>
  <td><?php  echo $row['LastName'];?></td>
  <td><?php  echo $row['Email'];?></td>
  <td><?php  echo $row['MobileNumber'];?></td>
  <td><?php  echo $row['Address'];?></td>
  </tr>
  
<?php 
$cnt=$cnt+1;
}?>
                 
</tbody>
</table>
       
        </div>
    </div>
</div>     
</body>
</html>

Creating the edit Page

Let’s create a file named “edit.php” and put the following code inside it. It will update the existing records in the employee table based the id attribute of the employee.

<?php 
//Database Connection
include('Database.php');
if(isset($_POST['submit']))
  {
  	$eid=$_GET['editid'];
  	//Getting Post Values
    $fname=$_POST['fname'];
    $lname=$_POST['lname'];
    $mobile=$_POST['mobile'];
    $email=$_POST['email'];
    $add=$_POST['address'];
    //Query for data updation
     $query=mysqli_query($con, "update  employee set FirstName='$fname',LastName='$lname', MobileNumber='$mobile', Email='$email', Address='$add' where ID='$eid'");
     
    if ($query) {
    echo "<script>alert('You have successfully update the data');</script>";
    echo "<script type='text/javascript'> document.location ='index.php'; </script>";
  }
  else
    {
      echo "<script>alert('Something Went Wrong. Please try again');</script>";
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto:400,700">
<title>PHP Crud Operation!!</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
<style>
body {
	color: #fff;
	background: lightblue;
	font-family: 'Roboto', sans-serif;
}
.form-control {
	height: 40px;
	box-shadow: none;
	color: #969fa4;
}
.form-control:focus {
	border-color: #5cb85c;
}
.form-control, .btn {        
	border-radius: 3px;
}
.signup-form-employee {
	width: 900px;
	margin: 0 auto;
	padding: 30px 0;
  	font-size: 15px;
}
.signup-form-employee h2 {
	color: #636363;
	margin: 0 0 15px;
	position: relative;
	text-align: center;
}
	
.signup-form-employee h2:before {
	left: 0;
}
.signup-form-employee h2:after {
	right: 0;
}
.signup-form .hint-text {
	color: #999;
	margin-bottom: 30px;
	text-align: center;
}
.signup-form-employee form {
	color: #999;
	border-radius: 3px;
	margin-bottom: 15px;
	background: #f2f3f7;
	box-shadow: 0px 2px 2px rgba(0, 0, 0, 0.3);
	padding: 30px;
}
.signup-form-employee .form-group {
	margin-bottom: 20px;
}
.signup-form-employee input[type="checkbox"] {
	margin-top: 3px;
}
.signup-form-employee .btn {        
	font-size: 16px;
	font-weight: bold;		
	min-width: 140px;
	outline: none !important;
}
.signup-form-employee .row div:first-child {
	padding-right: 10px;
}
.signup-form-employee .row div:last-child {
	padding-left: 10px;
}    	
.signup-form-employee a {
	color: #fff;
	text-decoration: underline;
}
.signup-form-employee a:hover {
	text-decoration: none;
}
.signup-form-employee form a {
	color: #5cb85c;
	text-decoration: none;
}	
.signup-form-employee form a:hover {
	text-decoration: underline;
} 
</style>
</head>
<body>
<div class="signup-form-employee">
    <form  method="POST">
 <?php
$eid=$_GET['editid'];
$ret=mysqli_query($con,"select * from employee where ID='$eid'");
while ($row=mysqli_fetch_array($ret)) {
?>
		<h2 style="color:red;font-weight: bold;">Update Employee Data </h2>
		<p class="hint-text">Update your info.</p>
        <div class="form-group">
			<div class="row">
				<div class="col"><input type="text" class="form-control" name="fname" value="<?php  echo $row['FirstName'];?>" required="true"></div>
				<div class="col"><input type="text" class="form-control" name="lname" value="<?php  echo $row['LastName'];?>" required="true"></div>
			</div>        	
        </div>
        <div class="form-group">
            <input type="text" class="form-control" name="mobile" value="<?php  echo $row['MobileNumber'];?>" required="true" maxlength="10" pattern="[0-9]+">
        </div>
        <div class="form-group">
        	<input type="email" class="form-control" name="email" value="<?php  echo $row['Email'];?>" required="true">
        </div>
		
		<div class="form-group">
            <textarea class="form-control" name="address" required="true"><?php  echo $row['Address'];?></textarea>
        </div>        
      <?php 
}?>
		<div class="form-group">
            <button type="submit" class="btn btn-danger btn-lg btn-block" name="submit">Update</button>
			<!--button type="button" class="btn btn-primary btn-lg btn-block" name="back"><a href="index.php" style="color:#fff; text-decoration:none;">Back Index</a></button-->
        </div>
    </form>
</div>
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
</body>
</html>

Creating the Delete Page

Let’s create a file named “delete.php” and put the following code inside it. It will delete the existing records from the employee table based the id attribute of the employee.

<?php
//database conection  file
include('Database.php');
//Code for deletion
if(isset($_GET['delid']))
{
$rid=intval($_GET['delid']);
$sql=mysqli_query($con,"delete from employee where ID=$rid");
echo "<script>alert('Data deleted');</script>"; 
echo "<script>window.location.href = 'index.php'</script>";     
} 
?>

Conclusion

A CRUD application in PHP is essential for developing dynamic web applications that require data management capabilities. By carefully implementing and securing the Create, Read, Update, and Delete operations, you can build robust and user-friendly applications that interact effectively with a database. Adhering to best practices in security and code organization will help ensure your CRUD application is reliable and maintainable.

Related Post

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Must Read

CATEGORY

spot_img