MySql Update Through Check Boxes in form

Update only those records which are checked

[mvaz]

Hi friends, I have a database which is populated with info taken from site visitors. The data is not displayed on the site until it has been authorised, and this is done by setting one of the fields auth which is an enum from 'n' to 'y'.

To achieve this, I output all the fields where the auth is 'n' and also provide with a check box at the end of each row. There is a submit button at the end of this approval page and once the submit button is clicked, I want all those rows which are checked to be approved to have their value updated or changed from 'n' to 'y'.

Below is my code to display the original data.
<?php
include_once $_SERVER['DOCUMENT_ROOT']."/includes/db_conx.php";

$query = "SELECT * FROM wishes WHERE auth='n' ORDER by msg_date";
$result = mysql_query($query);
$count = mysql_num_rows($result);

<table width="100%" cellpadding="0" cellspacing="0" border="0" id="wish_body">
<tr>
<td colspan="6"><hr id="hr2" /></td>
</tr>
<form action="approval_process.php" method="post">
<?php
//Run a While loop for the rows
while($c=mysql_fetch_array($result)) {
?>
<tr>
<td width="4%"><span class="style11"><?php echo ($c['id']); ?></span></td>
<td width="16%"><span class="style11"><?php echo stripslashes($c['msg_date']); ?></span></td>
<td width="10%"><span class="style11"><?php echo stripslashes($c['msg_type']); ?></span></td>
<td width="51%"><span class="style11"><?php echo stripslashes(nl2br(stripslashes($c['msg'])));?></span></td>
<td width="8%"><span class="style11"><?php echo ($c['auth']); ?></span></td>
<td width="11%"><span class="style11">
<input type="checkbox" name="approved">
Approve?</span></td>
</tr>
<tr>
<td colspan="6"><br /><hr id="hr2" /></td>
</tr>
<?php
}

?>
<tr>
<td colspan="6" align="center"><input type="submit" name="approval" value="Confirm"></td>
</tr>
</form>
</table>

So far so good, the data is displayed for approval as I want it. The processing script is where I am having the problem, code for which I have written is below:

<?php
include_once $_SERVER['DOCUMENT_ROOT']."/includes/header.inc.php";
include_once $_SERVER['DOCUMENT_ROOT']."/includes/fonts.inc.php";
include_once $_SERVER['DOCUMENT_ROOT']."/includes/db_conx.php";

$query = "SELECT * FROM wishes ORDER by msg_date";
$result = mysql_query($query);
$count = mysql_num_rows($result);

while($row = mysql_fetch_array($result)) {

if(isset($_POST['approval']))
{
$checked = $_POST['approved'];

foreach($checked as $key => $value){
$id= $value;
$sql = "UPDATE wishes SET auth='y' WHERE id='$id'";
$result = mysql_query($sql) or die(mysql_error()."<br />SQL: $sql");
}

//if successful give the below message with number of records updated/changed

if($result){
echo $count." records approved";
}
}
}
mysql_close();
?>

Any help and corrections to the above script is greatly appreciate.
Many thanks - Melwyn

[cameraman]

You aren't setting the value attribute in your display code, and you should change the checkbox name to indicate an array:
<input type="checkbox" name="approved[]" value="<?php echo $c['id']; ?>">

You can simplify the processing, first sanitize the data:
foreach($_POST['approved'] as $idx => $val)
$_POST['approved'][$idx] = intval($val);

Then
$approved = implode(',',$_POST['approved']);
$sql = "UPDATE wishes SET auth='y' WHERE id IN ($approved)";

That will update them with one query. The way you're doing your $count, it's just indicating the number of records in the table. After you do the update query you can ask mysql how many records were affected by the last operation.

[mvaz]

Hello Cameraman, many thanks for your assistance, and apologies for not responding earlier (was down with flu).

I tried to work this script as below:

Form:
while($row = mysql_fetch_array($result))
<input type="checkbox" name="approved[]" value="<?php echo $c['id']; ?>" /> Approve?

then, on the processing script:

foreach ($_POST['approved'] as $idx => $val) {
$_POST['approved']['$idx'] = intval($val);
$approved = implode(',', $_POST['approved']);
$sql = "UPDATE wishes SET auth ='y' WHERE id IN ($approved) LIMIT 1";
}
if ($sql) {

echo "updated successfully";
} else {
echo "Failed"; }
mysql_close();
?>

It says as updated successfully, but nothing really happens. I am confused and tired assessing where and what could be wrong in the script.

I believe two or more sets of eyes and brains are better than one, so could I request you to review the above and advise me accordingly.

Many thanks in advance - Melwyn

[sonjay]

You're only creating a SQL statement with your $sql variable. You need to actually run it:

$query = mysql_query($sql) or die(mysql_error());

[mvaz]

Thanks @sonjay for the correction which I had missed out. I have succeeded in updating the database; but not as I wanted it.

If more than one record is selected (eg. id's 102, 103, 104) the script updates only the first record ie 102 and not the others.

Any ideas why this is happening or where I have gone wrong?

Any help in this direction is highly apppreciated. - Thanks, Melwyn

[mvaz]

Sorted out the problem, I had put LIMIT one in my sql statement.

[Robeysan]

Thanks this thread helped me out.