CRUD Operations in PHP Using MySQL

CRUD Operations in PHP: In this tutorial, you will get the smart & the standard way to create a CRUD Operations. It is created using PHP and MySQL with a simple example. Even It is explained through the custom functions. So, It is very simple to learn & develop for your project.

If you already know it, you will certainly read this tutorial. Because you will get a new way & concept through this tutorial.

If you are a learner, you have a good opportunity to learn in a smart way. It will help you to develop your PHP project. Even It will be most useful for the Interview.

CRUD Operation in PHP With Source Code

In the case of CRUD Operations in PHP, You can CREATEREADUPDATE and DELETE records. These operations will be performed with MYSQL Database.

In CRUD operations, you will learn to create the following Operation

  • CREATE –  You will insert records into MySQL table Using PHP and MySQLi Procedural
  • READ – You will fetch records from MySQL table  and display in HTML Table Using PHP and MySQLi Procedural
  • UPDATE – You will update records in MySQL table Using PHP and MySQLi Procedural
  • DELETE – You will delete records from MySQL table Using PHP and MySQLi Procedural

crud operations in php

 

Configure CRUD Operations in PHP

Before creating CRUD Operation in PHP, you must configure the following required steps.

Create a CRUD Folder Structure

You can create a  project folder like the following structure.

crud/
|__create-form.php
|__create-script.php
|__read-script.php
|__user-table.php
|__update-form.php
|__update-script.php
|__delete-script.php
|__database.php

Create MySQL Database and Table

Create user_details table in the  crud  MySQL Database using the following query

Table Name – user_details

CREATE TABLE `user_details` (
  `id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `full_name` varchar(255) DEFAULT NULL,
  `email_address` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
   `country` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL
);

Connect PHP to MYSQL Database

You must connect the PHP CRUD app to the MySQL database. Otherwise, the CRUD script will not work

File Name – database.php

<?php

$hostname     = "localhost";
$username     = "root";
$password     = "";
$databasename = "crud";

// Create connection
$connection = mysqli_connect($hostname, $username, $password,$databasename);
// Check connection
if (!$connection) {
    die("Unable to Connect database: " . mysqli_connect_error());
}
?>

 

Create the CRUD Operations Using PHP Step By Step

Now, you will learn each step with a simple example through the following steps. So, Read each point without skipping any one of the given steps.

CREATE – Insert Data into MySQL Table

In the case of CREATE, you have to insert data into the MySQL table using PHP. therefore, configure the following steps

Create an HTML Form using writing the following HTML code. This form will take user input to store in the MySQL table.

File Name – create-form.php

<?php

include('create-script.php');
?>

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>PHP CRUD Operations</title>
<style>
    
body{
    overflow-x: hidden;
}

* {
  box-sizing: border-box;}
.user-detail form {
    height: 100vh;
    border: 2px solid #f1f1f1;
    padding: 16px;
    background-color: white;
    }
    .user-detail{
      width: 30%;
    float: left;
    }

input{
  width: 100%;
  padding: 15px;
  margin: 5px 0 22px 0;
  display: inline-block;
  border: none;
  background: #f1f1f1;}
input[type=text]:focus, input[type=password]:focus {
    background-color: #ddd;
    outline: none;}
button[type=submit] {
    background-color: #434140;
    color: #ffffff;
    padding: 10px 20px;
    margin: 8px 0;
    border: none;
    cursor: pointer;
    width: 100%;
    opacity: 0.9;
    font-size: 20px;}
label{
  font-size: 18px;;}
button[type=submit]:hover {
  background-color:#3d3c3c;}
  .form-title a, .form-title h2{
   display: inline-block;
   
  }
  .form-title a{
      text-decoration: none;
      font-size: 20px;
      background-color: green;
      color: honeydew;
      padding: 2px 10px;
  }
 
</style>
</head>
<body>
<!--====form section start====-->

<div class="user-detail">

    <div class="form-title">
    <h2>Create Form</h2>
    
    
    </div>
 
<p style="color:red"><?php if(!empty($msg)){echo $msg; }?></p>

    <form method="post" action="">
          <label>Full Name</label>
          <input type="text" placeholder="Enter Full Name" name="full_name" required>
          <label>Email Address</label>
          <input type="email" placeholder="Enter Email Address" name="email_address" required>
          <label>City</label>
          <input type="city" placeholder="Enter Full City" name="city" required>
          <label>Country</label>
          <input type="text" placeholder="Enter Full Country" name="country" required>
          <button type="submit" name="create">Submit</button>
    </form>
        </div>
</div>
<!--====form section start====-->


</body>
</html>

 

Write MySQL INSERT Query in PHP to insert data into the MySQL table.

File Name – create-script.php

<?php

include('database.php');

if(isset($_POST['create'])){
   
      $msg=insert_data($connection);
      
}

// insert query
function insert_data($connection){
   
      $full_name= legal_input($_POST['full_name']);
      $email_address= legal_input($_POST['email_address']);
      $city = legal_input($_POST['city']);
      $country = legal_input($_POST['country']);

      $query="INSERT INTO user_details (full_name,email_address,city,country) VALUES ('$full_name','$email_address','$city','$country')";
      $exec= mysqli_query($connection,$query);
      if($exec){

        $msg="Data was created sucessfully";
        return $msg;
      
      }else{
        $msg= "Error: " . $query . "<br>" . mysqli_error($connection);
      }
}

// convert illegal input to legal input
function legal_input($value) {
  $value = trim($value);
  $value = stripslashes($value);
  $value = htmlspecialchars($value);
  return $value;
}
?>

 

READ – Fetch Data From MySQL Table

In the case of READ, you have to fetch data from the MySQL table and display it in an HTML table using PHP. therefore, configure the following steps

Write the MySQL Query in PHP to fetch data from the MySQL table.

File Name – read-script.php

<?php

include('database.php');

$fetchData= fetch_data($connection);

// fetch query
function fetch_data($connection){
  $query="SELECT * from user_details ORDER BY id DESC";
  $exec=mysqli_query($connection, $query);
  if(mysqli_num_rows($exec)>0){

    $row= mysqli_fetch_all($exec, MYSQLI_ASSOC);
    return $row;  
        
  }else{
    return $row=[];
  }
}
?>

 

Create an HTML Table and display data in it using writing the following HTML code.

File Name – user-table.php

<?php

include('read-script.php');
?>


<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>PHP CRUD Operations</title>
<style>
     table, td, th {  
      border: 1px solid #ddd;
      text-align: left;
    }
    
    table {
      border-collapse: collapse;
      max-width: 100%;
     width:90%;

    }
    .table-data{
      
      width:65%;
      float: right;
    }
    th, td {
      padding: 15px;
    }
body{
    overflow-x: hidden;
}

* {
  box-sizing: border-box;}
</style>
</head>
<body>


<div class="table-data">
        <div class="list-title">
 <h2>CRUD List</h2>
          
            </div>

    <table border="1">

        <tr>

            <th>S.N</th>
            <th>Full Name</th>
            <th>Email Address</th>
            <th>City</th>
            <th>Country</th>
            <th>Edit</th>
            <th>Delete</th>


        </tr>
        
<?php

        if(count($fetchData)>0){
        $sn=1;
        foreach($fetchData as $data){
            
?> <tr>
<td><?php echo $sn; ?></td>
<td><?php echo $data['full_name']; ?></td>
<td><?php echo $data['email_address']; </td>
<td><?php echo $data['city']; ?></td>
<td><?php echo $data['country']; ?></td>
<td><a href="crud-form.php?edit=<?php echo $data['id']; ?>">Edit</a></td>
<td><a href="crud-form.php?delete=<?php echo $data['id']; ?>">Delete</a></td>
</tr> <?php

      $sn++; }

      }else{
            
?>

      <tr>
        <td colspan="7">No Data Found</td>
      </tr>
                
<?php

    }
?>
 
    </table>
    </div>

</body>
</html>

 

UPDATE – Update Data in MySQL Table

In the case of UPDATE, you have to update data in the MySQL table using PHP. therefore, configure the following steps

Display data in the HTML form to update it.

File Name – update-form.php

<?php

include('update-script.php');
?>


<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>PHP CRUD Operations</title>
<style>
    
body{
    overflow-x: hidden;
}

* {
  box-sizing: border-box;}
.user-detail form {
    height: 100vh;
    border: 2px solid #f1f1f1;
    padding: 16px;
    background-color: white;
    }
    .user-detail{
      width: 30%;
    float: left;
    }

input{
  width: 100%;
  padding: 15px;
  margin: 5px 0 22px 0;
  display: inline-block;
  border: none;
  background: #f1f1f1;}
input[type=text]:focus, input[type=password]:focus {
    background-color: #ddd;
    outline: none;}
button[type=submit] {
    background-color: #434140;
    color: #ffffff;
    padding: 10px 20px;
    margin: 8px 0;
    border: none;
    cursor: pointer;
    width: 100%;
    opacity: 0.9;
    font-size: 20px;}
label{
  font-size: 18px;;}
button[type=submit]:hover {
  background-color:#3d3c3c;}
  .form-title a, .form-title h2{
   display: inline-block;
   
  }
  .form-title a{
      text-decoration: none;
      font-size: 20px;
      background-color: green;
      color: honeydew;
      padding: 2px 10px;
  }
 
</style>
</head>
<body>
<!--====form section start====-->

<div class="user-detail">

    <div class="form-title">
    <h2>Create Form</h2>
    
    
    </div>
 
    <p style="color:red">
    
<?php if(!empty($msg)){echo $msg; }?>
</p>
    <form method="post" action="">
          <label>Full Name</label>
        
<input type="text" placeholder="Enter Full Name" name="full_name" required value="<?php echo isset($editData) ? $editData['full_name'] : '' ?>">

          <label>Email Address</label>
        
<input type="email" placeholder="Enter Email Address" name="email_address" required value="<?php echo isset($editData) ? $editData['email_address'] : '' ?>">

          <label>City</label>
<input type="city" placeholder="Enter Full City" name="city" required value="<?php echo isset($editData) ? $editData['city'] : '' ?>">

          <label>Country</label>
        
<input type="text" placeholder="Enter Full Country" name="country" required value="<?php echo isset($editData) ? $editData['country'] : '' ?>">

          <button type="submit" name="update">Submit</button>
    </form>
        </div>
</div>
<!--====form section start====-->


</body>
</html>

 

Write the  query to update data in the MySQL table

File Name – update-script.php

<?php

include('database.php');


if(isset($_GET['edit'])){

    $id= $_GET['edit'];
  $editData= edit_data($connection, $id);
}

if(isset($_POST['update']) && isset($_GET['edit'])){

  $id= $_GET['edit'];
    update_data($connection,$id);
    
    
} 

// update data query
function update_data($connection, $id){

    $full_name= legal_input($_POST['full_name']);
      $email_address= legal_input($_POST['email_address']);
      $city = legal_input($_POST['city']);
      $country = legal_input($_POST['country']);

      $query="UPDATE user_details 
            SET full_name='$full_name',
                email_address='$email_address',
                city= '$city',
                country='$country' WHERE id=$id";

      $exec= mysqli_query($connection,$query);
  
      if($exec){
         header('location:crud-form.php');
      
      }else{
         $msg= "Error: " . $query . "<br>" . mysqli_error($connection);
         echo $msg;  
      }
}

// convert illegal input to legal input
function legal_input($value) {
  $value = trim($value);
  $value = stripslashes($value);
  $value = htmlspecialchars($value);
  return $value;
}
?>

 

DELETE – Delete Data from MySQL Table

In the case of DELETE, you have to delete the data from the MySQL table using PHP. therefore, configure the following steps

Write the following MySQL Query in PHP to delete the data from the MySQL table.

File Name – delete-script.php

<?php

include("database.php");
if(isset($_GET['delete'])){

    $id= $_GET['delete'];
  delete_data($connection, $id);

}

// delete data query
function delete_data($connection, $id){
   
    $query="DELETE from user_details WHERE id=$id";
    $exec= mysqli_query($connection,$query);

    if($exec){
      header('location:crud-form.php');
    }else{
        $msg= "Error: " . $query . "<br>" . mysqli_error($connection);
      echo $msg;
    }
}
?>

 

Suggestion:

I have explained the above CRUD Operations in PHP  in a simple. I hope, this tutorial will be helpful for you. If you have any questions, ask me directly through the comment box. I will definitely reply as soon as possible.

Thanks for giving time to this tutorial.

Categories PHP

Leave a Comment