PHP Script for CRUD Application

Date :06/12/2018

What is CRUD?

CRUD refers to the four basic types of Database operations: Create, Read, Update, Delete. Most applications and projects perform  CRUD functionality.

This is a important script for all PHP beginners. Once you learn about these CRUD operations, you can use them for many projects.

Executing a CRUD Script

Step by step procedure are as follows;

STEP 1:
  • In this script, I have taken a example as testimonial section for CRUD usage.
  • In a PHPMYADMIN  , create a database Name “testimonials
  • Create a table name as “users
  • Create a table structure of rows having  id , rating, clientname, reviews.  I have attached a snapshot below,

This image has an empty alt attribute; its file name is crud1-1024x291.png

STEP 2:

Create a connection.php file in your favorite text editor and execute the below script to make connection with your DB.

<?php

$databaseHost = 'localhost';
$databaseName = 'testimonials';
$databaseUsername = 'root';
$databasePassword = '';

$mysqli = mysqli_connect($databaseHost, $databaseUsername, $databasePassword, $databaseName); 
 
?>
Step 3:

Create add.php file and execute the below script for adding the Information and to insert a queries in to DB.

The programming script is with Enhanced CSS quality. Take a look on it and make use of it,

<html>
<style>
input[type=text] {
    color: #777;
    padding-left: 10px;
    margin: 10px;
    margin-top: 12px;
    margin-left: 18px;
    width: 290px;
    height: 35px;
    border: 1px solid #c7d0d2;
    border-radius: 2px;
    box-shadow: inset 0 1.5px 3px rgba(190, 190, 190, .4), 0 0 0 5px #f5f7f8;
}

  
   input[type=submit] {
    float: left;
    margin-right: 20px;
 
    width: 100px;
    height: 30px;
    font-size: 14px;
    font-weight: bold;
    color: #fff;
    background-color: #acd6ef; /*IE fallback*/
    background-image: -webkit-gradient(linear, left top, left bottom, from(#acd6ef), to(#6ec2e8));
    background-image: -moz-linear-gradient(top left 90deg, #acd6ef 0%, #6ec2e8 100%);
    background-image: linear-gradient(top left 90deg, #acd6ef 0%, #6ec2e8 100%);
    border-radius: 30px;
    border: 1px solid #66add6;
    box-shadow: 0 1px 2px rgba(0, 0, 0, .3), inset 0 1px 0 rgba(255, 255, 255, .5);
    cursor: pointer;
   }
</style>
<body>
  <a href="list.php"><input type="submit" value="<< Added " name="Go Back" /></a>
  <br/><br/>

  <form action="add.php" method="post" name="form1">
    <table width="25%" border="0">
      <tr> 
        <td><span style="color:blue">rating</span></td>
        <td><input type="text" name="rating"></td>
      </tr>
      <tr> 
        <td><span style="color:blue">clientname</span></td>
        <td><input type="text" name="clientname"></td>
      </tr>
      <tr> 
          <td><span style="color:blue">reviews</span></td>
        <td><input type="text" name="reviews"></td>
      </tr>
      <tr> 
        
        <td><input type="submit" name="Submit" value="Add >>"></td>
      </tr>
      
    </table>
  </form>
  
  <?php

  // Check If form submitted, insert form data into users table.
   if(isset($_POST['Submit'])) {
    $rating = $_POST['rating'];
    $clientname = $_POST['clientname'];
    $reviews = $_POST['reviews'];
    
    // include database connection file
    include_once("connection.php");
        
    // Insert user data into table
    $result = mysqli_query($mysqli, "INSERT INTO users(rating,clientname,reviews) VALUES('$rating','$clientname','$reviews')");
    
    
  }
  
     ?>
  
</body>
</html>


Once You execute this in XAMPP , the output will be like this.

STEP 4:

Create edit.php file and execute the below script .Once you edited, You will see changes in DB and you can modify the added contents.

&lt;?php
// include database connection file
include_once("connection.php");

// Check if form is submitted for user update, then redirect to homepage after update
if(isset($_POST['update']))
{	
	
	$id = $_POST['id'];
	
	$rating=$_POST['rating'];
	$clientname=$_POST['clientname'];
	$reviews=$_POST['reviews'];
		
	// update user data
	$result = mysqli_query($mysqli, "UPDATE users SET rating='$rating',clientname='$clientname',reviews='$reviews' WHERE id=$id");
	
	// Redirect to homepage to display updated user in list
	header("Location: list.php");
}
?&gt;
&lt;?php
// Display selected user data based on id
// Getting id from url
$id = $_GET['id'];

// Fetech user data based on id
$result = mysqli_query($mysqli, "SELECT * FROM users WHERE id=$id");

while($user_data = mysqli_fetch_array($result))
{
	$rating = $user_data['rating'];
	$clientname = $user_data['clientname'];
	$reviews = $user_data['reviews'];
	
}
?&gt;
&lt;html&gt;
&lt;head&gt;	
	&lt;title&gt;Edit User Data&lt;/title&gt;
&lt;/head&gt;
 &lt;style&gt;
    input[type=text] {
    color: #777;
    padding-left: 10px;
    margin: 10px;
    margin-top: 12px;
    margin-left: 18px;
    width: 290px;
    height: 35px;
    border: 1px solid #c7d0d2;
    border-radius: 2px;
    box-shadow: inset 0 1.5px 3px rgba(190, 190, 190, .4), 0 0 0 5px #f5f7f8;
}
  input[type=submit] {
    float: left;
    margin-right: 20px;
    width: 180px;
    height: 30px;
    font-size: 14px;
    font-weight: bold;
    color: #fff;
    background-color: #acd6ef; /*IE fallback*/
    background-image: -webkit-gradient(linear, left top, left bottom, from(#acd6ef), to(#6ec2e8));
    background-image: -moz-linear-gradient(top left 90deg, #acd6ef 0%, #6ec2e8 100%);
    background-image: linear-gradient(top left 90deg, #acd6ef 0%, #6ec2e8 100%);
    border-radius: 30px;
    border: 1px solid #66add6;
    box-shadow: 0 1px 2px rgba(0, 0, 0, .3), inset 0 1px 0 rgba(255, 255, 255, .5);
    cursor: pointer;
   }
   &lt;/style&gt;
   &lt;body&gt;
   
	&lt;a href="list.php"&gt;&lt;input type="submit" name="Submit" value="Home Listings"&gt;&lt;/a&gt;
	&lt;br/&gt;&lt;br/&gt;
	
	&lt;form name="update_user" method="post" action="edit.php"&gt;
		&lt;table border="0"&gt;
			&lt;tr&gt; 
				&lt;td&gt;&lt;span style="color:blue"&gt;rating&lt;/span&gt;&lt;/td&gt;
				&lt;td&gt;&lt;input type="text" name="rating" value=&lt;?php echo $rating;?&gt;&gt;&lt;/td&gt;
			&lt;/tr&gt;
			&lt;tr&gt; 
				&lt;td&gt;&lt;span style="color:blue"&gt;clientname&lt;/span&gt;&lt;/td&gt;
				&lt;td&gt;&lt;input type="text" name="clientname" value=&lt;?php echo $clientname;?&gt;&gt;&lt;/td&gt;
			&lt;/tr&gt;
			&lt;tr&gt; 
				&lt;td&gt;&lt;span style="color:blue"&gt;reviews&lt;/span&gt;&lt;/td&gt;
				&lt;td&gt;&lt;input type="text" name="reviews" value=&lt;?php echo $reviews;?&gt;&gt;&lt;/td&gt;
			&lt;/tr&gt;
			&lt;tr&gt;
				&lt;td&gt;&lt;input type="hidden" name="id" value=&lt;?php echo $_GET['id'];?&gt;&gt;&lt;/td&gt;
				&lt;td&gt;&lt;input type="submit" name="update" value="Update"&gt;&lt;/td&gt;
			&lt;/tr&gt;
		&lt;/table&gt;
	&lt;/form&gt;
&lt;/body&gt;
&lt;/html&gt;


STEP 5:

Create a file called  delete.php  , This will delete the content and directly delete in DB.

&lt;?php
// include database connection file

include_once("connection.php");

// Get id from URL to delete that user
$id = $_GET['id'];

// Delete user row from table based on given id
$result = mysqli_query($mysqli, "DELETE FROM users WHERE id=$id");

// After delete redirect to Home, so that latest user list will be displayed.
header("Location:list.php");
?&gt;

The output will look like this,

You can Edit / Delete a content  Both in UI and DB

Step 6:

I named a file as “list.php” , this is index  page for a total crud.

&lt;?php
// Create database connection using config file
include_once("connection.php");

// Fetch all users data from database
$result = mysqli_query($mysqli, "SELECT * FROM users ORDER BY id DESC");
?&gt;

&lt;html&gt;
&lt;head&gt;    
    &lt;title&gt;Homepage&lt;/title&gt;
&lt;/head&gt;
&lt;style&gt;
&lt;style&gt;
#rating {
    font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
    border-collapse: collapse;
    width: 100%;
}

#rating td, #rating th {
    border: 1px solid #ddd;
    padding: 8px;
}

#rating tr:nth-child(even){background-color: #f2f2f2;}

#rating tr:hover {background-color: #ddd;}

#rating th {
    padding-top: 12px;
    padding-bottom: 12px;
    text-align: left;
    background-color: blue;
    color: white;
}

input[type=submit] {
    float: left;
    margin-right: 20px;
    width: 180px;
    height: 30px;
    font-size: 14px;
    font-weight: bold;
    color: #fff;
    background-color: #acd6ef; /*IE fallback*/
    background-image: -webkit-gradient(linear, left top, left bottom, from(#acd6ef), to(#6ec2e8));
    background-image: -moz-linear-gradient(top left 90deg, #acd6ef 0%, #6ec2e8 100%);
    background-image: linear-gradient(top left 90deg, #acd6ef 0%, #6ec2e8 100%);
    border-radius: 30px;
    border: 1px solid #66add6;
    box-shadow: 0 1px 2px rgba(0, 0, 0, .3), inset 0 1px 0 rgba(255, 255, 255, .5);
    cursor: pointer;
   }
&lt;/style&gt;
&lt;/style&gt;

&lt;body&gt;
&lt;a href="add.php"&gt;&lt;input type="submit" name="Submit" value="Add Ratings"&gt;&lt;/a&gt;&lt;br/&gt;&lt;br/&gt;

   
	&lt;table id = "rating"&gt;

    &lt;tr&gt;
        &lt;th&gt;rating&lt;/th&gt; &lt;th&gt;clientname&lt;/th&gt; &lt;th&gt;reviews&lt;/th&gt; &lt;th&gt;Update&lt;/th&gt;
    &lt;/tr&gt;
    &lt;?php  
    while($user_data = mysqli_fetch_array($result)) {         
        echo "&lt;tr&gt;";
        echo "&lt;td&gt;".$user_data['rating']."&lt;/td&gt;";
        echo "&lt;td&gt;".$user_data['clientname']."&lt;/td&gt;";
        echo "&lt;td&gt;".$user_data['reviews']."&lt;/td&gt;";    
        echo "&lt;td&gt;&lt;a href='edit.php?id=$user_data[id]'&gt;Edit&lt;/a&gt; | &lt;a href='delete.php?id=$user_data[id]'&gt;Delete&lt;/a&gt;&lt;/td&gt;&lt;/tr&gt;";        
    }
    ?&gt;
    &lt;/table&gt;
&lt;/body&gt;
&lt;/html&gt;

In the above example, the effective usage of  crud is explained clearly, you can modify the script according to your  application and make benefit of it. 

Leave a Reply