ffsja
08-17-2005, 08:06 PM
In creating my php/mysql update form I'm having trouble populating the form select boxes with the right values. What I want to happen is for each select box to be populated with 1 instance of each team members from the team_members table AND I want the team members that are on the specific project to be highlighted automatically.
For the purposes of this problem the db consists of 3 tables: dates, team_members_x, and team_members. dates has the primary key 'date_id' > which is also 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 = @mysql_query($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 get the select boxes filled with only 1 of each value from the team members table AND highlighted just the team members on that specific project?
Thanks,
S./
For the purposes of this problem the db consists of 3 tables: dates, team_members_x, and team_members. dates has the primary key 'date_id' > which is also 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 = @mysql_query($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 get the select boxes filled with only 1 of each value from the team members table AND highlighted just the team members on that specific project?
Thanks,
S./