Page 1 of 1

Link to delete row from mysql

Posted: Wed Dec 17, 2008 9:54 pm
by jgmaddle
I am writing a simple script to insert data into mysql via an HTML form and then display it in an HTML table. That part of the script is working perfectly. What I want to do now is add a link next to each row in the HTML table that will let me delete that specific row.

I know that to accomplish this I need something like:

Code: Select all

 
 
$ok1 = @mysql_query("DELETE FROM ipaddrs WHERE ID='$id'");
if ($ok1) {
  echo('<p>Row Deleted Succesfully!</p>');
} else {
  echo('<p>Error deleting row from database!<br />'.
       'Error: ' . mysql_error() . '</p>');
}
 
 
but I completely at a loss as how to implement this in my page. I don't know how to pass the ID of the row I want to the $id variable.

Here is the code to the page that displays the data, where I imagine all the code to do this would need to be.

Code: Select all

 
<?php
 
mysql_connect('localhost', 'username', 'password');
mysql_select_db('network') or die ('Unable to connect to database: ' . mysql_error());
$query="SELECT * FROM ipaddrs";
$result=mysql_query($query);
 
$num=mysql_numrows($result);
 
mysql_close();
 
?>
 
<table border="0" cellspacing="5" cellpadding="5">
<tr>
<th><font face="Arial, Helvetica, sans-serif">ID</font></th>
<th><font face="Arial, Helvetica, sans-serif">IP Address</font></th>
<th><font face="Arial, Helvetica, sans-serif">Port</font></th>
<th><font face="Arial, Helvetica, sans-serif">Router</font></th>
<th><font face="Arial, Helvetica, sans-serif">FW Version</font></th>
<th><font face="Arial, Helvetica, sans-serif">Username</font></th>
<th><font face="Arial, Helvetica, sans-serif">Password</font></th>
<th><font face="Arial, Helvetica, sans-serif">Notes</font></th>
<th><font face="Arial, Helvetica, sans-serif">Delete</font></th>
</tr>
 
<?php
 
$i=0;
while ($i < $num) {
 
$id=mysql_result($result,$i,"id");
$ip=mysql_result($result,$i,"ip");
$port=mysql_result($result,$i,"port");
$router=mysql_result($result,$i,"router");
$fwversion=mysql_result($result,$i,"fwversion");
$user=mysql_result($result,$i,"user");
$pass=mysql_result($result,$i,"pass");
$notes=mysql_result($result,$i,"notes");
 
?>
 
<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $id; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $ip; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $port; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $router; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $fwversion; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $user; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $pass; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $notes; ?></font></td>
 
<?php
$i++;
}
 
echo "</table>";
 
?>
 
 
Any help that anyone can offer would be greatly appreciated. If I haven't given enough information please let me know. Thanks.

Re: Link to delete row from mysql

Posted: Wed Dec 17, 2008 11:27 pm
by pcoder
You can add delete on each row. Like
Image
I think you will get some idea to implement the delete functionality.
Cheers

Re: Link to delete row from mysql

Posted: Wed Dec 17, 2008 11:38 pm
by sparrrow
Add the row id to an arrayed checkbox, then parse through the array after post.

Checkbox:

Code: Select all

<input type="checkbox" name="deleteArray[]" value="<?php echo $id; ?>" />
Delete code:

Code: Select all

$deleteArray = $_POST['deleteArray'];
if (count($deleteArray)) { //Does array have anything in it? If so, proceed
   foreach ($deleteArray as $idToDelete) { //iterate through each value in the array...
      $sql = "DELETE FROM $tablename WHERE id = $idToDelete"; //...and assemble an sql statement for each value...
      $result = mysql_query($sql); //...then execute it
   }
}

Re: Link to delete row from mysql

Posted: Thu Dec 18, 2008 2:00 pm
by jgmaddle
Thanks for the help. I was able to make it work finally. Spent more time trying to figure this out than I did on the whole rest of the script.

Thanks again.

-Jason

Re: Link to delete row from mysql

Posted: Thu Dec 18, 2008 3:05 pm
by RobertGonzalez
For the record the easiest way to accomplish this is to pass back the id of the row you want to delete with a query string var that pushes the delete. This is also the unsafest way to do it because anyone that wants to start screwing with your data can just substitute any value into the query string they want and start deleting anything they want. Is that the desired effect you are after?

Something you can do is set up a form for each listed row and use a post request to push the posted data to the page. Maybe even add a second step to confirm deletion. Or perhaps you could drill down into the row so that when you click the row you see the data in the row and an icon to delete from there with a check code that is generated on the fly or something.