[SOLVED] Delete a specific MySQL row

[iceblade]

Hi everyone.

 

It's now been around 3 weeks since my first question and still learning a lot about PHP.

 

I'm stuck however on something I'm sure is very easy.

 

I'm displaying a table of data retrieved from a MySQL database.

 

I want each row of this table to have a delete button added to the end of it and when the delete button is push the row is deleted.

 

The problem with my code is that I only seem to be able to delete the last row due to the loop being used in the table.

 

All rows have a primary key of ID.

 

Could someone please help in completing the code below.

 

Thank you.

 

expenses.php

<?php
include "../config.php";
$userid = $_SESSION['userid'];
                                                          
Print "<br><table border=1 cellpadding=0 cellspacing=0 bgcolor=#999999 bordercolor=>";
Print    "<td width=75 align=center>Date</td>";
Print    "<td width=248 align=center>Location</td>";
Print    "<td width=150 align=center>Type</td>";
Print    "<td width=206 align=center>Description:</td>";
Print    "<td width=115 align=center>Size</td>";
Print    "<td width=45 align=center>Qty</td>";
Print    "<td width=65 align=center>Cost</td>";


$data = mysql_query("SELECT * FROM expenses WHERE UserID='$userid' ORDER BY Date ")
or die(mysql_error());
while($info = mysql_fetch_array( $data ))
{

Print    "<table border=1 cellpadding=0 cellspacing=0 bgcolor=#D8D8D8 bordercolor=>";
Print    "<td width=75 align=center>".$info['Date'] . " </td>";
Print    "<td width=248 align=left>".$info['Location'] . " </td>";
Print    "<td width=150 align=center>".$info['Type'] . " </td>";
Print    "<td width=206 align=left>".$info['Description'] . " </td>";
Print    "<td width=115 align=center>".$info['Size'] . " </td>";
Print    "<td width=45 align=center>".$info['Qty'] . " </td>";
Print    "<td width=65 align=center>".$info['Cost'] . " </td>";
$_SESSION['Id'] = $info['Id'];

Print    "<td><form action='../expenses/expenses_delete.php' method='post' name='Delete' id='Delete'>
        <input name='Delete' type='submit' id='Delete' value='Delete' > </td>";
}

?>

expenses_delete.php

<?php
include "../config.php";
$userid = $_SESSION['userid'];
$Id = $_SESSION['Id'];

mysql_query("DELETE FROM expenses WHERE UserID='$userid' AND Id='$Id' ")
	or die(mysql_error());

    header("Refresh: 2; url=\"../expenses/expenses_frame1.php\"");
    echo "<br><br><br><h3><center>Record Deleted";


mysql_close("config.php")
?>

[mikesta707]

change expenses.php to have the button look like

Print    "<td><form action='../expenses/expenses_delete.php?id=".$info['ID']."' method='post' name='Delete' id='Delete'>
        <input name='Delete' type='submit' id='Delete' value='Delete' > </td>";

 

though I would just make a simple link. like so:

 

print "<a href='../expenses/expenses_delete.php?id=".$info['ID']."'>Delete</a>";

 

then on delete.php

 

<?php
include "../config.php";
$userid = $_SESSION['userid'];
$Id = $_GET[id'];//assuming this is the primary key that you want to delete.

   mysql_query("DELETE FROM expenses WHERE UserID='$userid' AND Id='$Id' ")
      or die(mysql_error());

    header("Refresh: 2; url=\"../expenses/expenses_frame1.php\"");
    echo "<br><br><br><h3><center>Record Deleted";


mysql_close("config.php")
?>

 

Hope that helps!

 

[iceblade]

Thanks for that.

 

The expenses.php file now passes the ID on correct.

 

However the GET command in the delete file isn't picking up the ID.

 

I've tested this using an echo and nothing is displayed.

[mikesta707]

hmm.. do you get an error? post your code

[iceblade]

no error at all.

 

Code from the delete file:

<?php

include "../config.php";

$userid = $_SESSION['userid'];

$Id = $_GET['Id'];

 

  mysql_query("DELETE FROM expenses WHERE UserID='$userid' AND Id='$Id' ")

      or die(mysql_error());

 

    header("Refresh: 2; url=\"../expenses/expenses_frame1.php\"");

    echo "<br><br><br><h3><center>Record Deleted";

 

 

mysql_close("config.php")

?>

 

If I change the AND Id='$Id' ") to AND Id='32' ") it deletes line 32 for that user.

 

However if I for example echo "$Id"; I don't get anything either. For this reason I'm guessing the $Id isn't being passed though or being picked up by the delete.php file

[iceblade]

Solved the problem.

 

Changed a line in the delete file;

 

  mysql_query("DELETE FROM expenses WHERE UserID='$userid' AND Id=".$_GET["id"]." ")

      or die(mysql_error())