Beta-Labs: CRUD Operations in PHP

What is CRUD

CRUD is an acronym for Create, Read, Update, and Delete. CRUD operations are basic data manipulation for the database. Most applications and projects perform some kind of CRUD functionality. Once you learn about these CRUD operations, you can use them for many projects. For example, if you learn how to create a student table with multiple columns, you can use a similar approach to create an employee table or customer table.

In this session, we'll create a simple PHP application to perform all these operations on a MySQL database table in one place.

Creating the Database Table

Execute the following SQL query to create a table named secetable inside your MySQL database. We will use this table for all of our future operations.

Example

CREATE TABLE secetable (
rollno INT NOT NULL PRIMARY KEY,
name VARCHAR(100),
class VARCHAR(255)

);

Note: Insert Some dummy data to perform following CRUD Operations.

Creating the Config File

After creating the table, we need to create a PHP script in order to connect to the MySQL database server. Let's create a file named "connection.php" and put the following code inside it. We'll later include this config file in other pages using the PHP include_once( ) function.
Connection.php

<?php  
 error_reporting(0);  
 $conn = mysqli_connect("localhost","root","","learnmysql");  
 if($conn)  
 {  
   echo "";  
 }  
 else  
 {  
   echo "Connection Failed";  
 }

?>

Creating the Create Page

In this section, we'll build the Create functionality of our CRUD application. Let's create a file named "createFirstRecord.php" and put the following code inside it.

createFirstRecord.php

<?php  
 include_once 'connection.php';  
 $query = "insert into secetable values(105,'Mohak','3E')";  
 $data = mysqli_query($conn,$query);  
 if($data){  
 echo "Record Inserted";  
 }  
 else{  
 echo "Record not Inserted";  
 }  
 ?>

Note: Here the Create CRUD operation is used to insert data into the database manually ( static data). But latter we use the form to insert records dynamically.

Inserting Records using Form

insertRecordForm.php

<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Insert Record</title>
</head>
<body>
   <form method="POST">
   <label for="">Rollno. </label>
   <input type="number" name="t1"><br><br>

   <label for="">Name </label>
   <input type="text" name="t2"><br><br>

   <label for="">Class </label>
   <input type="text" name="t3" ><br><br>

   <input type="submit" name="b1" value="Insert Record">
   </form>
</body>
</html>
<?php
include_once 'connection.php';
if(isset($_POST['b1']))
{
    $roll = $_POST['t1'];
    $name = $_POST['t2'];
    $class = $_POST['t3'];
    if($roll!="" and $name!="" && $class!="")
    {
    $query = "insert into secetable values('$roll','$name','$class')";
    $data = mysqli_query($conn,$query);
        if($data)
        {
            echo "<p style='color:green'>Record Inserted Succesfully <a href='http://localhost/phpCode/02-Classes/Section-3E/05-Database/read.php'>Click this to see updated Records</a>";
        }
        else
        {
            echo "Data not Inserted";
        }
}
    else{
    echo "<p style='color:red'> All Fields are required</p>";
}
}
?>

Creating the Read Page

Now it's time to build the Read functionality of our CRUD application. Let's create a file named "read.php" and put the following code inside it. It will simply retrieve the records from the secetable table based on the rollno attribute of the student.

read.php

<!DOCTYPE html>   
  <html lang="en">   
  <head>   
   <meta charset="UTF-8">   
   <meta name="viewport" content="width=device-width, initial-scale=1.0">   
   <meta http-equiv="X-UA-Compatible" content="ie=edge">   
   <title>Read Records</title>   
  </head>   
  <style>   
  td{   
   padding:10px;   
  }   
  </style>   
  <body>   
  <br>

<?php   
  include_once 'connection.php';   
  $query = "SELECT * FROM secetable";   
  $data = mysqli_query($conn,$query);   
  $totRec = mysqli_num_rows($data);   
  // echo $totRec;   
  if($totRec!=0)   
  {   
   ?>   
 <table>       <tr>   
     <th>RollNo.</th>   
     <th>Photo</th>   
     <th>Name</th>   
     <th>Class</th>   
     <th colspan="2">Operations</th>
   </tr>  
  <?php   
   while($result = mysqli_fetch_assoc($data))   
   {   
    echo "   
    <tr>   
     <td>".$result['rollno']."</td>   
     <td>".$result['name']."</td>   
     <td>".$result['class']."</td>   
     <a href='update.php?rn=$result[rollno]&nm=$result[name]&cl=$result[class]'>Edit</a> |    
     <a href='delete.php?rn=$result[rollno]' onclick = 'return DeleteRecord()'>Delete</a></h4></td>   
    </tr>   
    ";   
   }   
  }   
  else   
  {   
   echo "No Records found";   
  }   
  ?>   
   </table>

  <a href="http://localhost/phpCode/02-Classes/Section-3E/05-Database/InsertRecordForm.php">+ Add More Records</a>   
  <script>   
   function DeleteRecord()   
   {   
    return confirm("Do u want to delete");   
   }   
  </script>   
  </body>

  </html>



Create the Update Page
Similarly, we can build the Update functionality of our CRUD application. Let's create a file named "update.php" and put the following code inside it. It will update the existing records in the secetable table based the rollno attribute of the student.

update.php  
  <?php    
  include_once 'connection.php'    
  ?>    
  <!DOCTYPE html>  
 <html lang="en">   
  <head>   
   <meta charset="UTF-8">   
   <meta name="viewport" content="width=device-width, initial-scale=1.0">   
   <meta http-equiv="X-UA-Compatible" content="ie=edge">   
   <title>Insert Record</title>   
  </head>   
  <body>   
   <form action="" method="GET">   
   <label for="">Rollno</label>   
   <input type="number" name="t1" value= "<?php echo $_GET['rn']; ?>"><br></br>   
   <label for="">Name</label>   
   <input type="text" name="t2" value= "<?php echo $_GET['nm']; ?>"><br></br>   
   <label for="">Class</label>   
   <input type="text" name="t3" value= "<?php echo $_GET['cl']; ?>"><br></br>   
   <input type="submit" name="b1" Value="Update">   
   </form>   
  </body>   
  </html>   
  
<?php
  if(isset($_GET['b1']))   
  {   
    $roll = $_GET['t1'];   
    $name = $_GET['t2'];   
    $class = $_GET['t3'];   
    if($roll!="" and $name!="" && $class!="")   
    {   
     $query = "UPDATE secetable SET name='$name', class='$class' where rollno='$roll'";   
     $data = mysqli_query($conn,$query);   
      if($data)   
      {   
       echo "<p style='color:green'>Record Updated Succesfully <a href='http://localhost/phpCode/02-Classes/Section-  
         3E/05-Database/read.php'>Click this to see updated Records</a>";   
      }   
      else   
      {   
       echo "Record not Updated ";   
      }   
    }   
    else   
    {   
     echo "All Fields are required";   
    }    
  }   
  ?>  


Create the Delete Page

Finally, we will build the Delete functionality of our CRUD application. Let's create a file named "delete.php" and put the following code inside it. It will delete the existing records from the secetable table based the rollno attribute of the student.

delete.php  
  <?php   
  include_once 'connection.php';   
  $roll = $_GET['rn'];   
  $query = "delete from secetable where rollno='$roll '";   
  $data = mysqli_query($conn, $query);   
  if($data)   
  {   
   echo "<script>alert('Record Deleted')</script>";   
   ?>   
   <meta http-equiv="refresh" content="0; url=http://localhost/phpCode/02-Classes/Section-3E/05-Database/read.php">   
   <?php   
  }   
  else   
  {   
   echo " Delete Process Fail";   
  }   
  ?>

Pages

Tuesday, March 10, 2020

CRUD Operations in PHP

In this section, we'll build the Create functionality of our CRUD application. Let's create a file named "createFirstRecord.php" and put the following code inside it.

Inserting Records using Form

Creating the Read Page

Now it's time to build the Read functionality of our CRUD application. Let's create a file named "read.php" and put the following code inside it. It will simply retrieve the records from the secetable table based on the rollno attribute of the student.

Create the Update Page

Create the Delete Page

Finally, we will build the Delete functionality of our CRUD application. Let's create a file named "delete.php" and put the following code inside it. It will delete the existing records from the secetable table based the rollno attribute of the student.