insert ,update & delete script in php
Table creation:
CREATE TABLE `test_mysql` (
`id` int(4) NOT NULL auto_increment,
`name` varchar(65) NOT NULL default '',
`lastname` varchar(65) NOT NULL default '',
`email` varchar(65) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;
Then creating our database connection called connect.php
<?php
mysql_connect("localhost", "$root", "")or die("cannot connect");
mysql_select_db("test")or die("cannot select DB");
?>
<span style="font-weight:bold;">step 1: insert data in mysql (insert.php)</span>
<!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>
</head>
<body>
<table width="300" border="1" align="center" cellpadding="0" cellspacing="1">
<tr>
<td><form name="form1" method="post" action="../rakhi_practice/insert_action.php">
<table width="100%" border="0" cellspacing="1" cellpadding="3">
<tr>
<td colspan="3" align="center"><strong>Registration</strong></td>
</tr>
<tr>
<td width="71">Name</td>
<td width="6">:</td>
<td width="301"><input name="name" type="text" id="name"></td>
</tr>
<tr>
<td>Lastname</td>
<td>:</td>
<td><input name="lastname" type="text" id="lastname"></td>
</tr>
<tr>
<td>Email</td>
<td>:</td>
<td><input name="email" type="text" id="email"></td>
</tr>
<tr>
<td colspan="3" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</form>
</td>
</tr>
</table>
</body>
</html>
<span style="font-weight:bold;">Step 2: insert_action.php</span>
<?php
include("connect.php");
// get values from from
$name=$_POST['name'];
$lastname=$_POST['lastname'];
$email=$_POST['email'];
// Insert data into mysql
$sql="INSERT INTO test_mysql(name,lastname,email)VALUES ( '$name','$lastname', '$email')";
$result=mysql_query($sql);
if($result)
{
echo "Congratulations!Please login ";
echo "<BR>";
echo "<a href='list_records.php'>Click here to edit</a>";
}
/*
{
echo "sorry ! U are not succesful";
}*/
mysql_close();
?>
step 3:list_records.php
<?php
include("connect.php");
//select query
$result = mysql_query("SELECT * FROM test_mysql");
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List data from mysql </strong> </td>
</tr>
<tr>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Update</strong></td>
<td align="center"><strong>Delete</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td><?php echo $rows['name']; ?></td>
<td><?php echo $rows['lastname']; ?></td>
<td><?php echo $rows['email']; ?></td>
<td align="center"><a href="update.php?id=<?php echo $rows['id']; ?>">update</a></td>
<td align="center"><a href="delete.php?id=<?php echo $rows['id']; ?>">delete</a></td>
</tr>
<?php
}
?>
</table>
</td>
</tr>
</table>
<?php
mysql_close();
?>
<span style="font-weight:bold;">step 4:update.php</span>
<?php
include("connect.php");
// get value of id that sent from address bar
$id=$_REQUEST['id'];
// Retrieve data from database
$result = mysql_query("SELECT * FROM test_mysql WHERE id='$id'");
$rows=mysql_fetch_array($result);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="../rakhi_practice/update_action.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td> </td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Email</strong></td>
</tr>
<tr>
<td> </td>
<td align="center"><input name="name" type="text" value="<?php echo $rows['name']; ?>"></td>
<td align="center"><input name="lastname" type="text" value="<?php echo $rows['lastname']; ?>" size="15"></td>
<td><input name="email" type="text" value="<?php echo $rows['email']; ?>" size="15"></td>
</tr>
<tr>
<td> </td>
<td><input name="id" type="hidden" id="id" value="<?php echo $rows['id']; ?>"></td>
<td align="center"><input type="submit" name="Submit" value="Submit"></td>
<td> </td>
</tr>
</table>
</td>
</form>
</tr>
</table>
<?php
// close connection
mysql_close();
?>
step 5: Update_action.php</span>
<?php
include("connect.php");
$name =$_REQUEST['name'];
$lastname =$_REQUEST['lastname'];
$email =$_REQUEST['email'];
$id =$_REQUEST['id'];
// update data in mysql database
$sql="UPDATE test_mysql SET name='$name', lastname='$lastname', email='$email' WHERE id=".$id;
$result=mysql_query($sql);
// if successfully updated.
if($result)
{
echo "Thanks for update";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}
else
{
echo "ERROR";
}
?>
step 6: Delete.php
<?php
include("connect.php");
// get value of id that sent from address bar
echo $id=$_REQUEST['id'];
$name =$_REQUEST['name'];
$lastname =$_REQUEST['lastname'];
$email =$_REQUEST['email'];
$id =$_REQUEST['id'];
// Retrieve data from database
$result = mysql_query("SELECT * FROM test_mysql WHERE id='$id'");
$rows=mysql_fetch_array($result);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="delete_action.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td> </td>
<td colspan="3"><strong>Delete data </strong> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Email</strong></td>
</tr>
<tr>
<td> </td>
<td align="center"><input name="name" type="text" value="<?php echo $rows['name']; ?>"></td>
<td align="center"><input name="lastname" type="text" value="<?php echo $rows['lastname']; ?>" size="15"></td>
<td><input name="email" type="text" value="<?php echo $rows['email']; ?>" size="15"></td>
</tr>
<tr>
<td> </td>
<td><input name="id" type="hidden" id="id" value="<?php echo $rows['id']; ?>"></td>
<td align="center"><input type="submit" name="delete" value="delete"></td>
<td> </td>
</tr>
</table>
</td>
</form>
</tr>
</table>
<?php
// close connection
mysql_close();
?>
step 7: delete_action.php
<?php
include("connect.php");
$id =$_REQUEST['id'];
$sql="DELETE from test_mysql WHERE id=".$id;
$result = mysql_query($sql);
if($sql)
{
echo "The record is deleted";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}
else
{
echo "ERROR";
}
?>
CREATE TABLE `test_mysql` (
`id` int(4) NOT NULL auto_increment,
`name` varchar(65) NOT NULL default '',
`lastname` varchar(65) NOT NULL default '',
`email` varchar(65) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;
Then creating our database connection called connect.php
<?php
mysql_connect("localhost", "$root", "")or die("cannot connect");
mysql_select_db("test")or die("cannot select DB");
?>
<span style="font-weight:bold;">step 1: insert data in mysql (insert.php)</span>
<!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>
</head>
<body>
<table width="300" border="1" align="center" cellpadding="0" cellspacing="1">
<tr>
<td><form name="form1" method="post" action="../rakhi_practice/insert_action.php">
<table width="100%" border="0" cellspacing="1" cellpadding="3">
<tr>
<td colspan="3" align="center"><strong>Registration</strong></td>
</tr>
<tr>
<td width="71">Name</td>
<td width="6">:</td>
<td width="301"><input name="name" type="text" id="name"></td>
</tr>
<tr>
<td>Lastname</td>
<td>:</td>
<td><input name="lastname" type="text" id="lastname"></td>
</tr>
<tr>
<td>Email</td>
<td>:</td>
<td><input name="email" type="text" id="email"></td>
</tr>
<tr>
<td colspan="3" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</form>
</td>
</tr>
</table>
</body>
</html>
<span style="font-weight:bold;">Step 2: insert_action.php</span>
<?php
include("connect.php");
// get values from from
$name=$_POST['name'];
$lastname=$_POST['lastname'];
$email=$_POST['email'];
// Insert data into mysql
$sql="INSERT INTO test_mysql(name,lastname,email)VALUES ( '$name','$lastname', '$email')";
$result=mysql_query($sql);
if($result)
{
echo "Congratulations!Please login ";
echo "<BR>";
echo "<a href='list_records.php'>Click here to edit</a>";
}
/*
{
echo "sorry ! U are not succesful";
}*/
mysql_close();
?>
step 3:list_records.php
<?php
include("connect.php");
//select query
$result = mysql_query("SELECT * FROM test_mysql");
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4"><strong>List data from mysql </strong> </td>
</tr>
<tr>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Email</strong></td>
<td align="center"><strong>Update</strong></td>
<td align="center"><strong>Delete</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td><?php echo $rows['name']; ?></td>
<td><?php echo $rows['lastname']; ?></td>
<td><?php echo $rows['email']; ?></td>
<td align="center"><a href="update.php?id=<?php echo $rows['id']; ?>">update</a></td>
<td align="center"><a href="delete.php?id=<?php echo $rows['id']; ?>">delete</a></td>
</tr>
<?php
}
?>
</table>
</td>
</tr>
</table>
<?php
mysql_close();
?>
<span style="font-weight:bold;">step 4:update.php</span>
<?php
include("connect.php");
// get value of id that sent from address bar
$id=$_REQUEST['id'];
// Retrieve data from database
$result = mysql_query("SELECT * FROM test_mysql WHERE id='$id'");
$rows=mysql_fetch_array($result);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="../rakhi_practice/update_action.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td> </td>
<td colspan="3"><strong>Update data in mysql</strong> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Email</strong></td>
</tr>
<tr>
<td> </td>
<td align="center"><input name="name" type="text" value="<?php echo $rows['name']; ?>"></td>
<td align="center"><input name="lastname" type="text" value="<?php echo $rows['lastname']; ?>" size="15"></td>
<td><input name="email" type="text" value="<?php echo $rows['email']; ?>" size="15"></td>
</tr>
<tr>
<td> </td>
<td><input name="id" type="hidden" id="id" value="<?php echo $rows['id']; ?>"></td>
<td align="center"><input type="submit" name="Submit" value="Submit"></td>
<td> </td>
</tr>
</table>
</td>
</form>
</tr>
</table>
<?php
// close connection
mysql_close();
?>
step 5: Update_action.php</span>
<?php
include("connect.php");
$name =$_REQUEST['name'];
$lastname =$_REQUEST['lastname'];
$email =$_REQUEST['email'];
$id =$_REQUEST['id'];
// update data in mysql database
$sql="UPDATE test_mysql SET name='$name', lastname='$lastname', email='$email' WHERE id=".$id;
$result=mysql_query($sql);
// if successfully updated.
if($result)
{
echo "Thanks for update";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}
else
{
echo "ERROR";
}
?>
step 6: Delete.php
<?php
include("connect.php");
// get value of id that sent from address bar
echo $id=$_REQUEST['id'];
$name =$_REQUEST['name'];
$lastname =$_REQUEST['lastname'];
$email =$_REQUEST['email'];
$id =$_REQUEST['id'];
// Retrieve data from database
$result = mysql_query("SELECT * FROM test_mysql WHERE id='$id'");
$rows=mysql_fetch_array($result);
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<form name="form1" method="post" action="delete_action.php">
<td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr>
<td> </td>
<td colspan="3"><strong>Delete data </strong> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
</tr>
<tr>
<td align="center"> </td>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>Lastname</strong></td>
<td align="center"><strong>Email</strong></td>
</tr>
<tr>
<td> </td>
<td align="center"><input name="name" type="text" value="<?php echo $rows['name']; ?>"></td>
<td align="center"><input name="lastname" type="text" value="<?php echo $rows['lastname']; ?>" size="15"></td>
<td><input name="email" type="text" value="<?php echo $rows['email']; ?>" size="15"></td>
</tr>
<tr>
<td> </td>
<td><input name="id" type="hidden" id="id" value="<?php echo $rows['id']; ?>"></td>
<td align="center"><input type="submit" name="delete" value="delete"></td>
<td> </td>
</tr>
</table>
</td>
</form>
</tr>
</table>
<?php
// close connection
mysql_close();
?>
step 7: delete_action.php
<?php
include("connect.php");
$id =$_REQUEST['id'];
$sql="DELETE from test_mysql WHERE id=".$id;
$result = mysql_query($sql);
if($sql)
{
echo "The record is deleted";
echo "<BR>";
echo "<a href='list_records.php'>View result</a>";
}
else
{
echo "ERROR";
}
?>
This is great! Thank you!
Excellent Article for beginners.