Need help with MySQL UPDATE

[mtran]

Hi, I have a View basket page, which works fine in showing a list of items at first. In this same View Basket page, I include a button/form with checkbox to delete any item out. When users select a checkbox and click Update, this leads them to the reserve_edit page, and right back to this updated View basket page.

The problem is: When I select any item, or 2 or more items and click Update, ONLY ONE item always gets deleted: the last item in the table.

Could you help me out of this? I know my description is not very clear, but I hope someby could help.
Here's my View Basket page code:


<?php
$username=$_COOKIE['log'];
$query="SELECT * FROM cart WHERE username='$username' AND status=''"; //initial status is blank.
$result=mysql_query($query,$connection);
?>
<form name='edit' method='post' action='reserve_edit.php'>
<table width="590"class="basket">
<tr class='basket_header'>
<td width='12%'height="35">Remove</td>
<td width='34%' >Title</td>
<td width='15%'>Number reserved </td>
<td width='12%'>From-to</td>
<td width='12%'>Cost</td>
<td width='15%'>Pickup date </td>
</tr>

<?php
while($row = mysql_fetch_array($result)) {
printf("<tr class='basket'>
<td><input type='checkbox' name='delete' value='d'>
<input name='id' type='hidden' value='%s'></td>
<td><a href='listing_detail_m.php?id=%s'>%s</a></td>
<td>%s&nbsp;%s</td>
<td>%s</td>
<td>$%s</td>
<td>%s</td></tr>",
$row["id"],$row["id"],$row["title"],$row["number"],$row["type"],$row['from_to'],$row["cost"],$row["pickup"]); } ?>
</table>
<br>
<input type="submit" name="update" value="Update" class="btn">
</form>


Here's my reserve_edit page code:


<?php
require "config.php";
$username=$_COOKIE['log'];
$status=$_POST['delete'];
$id=$_POST['id'];

$result = mysql_query("UPDATE cart SET status='$status' WHERE username='$username' AND id='$id'");
header("Location: reserve_basket.php");
?>

[Twey]

If you want to update multiple rows, you need to have multiple elements. All those checkboxes have the same name, so they'll just override one another.

You should also use mysql_real_escape_string (http://www.php.net/mysql-real-escape-string)() to safely escape any user data you intend to be using in a query. Not doing so leaves your database open to attack.

[mtran]

The problem is also: Even when I don't select the last item in the table, it's always the one, and the only one that gets deleted. How come?
I'm trying to change the name of my checkboxes and may come back and bug you again! :)
To Twey, about mysql_real_escape_string(), you showed my last time, but I forgot using it this time. Bad student huh!:)

[mtran]

I've been researching about dynamically naming the checkboxes. I see some tutorial doing like this, but because of novice knowledge, don't know how to make it work. Could you help please with whatever way as long as it works. Thanks!!!


View basket:
<input type='checkbox' name='delete[]' value='d'>



Edit basket:
$status=$HTTP_POST_VARS['delete'];
$result = mysql_query("UPDATE cart SET status='$status' WHERE username='$username' AND id='$id'");

[Twey]

Personally, I'd do it like this:
<input type='checkbox' name='delete" . $row['id'] . "' value='d'>
<input name='id' type='hidden' value='%s'>I haven't used printf because there's no real need to here :)

Then:
$status = mysql_real_escape_string($_POST['delete' . $id]);
$username = mysql_real_escape_string($username);
$result = mysql_query("UPDATE cart SET status='$status' WHERE username='$username' AND id='$id'");

[mtran]

I followed Twey's instruction, and the edit page works but ONLY when you select the last record:

if only the last record is selected, it gets deleted: OK
if the last record and other record are selected: only the last record is deleted: not ok
if other records are selected and the last one is not: nothing happens: not Ok

I don't know why. Need help!