how to delete record from database through html form – PHP

  php

Q(Question):

I am using Windows XP as my OS and apache server. I have successfully inserted new records into database using forms and now want to delete the records from the database through using radio buttons.

Any record i’ll select with radio button should be deleted on submit.


<?
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","root","root");
//select which database you want to edit
mysql_select_db("mydb");
//If cmd has not been initialized
if(!isset($cmd))
{
//display all the news
$result = mysql_query("select * from stuinfo order by FirstName");
//run the while loop that grabs all the news scripts
while($r=mysql_fetch_array($result))
{
//grab the title and the ID of the news
//$title=$r["title"];//take out the title
$FirstName=$r["FirstName"];//take out the id
//make the title a link
echo "<a href='delete.php?cmd=delete&FirstName=$FirstName'></a>";
echo "<br>";
}
}
?>

<HTML>
<HEAD>
<TITLE>New Document</TITLE>
</HEAD>
<BODY>
<? php
if($_GET["cmd"]=="delete")
{
$sql = "DELETE FROM stuinfo WHERE FirstName=$FirstName";
$result = mysql_query($sql);
echo "Row deleted!";
}
?>
</BODY>
</HTML>

A(Answer):

Both of these page are unrelated
You would have to do
mysql_connect and mysql_close in every unrelated page. In the second page you didnt do the mysql connection. without connection you cant delete from database.

Regards,
JOHNY

A(Answer):

What you’d probably want is a record id included with the list of displayed rows.

Here’s a simple example of one approach:


?php
// Delete rows test
if (isset($_POST['submit'])) {
$sql = 'delete from table_x where id in (';
foreach ($_POST['id'] as $a) {
$sql .= $a . ',';
}
$sql = substr($sql,0,strripos($sql,',')) . ')';
print $sql;
}
?>
<html>
<head><title>Test deleting records</title></head>
<body>
<form action="" method="post">
<table border="1">
<thead><th>Delete?</th><th>Record Detail</th></thead>
<?php
for ($i=0;$i<10;$i++) {
// Note the variable name id[] is an array...
print '<tr><td><input type="checkbox" name="id[]" value="'.$i.'" /></td>';
print "<td><p>Record#$i</p></td></tr>";
}
?>
</table>
<input type="submit" name="submit" value="Delete Record(s)!" />
</form>
</body>
</html>

A(Answer):

You’d want to use the ID of the database records as the "value" attribute of your form element. Then, use the selected ID to delete the row with that ID.

Personally, I don’t feel that deletion should be a one-step process. I always provide an extra "Are you sure?" form that allows the user to change their mind, and protects them from accidental clicks.

A(Answer):

@kovik

I agree…

In addition, it would be best that the user that is initiating the deletion operation is authenticated and probably done via an https page.

Also, it might be advisable to have a ‘roll back’ table set up in case the user changes their mind after the deletions are made.

i.e.
The records are pushed into a ‘deletion history’ table before being deleted from the main table. This table should contain additional columns such as: datetime, userid, and possibly more.

To generalize this operation, it might be best that this table is setup with a BLOB field so the records can be serialized/de-serialized versus duplicating the structure of the main table.

If this is done, an additional column should be added called something like: tablename.

That way, a general function could be constructed that would allow deletion of records from any table in the system.

Of course if there are table relations and constraints are in place that would automatically delete ‘child’ records, things get a bit more complicated

But I’m getting way ahead of myself here….

A(Answer):

Or all deletable items should have an "is_deleted" flag and an expiration date. That’s the easier solution for the "Undo" function. It allows for periodic database purging, and an easily programmed Undo capability.

But, like you said, getting ahead of ourselves.

A(Answer):

@kovik

Yep… Much better… And yep… that’s the normal way of doing it…

LEAVE A COMMENT