Insert, Update and Delete Operations using PHP + MySQL

Today, we are going to talk about CRUD Operation which means CREATE, READ, UPDATE and DELETE. It is the very first and basic thing which we need to deal with if we are thinking to create any application which will save users data into database. So, we will be using PHP as a server side scripting language and an open source database as MySQL for storing our data. By integrating both we will write a code that will perform the CRUD Operations. Before starting I am hoping that you are familiar with PHP and MySQL.

INSERT_UPDATE_DELETE_PHP_MYSQL



First, We will create a custome table, name "MyData" for storing our data

mysql_table


Creating Database and Table

First I have created a Database with name "inspire2code" and then i have created a "MyData" table in that database

CREATE TABLE `inspire2code`.`wall`
  ( `ID` INT(100) NOT NULL AUTO_INCREMENT ,
 `Name` VARCHAR(200) NOT NULL ,
 `Content` VARCHAR(200) NOT NULL ,
 `ProfileImg` VARCHAR(200) NOT NULL ,
  PRIMARY KEY (`ID`)) ENGINE = MyISAM;


index.php : We are Performing Insert & View Operation on the same page

Post Insertion Design


<div class="Main">
<h1 style="font-size:28px;" align="center">Implementing CRUD Operation on Wall Feed</h1>
<div class="InnerMain">
<div class="ProfileDiv" ><img src="profile.jpg" class="Profile" /></div>
<div class="ContentDiv">
<form method="post" action="">
<textarea class="TextArea" name="Content"></textarea>
<input type="submit" class="btn btn-outline-info" style="margin-top:10px;" name="Post" value="POST" />
</form>
</div>
</div>
</div>

insert
Creating Database Connection in MySQL

We are creating a separate file for connection and then we will import in index.php using

<?php require('DbConfig.php'); ?>

DbConfig.php

 <?php

//defining name
define('SERVER', 'localhost');
define('USER', 'root');
define('PASSWORD', '');
define('DB', 'inspire2code');

//Establishing connection to mysql
$link = mysqli_connect(SERVER, USER, PASSWORD, DB)
        or die("Erro-> ".mysqli_connect_error());


?>   
PHP Codes that will Insert our data into Database

These codes will come in action when you trigger the POST Button.

Line no. 3 : We are importing the database connection so mysql commands such as INSERT, UPDATE, DELETE can perform their task on database to which we are connected
Line no. 5 : We are checking if there is something posted via POST Method of form <form method="POST"></form> and $_POST['attribute_name'] sense the data with the attribute name which is posted via POST method
Line no. 8 : Here we are using mysqli_query("Connection","String); to avoid SQL Injection and hacking attempts

DbConfig.php

<?php
require('DbConfig.php');

if(isset($_POST['Post'])) {

$Content=trim($_POST['Content']);
$Content = mysqli_escape_string($link,$Content);
$Name="Rohit Sharma";
$ProfileImg = "Profile.jpg";
if(!empty($Content)) {
$Query= "INSERT INTO `wall` SET
         Name       = '$Name',
		 Content    = '$Content',
		 ProfileImg = '$ProfileImg'";

$Execute = mysqli_query($link,$Query);

if($Execute==true)
{

//Do Something

	}
                      }
                             }

?>  

index.php : Now, We are Performing View Operation on the same page

Post View Design integrated with PHP Code

Here we are using while {} loop to display each record from $Result array.

<a class="update" href="update.php?id=<?php echo $Result['ID']; ?>">Edit</a> <a href="delete_post.php?id=<?php echo $Result['ID']; ?>">Delete</a>

We are sending record ID on the update.php & delete_post.php via Get Method to Edit & Delete the records.

<div class="display">
<h4>Showing inserted content</h4>
<?php
$Query  = "SELECT * FROM `wall` ORDER BY ID DESC";
$Execute = mysqli_query($link,$Query);
while($Result = mysqli_fetch_assoc($Execute))
{

?>
<div class="subdiv">
<div class="InnerMain" >
<div class="ProfileDiv" ><img src="<?php echo $Result['ProfileImg']; ?>"  class="ProfileWall" /></div>
<div class="ContentDiv border" >

<div class="container"><?php echo $Result['Content']; ?></div>
<div style="margin-top:27px;">
<a class="update" href="update.php?id=<?php echo $Result['ID']; ?>">Edit</a>
<a  href="delete_post.php?id=<?php echo $Result['ID']; ?>">Delete</a></div>
</div>

</div>
</div>
<?php } ?>
</div>

insert
update.php

  <?php
if(isset($_POST['Update'])) {

$Content=trim($_POST['Content']);

$ID = trim($_GET['id']);
$Content= mysqli_escape_string($link,$Content);
if(!empty($Content)) {
$Query= "UPDATE `wall` SET
         Content    = '$Content'
		 WHERE ID='$ID'";


$Execute = mysqli_query($link,$Query);

if($Execute==true)
{

	header('location:index.php');

	}
                   }
                               }

?>
  
insert
delete_post.php

<?php
if(isset($_GET['id'])) {


$ID = trim($_GET['id']);
$ID = mysqli_escape_string($link,$ID);
if(!empty($ID) {
$Query= "DELETE FROM `wall` WHERE ID='$ID'";


$Execute = mysqli_query($link,$Query);

		}
          	        }

 ?>
insert

People Reaction : 2

Sandeep
15th Oct 16 AT 11:55 AM Sandeep
Hello Rohit, you need to check something at the PHP codes. you are allowing to post the content even the text area is left blank. you should go for
if(!empty($content) && $content!=' ')
{
//Insert into DB
}
Rohit Sharma
15th Oct 16 AT 11:55 AM Rohit Sharma
Bugs Fixed. Thank you Sandeep for observing this bugs.
Rohit Sharma
Name : Email : Website :
Rohit Sharma

Hi There! My name is Rohit and I am working in the one of MNC as Web Apps developer. I have been in this tech industry for last 3.6 years. This blog is just a part of my career journey.
Ready to make new mistakes without repeating the previous ones.

"All life is an experiment. The more experiments you make the better"


© 2020 WriteSomeCode. All Right Reserved. A Rohit Sharma Blog. Creative Commons License licensed under a Creative Commons Attribution 4.0 International License