Basic insert, view, edit, delete and update using PHP and Mysql
Dear Friends today i going to post one of old topic in php and mysql, here i'm going to post Insert data through form, and fetch ,view the data from database, and also Edit, Delete and Update with detailed explanation. i know mysql is depreciated, even most of the people are using mysql so now i'm going to use mysql and i'll update with pdo later, now i'm going to explain the basics of php mysql functions, how to insert, fetch, delete, update like all operations.
and know we create empty files and named mention as..
1.index.php
2.add.php
2.add.php
3.viewadd.php
4.edit.php
5.delete.php
create database named as
1.index.php
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>profile</title>
<style type="text/css">
#tableback
{
background:#fff;
width:500px;
height:300px;
border-radius:5px;
color:#333;
margin:50px 0px 0px 400px;}
{
background:#fff;
width:500px;
height:300px;
border-radius:5px;
color:#333;
margin:50px 0px 0px 400px;}
#btn{
background-image:none transparent;
border:1px solid transparent;
padding:10px 15px 10px;
border-radius:4px;
border:1px solid #ccc;
color:#000;
background:#fff;
}
#btn:hover
{
background-color:#F5F5F5;
}
{
background-color:#F5F5F5;
}
</style>
</head>
<body>
<p>Profile>></p>
<table id="tableback" width="700" cellpadding="4" cellspacing="0" border="0" align="center">
<tr>
<td>
<table width="100%" cellpadding="4" cellspacing="0">
<tr><td colspan="3">DTH SERVICE NO.</td>
<form name="dn" action="add.php" method="post" >
<tr>
<td width="100">Username</td>
<td width="3">:</td>
<td width="592"><p>It's automatically taken your username</p></td>
</tr>
<tr>
<td>DTH service </td>
<td>:</td>
<td><input type="checkbox" name="public[]" value="Sun Direct" />Sun Direct<br />
<input type="checkbox" name="public[]" value="Big Tv" />Big TV<br />
<input type="checkbox" name="public[]" value="Reliance" />Reliance<br />
<input type="checkbox" name="public[]" value="Videocon" />Videocon<br />
<input type="checkbox" name="public[]" value="Dish Tv" />Dish Tv</td>
</tr>
<tr>
<td>DTH No</td>
<td>:</td>
<td><input type="text" name="dthno" size="40"/></td>
</tr>
<tr>
<td>Mobileno</td>
<td>:</td>
<td><input type="text" name="mobileno" size="40" /></td></tr>
<tr>
<td>Amount Rs</td>
<td>:</td>
<td><input type="text" name="amount" size="40" /></td></tr>
<tr>
<td></td>
<td></td>
<td><input type="hidden" name="date" /><input type="submit" id="btn" name="submit" value="Add to admin profile" /></td></tr>
</form></table>
</td></tr></table>
2.add.php
<?php
session_start();
if(isset($_SESSION['username'])){
$username=$_SESSION['username'];
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("myproject") or die("cannot connect to db" .mysql_error());
$query=mysql_query("select * from users where email='$username'");
$rec1=mysql_fetch_array($query);
$userid=$rec1[0];
$user=$rec1[3];
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>view add</title>
</head>
<body>
<?php
if(isset($_POST['submit'])){
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("myproject") or die("cannot connect to db" .mysql_error());
foreach($_POST['public'] as $value){
}
$dthno=mysql_real_escape_string($_POST['dthno']);
$mobileno=($_POST['mobileno']);
$amount=mysql_real_escape_string($_POST['amount']);
$date=$_POST['date'];
$query1="insert into profile(userid,username,dthservice,dthno,mobileno,amount,date) values('$userid','$user','$value','$dthno','$mobileno','$amount','$date')";
$result=mysql_query($query1);
if($result>0){
echo '<script> alert("inserted successfully");</script>';
echo '<script> window.location.assign("index.php");</script>';
}else{
echo '<script>alert("problem in tables");</script>';
}
}
else{
echo '<script>alert("problem in inserted");</script>';
echo '<script>window.location.assign("index.php");</script>';
}
?>
</body>
</html>
2.viewadd.php
<?php
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("myproject") or die("cannot connect db" .mysql_error());
if(isset($_SESSION['username'])){
$username=$_SESSION['username'];
$query=mysql_query("select * from users where email='$username'");
$rec=mysql_fetch_array($query);
$userid=$rec[0];
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>view add</title>
<style type="text/css">
#tablerow{background-color:#fff; border:2px solid #09F; margin:60px 0px 0px 270px; color:#000;}
</style>
</head>
<body>
<table id="tablerow" border="2px" align="center" cellpadding="6" cellspacing="0" >
<tr>
<th>POSTID</th>
<th>USERID</th>
<th>USERNAME</th>
<th>DTHNAME</th>
<th>DTHNO</th>
<th>MOBILENO</th>
<th>AMOUNT</th>
<th>EDIT</th>
<th>DELETE</th>
</tr>
<?php
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("myproject") or die("cannot connect db" .mysql_error());
$query2=mysql_query("select * from profile where userid='$userid'");
while($row=mysql_fetch_array($query2)){
$id=$row[0];
$userid=$row[1];
$username=$row[2];
$dthname=$row[3];
$dthno=$row[4];
$mobileno=$row[5];
$amount=$row[6];
print '<tr>';
print '<td align="center">'.$id. '</td>';
print '<td align="center">'.$userid. '</td>';
print '<td align="center">'.$username. '</td>';
print '<td align="center">'.$dthname. '</td>';
print '<td align="center">'.$dthno. '</td>';
print '<td align="center">'.$mobileno.'</td>';
print '<td align="center">'.$amount.'</td>';
print '<td align="center"><a href="edit1.php?userid='.$userid.'">edit</a></td>';
print '<td align="center"><a href="#" onclick="myfunction('.$id.')">delete</a></td>';
print '</tr>';
}
?></table>
<script type="application/javascript">
function myfunction(id){
var r=confirm("Are u sure delete this record y/n?");
if(r==true){
window.location.assign("delete.php?id=" +id);
}
}
</script>
<?php
session_start();
if(isset($_SESSION['username'])){
$username=$_SESSION['username'];
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("myproject") or die("cannot connect to db" .mysql_error());
$query=mysql_query("select * from users where email='$username'");
$rec1=mysql_fetch_array($query);
$userid=$rec1[0];
$user=$rec1[3];
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>view add</title>
</head>
<body>
<?php
if(isset($_POST['submit'])){
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("myproject") or die("cannot connect to db" .mysql_error());
foreach($_POST['public'] as $value){
}
$dthno=mysql_real_escape_string($_POST['dthno']);
$mobileno=($_POST['mobileno']);
$amount=mysql_real_escape_string($_POST['amount']);
$date=$_POST['date'];
$query1="insert into profile(userid,username,dthservice,dthno,mobileno,amount,date) values('$userid','$user','$value','$dthno','$mobileno','$amount','$date')";
$result=mysql_query($query1);
if($result>0){
echo '<script> alert("inserted successfully");</script>';
echo '<script> window.location.assign("index.php");</script>';
}else{
echo '<script>alert("problem in tables");</script>';
}
}
else{
echo '<script>alert("problem in inserted");</script>';
echo '<script>window.location.assign("index.php");</script>';
}
?>
</body>
</html>
<?php
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("myproject") or die("cannot connect db" .mysql_error());
if(isset($_SESSION['username'])){
$username=$_SESSION['username'];
$query=mysql_query("select * from users where email='$username'");
$rec=mysql_fetch_array($query);
$userid=$rec[0];
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>view add</title>
<style type="text/css">
#tablerow{background-color:#fff; border:2px solid #09F; margin:60px 0px 0px 270px; color:#000;}
</style>
</head>
<body>
<table id="tablerow" border="2px" align="center" cellpadding="6" cellspacing="0" >
<tr>
<th>POSTID</th>
<th>USERID</th>
<th>USERNAME</th>
<th>DTHNAME</th>
<th>DTHNO</th>
<th>MOBILENO</th>
<th>AMOUNT</th>
<th>EDIT</th>
<th>DELETE</th>
</tr>
<?php
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("myproject") or die("cannot connect db" .mysql_error());
$query2=mysql_query("select * from profile where userid='$userid'");
while($row=mysql_fetch_array($query2)){
$id=$row[0];
$userid=$row[1];
$username=$row[2];
$dthname=$row[3];
$dthno=$row[4];
$mobileno=$row[5];
$amount=$row[6];
print '<tr>';
print '<td align="center">'.$id. '</td>';
print '<td align="center">'.$userid. '</td>';
print '<td align="center">'.$username. '</td>';
print '<td align="center">'.$dthname. '</td>';
print '<td align="center">'.$dthno. '</td>';
print '<td align="center">'.$mobileno.'</td>';
print '<td align="center">'.$amount.'</td>';
print '<td align="center"><a href="edit1.php?userid='.$userid.'">edit</a></td>';
print '<td align="center"><a href="#" onclick="myfunction('.$id.')">delete</a></td>';
print '</tr>';
}
?></table>
<script type="application/javascript">
function myfunction(id){
var r=confirm("Are u sure delete this record y/n?");
if(r==true){
window.location.assign("delete.php?id=" +id);
}
}
</script>
.edit.php
<?php
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("myproject") or die("cannot connect db" .mysql_error());
if(isset($_SESSION['username'])){
$username=$_SESSION['username'];
$query=mysql_query("select * from users where email='$username'");
$rec=mysql_fetch_array($query);
$userid=$rec[0];
}
$id=$_REQUEST['userid'];
echo $id;
$query1=mysql_query("select * from profile where userid='".$id."'");
$row=mysql_fetch_assoc($query1);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
body{background:#ccc;}
#tablerow
{
background-color:#fff;
border:2px solid #09F;
border-radius:4px;
margin:60px 0px 0px 450px;
color:#000;
}
#tablerow1{
background-color:#fff;
border:2px solid #09F;
margin:60px 0px 0px 450px; color:#000;
}
#btn{
background-image:none transparent;
border:1px solid transparent;
padding:10px 15px 10px;
border-radius:4px;
border:1px solid #ccc;
color:#000;
background:#fff;
}
#btn:hover{background-color:#F5F5F5;}
</style>
</head>
<body>
<?php
if($_SERVER['REQUEST_METHOD']=="POST")
{
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("myproject") or die("cannot connect db" .mysql_error());
$dthno=$_POST['dthno'];
$mobileno=$_POST['mobileno'];
$amount=$_POST['amount'];
foreach($_POST['public'] as $list)
{
}
mysql_query("update profile set dthservice='$list',dthno='$dthno',mobileno='$mobileno',amount='$amount' where userid='".$id."'");
}
?>
<table id="tablerow" width="400" cellpadding="0" border="0">
<tr>
<td colspan="3">DTH Service
<form action="" method="post">
<table width="100%" cellpadding="0" >
<tr>
<td width="100">dthname</td>
<td width="3">:</td>
<td width="287"><input type="checkbox" name="public[]" value="Sun Direct" />Sun Direct<br />
<input type="checkbox" name="public[]" value="Big Tv" />Big TV<br />
<input type="checkbox" name="public[]" value="Reliance" />Reliance<br />
<input type="checkbox" name="public[]" value="Videocon" />Videocon<br />
<input type="checkbox" name="public[]" value="Dish Tv" />Dish Tv</td></tr>
<tr>
<td>DTH No</td>
<td>:</td>
<td><input type="text" name="dthno" size="40" value="<?php echo $row['dthno']; ?>"/></td>
</tr>
<tr>
<td>MobileNo</td>
<td>:</td>
<td><input type="text" name="mobileno" size="40" value="<?php echo $row['mobileno']; ?>"/></td>
</tr>
<tr>
<td>Amount</td>
<td>:</td>
<td><input type="text" name="amount" size="40" value="<?php echo $row['amount']?>"/></td>
</tr>
<tr>
<td></td>
<td></td>
<td><input type="submit" id="btn" name="submit" value="update list" /></td></tr>
</td></tr></table>
</form></td></tr>
</table>
</body>
</html>
<?php
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("myproject") or die("cannot connect db" .mysql_error());
if(isset($_SESSION['username'])){
$username=$_SESSION['username'];
$query=mysql_query("select * from users where email='$username'");
$rec=mysql_fetch_array($query);
$userid=$rec[0];
}
$id=$_REQUEST['userid'];
echo $id;
$query1=mysql_query("select * from profile where userid='".$id."'");
$row=mysql_fetch_assoc($query1);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
body{background:#ccc;}
#tablerow
{
background-color:#fff;
border:2px solid #09F;
border-radius:4px;
margin:60px 0px 0px 450px;
color:#000;
}
#tablerow1{
background-color:#fff;
border:2px solid #09F;
margin:60px 0px 0px 450px; color:#000;
}
#btn{
background-image:none transparent;
border:1px solid transparent;
padding:10px 15px 10px;
border-radius:4px;
border:1px solid #ccc;
color:#000;
background:#fff;
}
#btn:hover{background-color:#F5F5F5;}
</style>
</head>
<body>
<?php
if($_SERVER['REQUEST_METHOD']=="POST")
{
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("myproject") or die("cannot connect db" .mysql_error());
$dthno=$_POST['dthno'];
$mobileno=$_POST['mobileno'];
$amount=$_POST['amount'];
foreach($_POST['public'] as $list)
{
}
mysql_query("update profile set dthservice='$list',dthno='$dthno',mobileno='$mobileno',amount='$amount' where userid='".$id."'");
}
?>
<table id="tablerow" width="400" cellpadding="0" border="0">
<tr>
<td colspan="3">DTH Service
<form action="" method="post">
<table width="100%" cellpadding="0" >
<tr>
<td width="100">dthname</td>
<td width="3">:</td>
<td width="287"><input type="checkbox" name="public[]" value="Sun Direct" />Sun Direct<br />
<input type="checkbox" name="public[]" value="Big Tv" />Big TV<br />
<input type="checkbox" name="public[]" value="Reliance" />Reliance<br />
<input type="checkbox" name="public[]" value="Videocon" />Videocon<br />
<input type="checkbox" name="public[]" value="Dish Tv" />Dish Tv</td></tr>
<tr>
<td>DTH No</td>
<td>:</td>
<td><input type="text" name="dthno" size="40" value="<?php echo $row['dthno']; ?>"/></td>
</tr>
<tr>
<td>MobileNo</td>
<td>:</td>
<td><input type="text" name="mobileno" size="40" value="<?php echo $row['mobileno']; ?>"/></td>
</tr>
<tr>
<td>Amount</td>
<td>:</td>
<td><input type="text" name="amount" size="40" value="<?php echo $row['amount']?>"/></td>
</tr>
<tr>
<td></td>
<td></td>
<td><input type="submit" id="btn" name="submit" value="update list" /></td></tr>
</td></tr></table>
</form></td></tr>
</table>
</body>
</html>
And Finally we Learn Basic Insert ,View ,Edit ,Delete and Update using php and mysql.That's all,. You can customize this code further as per your requirement. And please feel free to give comments on this tutorial.