Help with dynamic form for updating multiple records in SQL table.

[Simplicity]

Hi there.

I was wondering whether any of you would be able to give me a nudge in the right direction.  I may have taken on a project that exceeds my limited capabilities but thats just an opportunity to develop my own skills, right?  :)

I have to create a page to monitor match attendance that will enable the user to update the status field in the database for each user.
The user will select the date and team name from dropdown lists, executing this query will provide them with all of the player name records

What I need to be able to do is display a list of the names and an editable field to the right of them which can all be updated (with the status code).  I then need all of the records to update in the database with the new status when submitted.

The table in my database has the following fields
TeamName
FirstName
LastName
Date
Status

Obviously all the fields but status are already set.  Its just a case of updating the status column.

I know how to do all the parts individually, selecting, displaying, updating (although I've not had any experience updating multiple records at once), form handling etc but trying to put it all together is a bit beyond my capability at the moment.  I'm not looking for someone to code it for me, but if you have any suggestions on how you would tackle coding this, which order I should be defining things in/where I should start then I think I can have a damn good stab at writing it (then maybe come back with my code for pointers if it doesn't quite go to plan).

If you could provide any assistance it would be appreciated.  I don't really want to have to manually alter the original template on a weekly basis.

Kind Regards
S.

[dhimok]

All you need to do is to add a checkbox in the html page for each id in the database like this:

<input type="checkbox" name="selected_fld[]" value="<?=$id?>"  />

and then the action php page like this:

[code]
<?php
// you can get the status value from a qyerystring
$status = $_GET["status"];


foreach(@$_POST['selected_fld'] as $key => $value) {
mysql_query("UPDATE database_table SET status = '$status' WHERE id = '$value'") or die('Error, query failed. ' . mysql_error());
}
?>
[/code]

U can update all the checked records. You can use some javascript to check all or uncheck all at once. You may get an error if u update with none selected. In that case u do this

[code]
<?php
$status = $_GET["status"];
if(count(@$_POST['selected_fld']) == 0) {
                echo "No checkboxes selected";
                exit;
//header("location: sendBackToPrevious.php");
} else {
foreach(@$_POST['selected_fld'] as $key => $value) {
mysql_query("UPDATE database_table SET status = '$status' WHERE id = '$value'") or die('Error, query failed. ' . mysql_error());
}
}
?>
[/code]

You can redirect if u dont want to exit