enter multiple variables into table, assigning to each appropriate record

Question

I display data into an html table, w/ a drop down box with a list of venues. Each volunteer will be assigned a venue. I envision being able to go down thru the html table and assigning each volunteer a venue. The drop down box contains all the possible venues that they can be assigned to.

<select>
<option value="1">Setup</option>
<option value="2">Check in</option>
etc...
</select>

Then once I am done assigning each volunteer, I want to hit submit and it will assign the appropriate value for each volunteer.

How would I go about doing that, I know how to do that, but only one at a time.

Answer 1

I can't think of any way. You can put multiple SQL statements into 1 MySQL query:

UPDATE volunteer SET venue = 1 WHERE id = 2;
UPDATE volunteer SET venue = 2 WHERE id = 3;
...

I wish there was a way, but I'm thinking that the where will be too different in each query to make it into one.

Answer 2

Change the name of each select, in a way it includes a volunteer id:

<select name="venues[1]">
<option value="1">Setup</option>
etc...
</select>

<select name="venues[2]">
<option value="1">Setup</option>
etc...
</select>

<select name="venues[3]">
<option value="1">Setup</option>
etc...
</select>

After submit there will be a table in $_POST named venues and with indices: 1, 2, 3 (beeing volunteer id) and values beeing selected value for each volunteer.

Now you can iterate on $_POST['venues'] array and save each value:

foreach ($_POST['venues'] as $volunteer_id => $venue) {
  save_venue_for_volunteer($volunteer_id, $venue);
}

Answer 3

Here's a very rough example of how you might handle this. Note: your MySQL tables (assuming MySQL) must be of a type that supports transactions (InnoDB does, MyISAM does not).

<?php

if ( isset( $_POST['venuChoice'] ) )
{
    // Create a transaction
    mysql_query( 'BEGIN' );

    $failure = false;

    // Loop over the selections
    foreach ( $_POST['venuChoice'] as $employeeId => $venueId )
    {
     $sql = sprintf(
       'UPDATE table SET columns=%d WHERE id=%d'
      , intval( mysql_real_escape_string( $venueId ) )
      , intval( mysql_real_escape_string( $employeeId ) )
     );
     if ( ! @mysql_query( $sql ) )
     {
      $failure = true;
      break;
     }
    }

    // Close out the transaction
    if ( $failure )
    {
     mysql_query( 'ROLLBACK' );
     // Display and error or something
    } else {
     mysql_query( 'COMMIT' );
 // Success!
    }
}

?>

<form>
    <select name="venueChoice[1]">
     <option value="1">Setup</option>
     <option value="2">Check in</option>
    </select>
    <select name="venueChoice[2]">
     <option value="1">Setup</option>
     <option value="2">Check in</option>
    </select>
    <select name="venueChoice[3]">
     <option value="1">Setup</option>
     <option value="2">Check in</option>
    </select>

</form>

You could also modify this to keep track of each employee's current venue choice, compare it to the POST data, and then only execute UPDATE queries for those that were actually changed.

Answer 4

foreach ($_POST['venues'] as $volunteer_id => $venue) {
    save_venue_for_volunteer($volunteer_id, $venue);
}

function save_venue_for_volunteer($volunteer_id, $venue) {
    $result = mysql_query("UPDATE volunteers_2009 SET venue_id='$venue' WHERE id='$volunteer_id'") 
    or die(mysql_error());
}

The table it will be saved to is volunteers_2009, so this is how it should be?