Ok, I'm getting there, but I still need help. I still cannot get my select boxes to work right in the update form. Here's a summary of what's going on:
In creating my date update form I'm having trouble populating the form select boxes with the right values. What I want is for each select box to be populated with 1 instance of each value in the team_members table while simultaneously having the specific team members that are on that project to be highlighted in the select box automatically.
The basic structure of the db is 3 tables: dates, team_members_x, and team_members. dates has a primary key 'date_id' > which is a foreign key in team_members_x. Team_members has a primary key 'team_id' which is the other foreign key in team_members_x. When a user creates a new course most of the data is filled in the dates table. However they also select 1 or more team members from the team_members table. The date_id and the team_id for each new date and team_member is inserted into the team_members_x table at that time.
The problem is in the update form. The data in the db for a particular record should automatically fill out the update form when the user selects a particular record to update. Most of it is working fine except the select fields that allow for multiple selections. What I'm getting, unfortunately, is ALL the values from the intermediate table team_members_x populating the select box (ie. multiple values of each team member) instead of 1 value for each team member with the values for the particular record selected.
The code I'm currently using in the page that shows the form is as follows:
//the following is to specify the tables
$table_name10 = "team_members";
$table_name11 = "team_members_x";
// the following is the sql statement
$sql6 = "SELECT * FROM $table_name10 LEFT JOIN $table_name11 using (team_id)";
// the following is the result statement to hold the sql
$result6 = @($sql6,$connection) or die(mysql_error());
// the following creates the option block
$option_block6 = "";
// the following is the while statement i am using to populate the option_block6
while ($row = mysql_fetch_array($result6)) {
$team_id = $row['team_id'];
$date_id = $row['date_id'];
$team_fname = $row['team_fname'];
$team_lname = $row['team_lname'];
if ($team_id != NULL) {
$option_block6 .= "<option value=\"$team_id\" selected=\"selected\">$team_fname $team_lname</option>";
} else {
$option_block6 .= "<option value=\"$team_id\">$team_fname $team_lname</option>";
}
}
// the following is the select box
<select name="team_id[]" style="width:200px" multiple="yes" size="6">
<? echo "$option_block6"; ?>
</select>
Any ideas for how to accomplish my goals here?
Thanks,
S./
