Friday, May 25, 2012

php mysql delete

PHP MySQL Delete 

The DELETE statement is used to delete records in MySql tables.
Delete Data In a Database Table
Example:-
Following is the biodata table we created in previous lesson.
First Name Last Name Age
Scarlett Johansson 25
Clooney Anderson 25
Kate Hudson Hudson 25

We will delete the record with last name Anderson.
The following codes deletes the records in the "biodata"  where LastName is Anderson.

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_database", $con);

mysql_query("DELETE FROM biodata WHERE LastName='Anderson'");

mysql_close($con);
echo "delete 1 record";
?>

Delete data using web interface (HTML PHP Files) 

Re design the biodata view table with delete hyperlink like in following table. 

Id First Name Last Name Age Delete
1 Scarlett Johansson 25 delete
2 Clooney Anderson 35 delete
3 Kate Hudson 30 delete

Codes for above modified table is as follows 

<?php
 $host="localhost";
 $username="root";
 $password="";
 $db_name="my_database";
 $tbl_name="biodata";
mysql_connect("$host", "$username", "$password")or die("cannot connect");   mysql_select_db("$db_name")or die("cannot select DB");
$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
?>
<table bgcolor=#000000 align="center">
 <tr>
<td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>First Name</strong></td>
 <td align="center" bgcolor="#FFFFFF"><strong>Last Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Age</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Delete</strong></td> </tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td bgcolor="#FFFFFF"><?php echo $rows['id']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $rows['FirstName']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $rows['LastName']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $rows['Age']; ?></td>
<td bgcolor="#FFFFFF"><a href="delete_records.php?id=<?php echo $rows['id']; ?>">delete</a></td>
</tr>
<?php
 }
 mysql_close();
 ?>
</table>

"delete_records.php", The file which deletes data from the biodata table is bellow

<?php
$host="localhost";
$username="root";
$password="";
$db_name="my_database";
$tbl_name="biodata";
 mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB");
 $id=$_GET['id'];
$sql="DELETE FROM $tbl_name WHERE id='$id'";
$result=mysql_query($sql);
if($result)
{
echo "Deleted Successfully";
}
else
{
echo "ERROR";
}
mysql_close();
?>

Delete Multiple Records in MySql Tables 

Again we have to design the biodata table as follows with the facility of multiple delete interface.

Php Multiple Records Delete






ID





First Name





Last Name




Age





Delete Files
1 Scarlett Johansson 25




Yes
2 Clooney Anderson 35




Yes
3 Kate Hudson 30




Yes
Select Check box and Press Submit To Delete files

Look at bellow codes to create multiple delete php

<?php
$dbHost = 'localhost';
$dbUser = 'root';
$dbPass = '';
$dbName = 'my_database';
$dbConn = mysql_connect ($dbHost,$dbUser, $dbPass) or die ('MySQL connect failed. ' . mysql_error());
mysql_select_db($dbName) or die('Cannot select database. ' . mysql_error());
?>
<html>
 <head>
 <title></title>
 </head>
<body>
 <h2><font color="#54164e"><center>Php Multiple Records Delete</font></center></h2>
<table align="center" bgcolor="#9a7608" >
<tr bgcolor="#ffffff">
 <th> <center> ID </center> </th> <th> <center> First Name </center> </th>
<th> <center> Last Name</center> </th>
<th> <center> Age </center> </th>
<th> <center> Delete Files </center> </th> </tr>
<?php
 if($_POST['btsubmit']=='Submit') {
 foreach($_POST as $k => $v){
$$k = $v;
}
 $to_del= $_POST['to_del'];
if (("submit")&&($to_del != "")) {
 }
foreach($to_del as $v){
 mysql_query("DELETE FROM biodata where id='$v'");
}
 echo "<center><font color='white' size=5> records is been deleted.</font></center></br>";
}
?>
<?php $query = "SELECT * FROM biodata";
 $result = mysql_query($query);
 while ($row = mysql_fetch_assoc($result)){
 ?>
<form name="form" method="post">
<tr bgcolor="#ffffff">
<td><?php echo $row["ID"];?></td>
<td><?php echo $row["FirstName"];?></td>
<td><?php echo $row["LastName"];?></td>
<td><?php echo $row['Age'];?></td>
<td> <center> <input type=checkbox name="to_del[]" value="<?php echo $row["id"];?>">Yes </center> </td>
</tr>
<?php
 }
?>
<tr><th colspan=4 bgcolor="#ffffff">Select Check box and Press Submit To Delete files</th><td bgcolor="#ffffff"><input type="submit" value="Submit" name="btsubmit"></td>
</tr>
</table>
</form>
</body>
</html>
Posted by at

1 comment:

  1. Clipping White BackgroundMay 23, 2017 at 2:09 AM

    I can not delete my old sql data any one can help me how can I delete this?

    ReplyDelete

Subscribe to: Post Comments (Atom)