Update data using checkbox ( php and mysql), How to update data using checkbox

[Azriq_Rieqhael]

Hi there,

I am a php beginner. Currently i am working on some testing php and i get stuck with updating data using checkbox.

The system that i build could update all data correctly if i check all the checkbox and then click submit button.

But there is a problem when i randomly select a row from table to update data. The data is updated according to the first row of data and not the data that i keyed in(i.e, the data is updated same with the value of first row in the table). Below is the codes. Hope someone could enlighten me

//manage_form.php
<?php
$sql = "SELECT * FROM warranty";
$sresult=mysql_query($sql) or die(mysql_error());
if(mysql_numrows($sresult))
{
while($row = mysql_fetch_row($sresult))
{
$id = $row[0];

$orderid=$row[1];

$cust_name=$row[2];

$email = $row[11];

$product = $row[13];

$arrival_date=$row[16];
$fault_desc = $row[28];
$status=$row[29];

$tracking_num=$row[30];

echo '<tr bgcolor="#e9e6ed"><td><input type=checkbox name=box[] id=box[] value="'.$id.'"</td>';
echo '<td align=center ><a href="index_.php?orderid='.$orderid.'&boxaction=display_form" target="_blank">'.$orderid.'</td>'; ?>
<td align=center><?php echo $cust_name;?></td><td align=center><?php echo $email; ?></td><td align=center><?php echo $product; ?></td>
<td width=150><?php echo $fault_desc; ?></td><td align=center><?php echo $arrival_date; ?></td>
<td align="center"><select name=status[]><option value=pending <?php if($status=="pending") echo "selected";?>>Pending</option><option value=processing <?php if($status=="processing") echo "selected";?>>Processing</option><option value=sent <?php if($status=="sent") echo"selected";?>>Sent</option></select></td><td align=center><textarea name=trackingnumber[]><?php echo $tracking_num; ?></textarea>
</td></tr>
<?php
}
echo '<tr><td><input type="submit" name="submit" Value="Update"></td><td><input type="submit" name="submit" Value="Delete" onclick="ConfirmChoice();return false;"></td></tr>';
}
else{
print("<tr><td><B>No record found<B></td><tr>");
}

?>

//update.php

<?php
include('auth.php');
include('database_access_param.php');


$con = mysql_connect($hostname,$dbuser,$dbpassword)or die(mysql_error());
$db = mysql_select_db($dbname,$con)or die(mysql_error());

$box=$_POST['box'];
$track=$_POST['trackingnumber'];
$status_value=$_POST['status'];


for($i=0; $i<count($box); $i++){
$up_id = $box[$i];

$tracks = $track[$i];
$statuss = $status_value[$i];
$sql = "UPDATE warranty SET status='".$statuss."', tracking_num='".$tracks."'WHERE id='".$up_id."'";
$result = mysql_query($sql) or die ("Error running MySQL query");

}
include('manage_form.php');
?>

I have attached a screen shot of the project. In the picture, there is 2 row of data. It is work fine if i want to update the first row data or update 2 rows of data at the same the time.

The problem is, when i wanna update the second row, the data will not updated as the data that i keyed in the textarea(tracking number column) instead it will updated with the data as the first row. I have tested will 5 row of data before this, all the subsequent rows data will be replaced with the value same as the first row.

I guess there is a problem with looping problem, but I do not know how to solve it.

Thanks

[kows]

Azriq_Rieqhael:
it's good to see you're using form arrays! anyway; what I would suggest first off is to use a foreach loop instead of a for loop. a checkbox is only set in a form when it's checked. if it remains unchecked when submitted, it does not exist as far as PHP knows. because of this, you can build your form with keys based upon the order ID instead of just having them set randomly. then, you can loop through $_POST['box'] and have access to the rest of the data for that row by referencing the key that you're at. if this isn't making much sense, here's an example of the form:

<form method="post">
  <!-- order id = 506 -->
  <input type="checkbox" name="box[506]" value="" checked="checked" />
  <input type="text" name="text[506]" value="Hello world" />

  <!-- order id = 907 -->
  <input type="checkbox" name="box[907]" value="" />
  <input type="text" name="text[907]" value="Hello world" />

  <input type="submit" value="Update" />
</form>

update script:

<?php
  //loop through our checkboxes
  foreach($_POST['box'] as $order_id => $value){

    //$order_id is the order we're on now, so we can manipulate other $_POST variables using it
    //for example: $_POST['track'][$order_id]

    $sql = "UPDATE table SET tracking='{$_POST['track'][$order_id]}' WHERE id=$order_id";

    mysql_query($sql);
  }
?>

of course, this script doesn't have any user validation! you should definitely make sure you escape any user input using mysql_real_escape_string().

it's basically doing the same thing your script does but the keys would be off. let me know if you try it!