Insert, Delete and Update data in Php using MySql.

POSTED

AUTHOR: VARUN SINGH

Today, I am going to explain how to Insert, delete and update data in php using MySql. To create dynamic website we need these these command to manage data on web. There are many command like INSERT, DELETE, UPDATE to manage the data on website. Before reading this article firstly you will have to learn these command. Here I am going to explain all these command one by one.

INSERT: The INSERT INTO statement is used to add new records to a MySQL table:

INSERT INTO table_name (column1, column2, column3,...) 
VALUES (value1, value2, value3,...)

DELETE: The DELETE statement is used to delete records from a table:

DELETE FROM table_name
WHERE some_column = some_value
>

UPDATE: The UPDATE statement is used to update existing records in a table:

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Many users always search this type of tutorials on the web. But they don't find the best and simple example similar to this tutorial. Here I am going to write a tutorial to insert, delete and update data in php using MySql. This tutorial is originally written by me. Now I am going to display the output of our page.

Insert, Delete, Update data in PHP:

Here is the output of the page .

insert-delete-update-data-php


download view


Implementation of code:


config.php: This is the php file is used to connect to MySql database. Here is the code of this file.

<?php
$con=mysqli_connect("localhost","root","","employee");
mysqli_set_charset($con,'utf8');
?>

users.sql: This is the sql file is use to import to create users table. Here is the code of this file.

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `fname` varchar(20) NOT NULL,
  `lname` varchar(20) NOT NULL,
  `email` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `fname`, `lname`, `email`) VALUES
(1, 'Varun', 'Singh', 'varun@gmail.com'),
(2, 'Happy', 'Singh', 'happy@gmail.com');


index.php: This is the html file for front-end design. Here is the code of this file.

<html>
	<title>Insert, Delete and update data using mysql in php - coderglass</title>
	<head> 
		<link type="text/css" rel="stylesheet" href="style.css" /> 


</head>

<body>

<div class="headerx">
	</div>
	<div class="header">
	<center>
		<div id="logo" > <img  src="logo.png" /> </div>
	</center>	
	</div>
	<div class="main">
	     <center> 
		     <div class="box">
			 <div id="info2"> Insert, Delete, Edit Data using mysql in PHP</div><br>
		      <div id="info1"> Insert Record</div><br>
			  
			 <div style="float:left; margin-left:50px;">
			 <form method="post" action="insert.php" enctype="multipart/form-data">
			  <input type="text" name="f-name" placeholder="First Name" style="width:150px; height:30px;" />
			  <input type="text" name="l-name" placeholder="Last Name" style="width:150px; height:30px;" />
			  <input type="text" name="email" placeholder="Email" style="width:220px; height:30px;" />
			  <input class="button2" type="submit" name="insert" value="INSERT" />
			  </form>
			  </div><br><br><br>
			   <div id="info1">Display Record</div><br>
			   <h2 style="color:blue;"><?php echo @$_GET['deleted']; ?> </h2>
			   <h2 style="color:blue;"><?php echo @$_GET['inserted']; ?> </h2>
			   <h2 style="color:blue;"><?php echo @$_GET['updated']; ?> </h2>
			  <div style="float:left; margin-left:50px; font-weight:bold;"><p><span style="font-size:12px;  ">
			  
			  <li>FISRT NAME</li>
			  <li>LAST NAME</li> 
			  <li>EMAIL</li></span></p></div>
			  <div style="float:left; margin-left:50px;"><p><span style="font-size:12px; ">
			  
			  <?php 
		     include("config.php");
		     $i=1;
		    
			$query = "select * from users order by 1 DESC LIMIT 0,3";
			$run = mysqli_query($con,$query);
			while($row = mysqli_fetch_array($run)){
				$id=$row['id'];
				$username=$row['username'];
				$fname=$row['fname'];
				$lname=$row['lname'];
				$email=$row['email'];
				
	          ?>
			  
			  <li><?php echo $fname; ?></li>
			  <li><?php echo $lname; ?></li> 
			  <li><?php echo $email; ?></li>
			  <a href="edit.php?edit=<?php echo $id; ?>"><input class="button3" type="button" value="EDIT" /></a>
			  <a href="delete.php?del=<?php echo $id; ?>"><input class="button4" type="button" value="DELTE" /></a><br>
			<?php } ?>
			  </span></p></div><br><br>
			  
			  
			  
		     </div>
	     </center>
		
		 
		 <div class="footer">
		 <center>
		    <div class="box3">
			 
			  <p><span style="font-size:12px;"><li>Coder Glass &copy; 2016</li>
			  <li>Designed by: VARUN SINGH</li>
			  </span></p>
			  
			</div>
		 </center>
		 
		 </div>
		 
	</div>
	
	

</body>
</html>


delete.php: This is the php file is used to delete data from database. Here is the code of this file.

<?php
	include("config.php");
	$delete_id = $_GET['del'];
	$delete_query = "delete from users where id='$delete_id'";
	
	if(mysqli_query($con,$delete_query)){
		
		echo "<script>window.open('index.php?deleted=A Record has been deleted','_self')</script>";
		
	}
?>


edit.php: This is the php file is used to edit data from database. Here is the code of this file.

<html>
	<title>Insert, Delete and update data in php - coderglass</title>
	<head> 
		<link type="text/css" rel="stylesheet" href="style.css" /> 
		<meta name="Description" content="how to Insert, Delete and update data in php - coderglass"/>
		
      
	</head>
<body>
<script>
  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
  })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

  ga('create', 'UA-73364464-1', 'auto');
  ga('send', 'pageview');

   </script>
	<div class="headerx">
	</div>
	<div class="header">
	<center>
		<div id="logo" > <img  src="logo.png" /> </div>
	</center>	
	</div>
	<div class="main">
	     <center> 
		     <div class="box-edit">
			 <div id="info2"> Update Your Record</div><br>
			 <?php
	
				include("config.php");
				$edit_id = @$_GET['edit'];
				$query = "select * from users where id='$edit_id'";
				$run = mysqli_query($con,$query);
	
				while($row = mysqli_fetch_array($run)){
				$id=$row['id'];
				$fname=$row['fname'];
				$lname=$row['lname'];
				$email=$row['email'];
				}
			?>
		     
			  
			 <div style="float:left; margin-left:50px;">
			 <form method="post" action="edit.php?edit_form=<?php echo $id; ?>" enctype="multipart/form-data">
			  <input type="text" name="f-name" placeholder="First Name" value="<?php echo $fname; ?>" style="width:150px; height:30px;" />
			  <input type="text" name="l-name" placeholder="Last Name" value="<?php echo $lname; ?>" style="width:150px; height:30px;" />
			  <input type="text" name="email" placeholder="Email" value="<?php echo $email; ?>" style="width:220px; height:30px;" />
			  <input class="button2" type="submit" name="update" value="UPDATE" />
			  </form>
			  </div><br><br>
			  
			  <?PHP
	if(isset($_POST['update'])){
		$update_id = $_GET['edit_form'];
		$update_fname = $_POST['f-name'];
	    $update_lname = $_POST['l-name'];
		$update_email = $_POST['email'];
		
		
		
		$update_query = "update users set fname='$update_fname', lname='$update_lname', email='$update_email' where id='$update_id'";
		
		if(mysqli_query($con,$update_query)){
		
		echo "<script>window.open('index.php?updated= Record has been updated','_self')</script>";
		}
	
	}


?>
			  
			  
		     </div>
	     </center>
		
		 
		 <div class="footer">
		 <center>
		    <div class="box3">
			 
			  <p><span style="font-size:12px;"><li>Coder Glass &copy; 2016</li>
			  <li>Designed by: VARUN SINGH</li>
			  </span></p>
			  
			</div>
		 </center>
		 
		 </div>
		 
	</div>
	
	

</body>
</html>

insert.php: This is the php file is used to insert data into database. Here is the code of this file.

<?php
include("config.php");

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

		$fname = $_POST['f-name'];
		$lname = $_POST['l-name'];
		$email = $_POST['email'];
		
		
		if ($fname=='' or $lname=='' or $email==''){
		echo "<script>alert('Any field is empty.')</script>";
		exit();
		}
		
		
		$query = "INSERT INTO users (fname, lname, email) 
		values ('$fname','$lname','$email')";
		mysqli_set_charset($con,'utf8');
		if(mysqli_query($con,$query)){
		echo "<script>window.open('index.php?inserted=Record is inserted','_self') </script>";
		}
	
     }

?>

style.css: This file is used to set the every body part and other things on perfect postion.

body{ 
background:linear-gradient(to bottom right, white, #bcbdc4);
height:620px;
}
.headerx{
	position:absolute;
	width:100%;
	height:70px;
	top:0px;
	left:0px;
	color:white;
	z-index:7;
	font-family:verdana;
	-webkit-box-shadow: 0 3px 8px rgba(0, 0, 0, .25);
	
}
.header{
    position:absolute;
	width:100%;
	height:70px;
	background:#e1e3e4;
	top:0px;
	left:0px;
	color:white;
	z-index:7;
	font-family:verdana;
	
}
#logo{
	height:50px;
	width:200px;
	
	top:10px;
	
}
#logo img{
	height:50px;
	width:200px;
}

 .main{
	position:absolute;
	width:100%;
	height:500px;
	background-image:url("backgroung.png");
    background-repeat:no-repeat;
    background-size:cover;
	top:70px;
	left:0px;
	color:white;
	z-index:20;
	font-family:verdana;
	 
 }
 .box{
	 margin-top:20px;
	 
	 height:420px;
	 width:850px;
	 background:#efefef;
	 padding-top:15px;
	 padding-left:28px;
	 padding-right:28px;
	 padding-bottom:25px;
 }
 .box-edit{
	 margin-top:130px;
	 
	 height:200px;
	 width:850px;
	 background:#efefef;
	 padding-top:15px;
	 padding-left:28px;
	 padding-right:28px;
	 padding-bottom:25px;
 }
 .box li{
	 float:left;
	 margin-right:20px;
	 font-size:12px;
	 color:black;
	 width:150px;
 }
 #info1{
	 text-align:center;
	 
	 color:black;
	 
	 font-family:arial;
	 font-size:20px;
 }
 #info2{
	 text-align:center;
	 
	 color:black;
	 
	 font-family:arial;
	 font-size:30px;
 }
  #info3{
	 text-align:left;
	 
	 color:#888889;
	 
	 font-family:arial;
	 font-size:12px;
 }
 #info4{
	 text-align:center;
	 
	 color:#888889;
	 
	 font-family:arial;
	 font-size:12px;
 }
 
 .button2{ width:150px; height:30px; 
 top:625px; background:green; font-family:verdana;
 font-size:14px; font-weight:bold; color:white; border-radius:5px 5px 5px 5px;
 border-width: 1px; border-style:solid; border-color: gray;
 cursor:pointer; outline:none; }
 
 .button3{ width:100px; height:20px; 
 top:625px; background:blue; font-family:verdana;
 font-size:12px; font-weight:bold; color:white; border-radius:5px 5px 5px 5px;
 border-width: 1px; border-style:solid; border-color: gray;
 cursor:pointer; outline:none; }
 
 .button4{ width:100px; height:20px; 
 top:625px; background:red; font-family:verdana;
 font-size:14px; font-weight:bold; color:white; border-radius:5px 5px 5px 5px;
 border-width: 1px; border-style:solid; border-color: gray;
 cursor:pointer; outline:none; }
 
 
 .footer{
	position:absolute;
	width:100%;
	height:80px;
	background:#000;
    top:500px;
	left:0px;
	color:white;
	z-index:20;
	font-family:verdana;
	 
 }
 
  .box3{
	 
	 color:white;
	 height:50px;
	 width:1000px;
	 
	 padding-top:10px;
	 padding-left:10px;
	 padding-right:10px;
	 padding-bottom:20px;
 }
 .box3 li{
	 float:left;
	 
	 margin-right:100px;
	 font-size:12px;
 }

The End:

Thank you for reading this article, and if you have any problem, have a another better useful solution about this article, please write message in the comment section. We will be very happy to hear that.

If you like my tutorial, please follow us on facebook, Google + and Twitter. I need your support to continue.

Advertise Here

Comments: