pulling values from two different tables

[bradkenyon]

i have a table that has volunteers in it, then a table that has venues stored.

 

each volunteer will be assigned to a certain venue, and i list all the volunteers w/ their name, and each has a drop down list of all the venues, the volunteer chairman will go in and assign each volunteer by the drop down which will be next to each volunteer's name.

 

what i envision is for the volunteer chair to go down thru the list and assign each volunteer and hit a button that will grab all the volunteers and plug the venue the chairman assigned them to, so it will store the venue id from the venues table into the volunteers table, within its venue_id column.

 

what i really need to know how to do is how to assign all the selected drop downs and assign the values into each volunteer's venue_id record field with a click of the button. so it will go thru the form and grab each value and store it into the venue_id column of the volunteers table.

 

thanks in advance!

 

<?php
// -----------------------------------------------------
//it displays appropriate columns based on what table you are viewing
function displayTable($table, $order, $sort){
$query = "select * from $table ORDER by $order $sort";
$result = mysql_query($query);
if($_POST) { ?>
	<table border="1" cellpadding="5" width="850px">
	<tr>
		<th>Name</th>
		<?php if($table == 'volunteers_2008' || $table == 'volunteers_2009') { ?>
			<th>Comments</th>
		<?php } ?>
		<?php if($table == 'volunteers_2009') { ?>
			<th>Interests</th>
			<th>Venue</th>
		<?php } ?>
		<th>Edit</th>
	</tr>
	<tr>
	<?php
	while($row = mysql_fetch_array($result))
	{
		$i = 0;
		while($i <=0)
		{
			print '<td>'.$row['fname'].' '.$row['lname'].'</td>';
			if($table == 'volunteers_2008' || $table == 'volunteers_2009') {
				print '<td><small>'.substr($row['comments'], 0, 32).'</small></td>';
			}
			if($table == 'volunteers_2009') {
				print '<td><small>1) '.$row['choice1'].'<br>2) '.$row['choice2'].'<br>3) '.$row['choice3'].'</small></td>';
				//display list of venues
				$query_venues = "select * from venues ORDER by venue_name ASC";
				$result_venues = mysql_query($query_venues); ?>
				<td><select name="venue">
					<option>Not Assigned</option><?php
				while($row_venues = mysql_fetch_array($result_venues)) {
					print '<option value="'.$row_venues['id'].'">'.$row_venues['venue_name'].'</option>';
				} ?>
				</select></td> <?php	
			} ?>
			<td><a href="?mode=upd&id=<?= $row[id] ?>&table=<?= $table ?>">Upd</a> / <a href="?mode=del&id=<?= $row[id] ?>&table=<?= $table ?>" onclick="return confirm('Are you sure you want to delete?')">Del</a></td> <?php
			$i++;
		}
	print '</tr>';
	}
	print '</table>';
}
}?>

[Barand]

I'd store venues in an array rather than query them for every volunteer

 

<?php
$sql = "SELECT id, venue_name FROM venue ORDER BY venue_name";
$res = mysql_query($sql);
$venues = array();
while (list($id, $name) = mysql_fetch_row($res)) $venues[$id] = $name;     // store array of venues

function venueMenu($volID, &$venues)
{
    $res = "<select name='venue[$volID]'>";                                // array of selected values will be posted
    $res .= "<option value='0'>Not assigned</option>";
    foreach ($venues as $id=>$name) $res .= "<option value='$id'>$name</option>";
    $res .= '</select>';
    return $res;
}

echo "<form method='post' action='somepage.php'>";
echo "<table>";

/**
*  List volunteers with venue menus
*/
$sql = "SELECT volID, name FROM volunteer";
$res = mysql_query($sql);
while (list($vid, $vname))
{
    echo "<tr><td>$name</td><td>" . venueMenu($id, $venues) . "</td></tr>";
}

echo "</table>";
echo "<input type='submit' name='btnSubmit' value='Submit'>";
echo "</form>"
?>

 

Then, to process

 

<?php
foreach ($_POST['venue'] as $volID => $venueID)
{
      // process volID and venueID
      if ($venueID != 0)
      {
        // insert ids into database
      }
}
?>

[bradkenyon]

Does this look good?

<?php 
foreach ($_POST['venue'] as $volID => $venueID) {
// process volID and venueID
if ($venueID != 0) {
	// insert ids into database
	$result = mysql_query("UPDATE volunteers_2009 SET venue_id='$venueID' WHERE id=$volID") 
	or die(mysql_error());
}
} ?>

 

 

[Barand]

ok if there's only ever one venue per volunteer each year

[bradkenyon]

They can only be assigned to one venue, for each year. So right now, I am just managing the 2009 Volunteers, for the festival that takes place in July of 2009.