multiple select box with selected option

[blockhead]

Hello,
first of all I have three tables (tournament_game, umpire_game, umpires).
Updating tournament_game umpires, each game may
have from two to four umpires, and those umpires can work from one to n number of games,
that's why I'm using connection table umpire_game.

I'd like to get selected multiple select box of umpires in game, it could be two to four options,
with all umpires listed as options example:

<select name=umpire_game[] size=10 multiple>
<option value=$umpire_id>$umpire_name</option>
<option value=$umpire_id selected>$umpire_name</option>
<option value=$umpire_id selected>$umpire_name</option>
<option value=$umpire_id>$umpire_name</option>
<option value=$umpire_id selected>$umpire_name</option>
<option value=$umpire_id selected>$umpire_name</option>

//Selecting particular game:

$result = db_query("SELECT * FROM tournament_game WHERE game_id = '$game_id'");
$data = db_fetch_object($result);

// data goes bellow

//Selecting umpires which work in selected game:

$resultumpire = db_query("SELECT * FROM umpire_game WHERE game_id = '$game_id'");
$i=0;
while($dataumpire = db_fetch_object($resultumpire))
{
   $umpire_id[$i]=$dataumpire->umpire_id;		
$i++;
}

//next should go list of all umpires which will be populated in option fields

Iwas trying following but got just those umpires who works in particular game:

$resultump = db_query("SELECT * FROM umpires WHERE umpire_id ='$umpire_id[$i]'");
while($dataump = db_fetch_object($resultump))
{
echo "<option value=".$dataump->umpire_id." >".$dataump->umpire_name."</option>";
}

I'm not able to move on from this point, to get listed all umpires and get selected just those umpires
who works in particular game as I described above.
It's too dificult task for me, since I'm quite intermediate programmer

I'd appreciate any help

Thanks

Stan

[barand]

This ought to do it

<?
function umpireOptions($game) {
	$sql = "select a.umpire_id, a.umpire_name, b.game_id from umpire a "
		. "left join umpire_game b on a.umpire_id = b.umpire_id "
		. "order by a.umpire_name";

$result = mysql_query($sql);

while (list($id, $name, $gid) = mysql_fetch_row($result)) {
	$select = $gid==$game ? 'selected' : '' ;  // this line selects the ones for $game
	echo "<option value=\"$id\" $select> $name </option>";
}
}	
?>

<select name="umpires" size="10" multiple>
	<? umpireOptions($game_id) ?>
</select>

[blockhead]

Hi,
thanks for your advice, it works, but somehow it repeats one value three times. I'll provide you sql structure for this and you'd
see it what i mean

here is sql dump:

#

Table structure for table 'tournament_game'

#

DROP TABLE IF EXISTS tournament_game;
CREATE TABLE tournament_game (
game_id smallint(9) NOT NULL auto_increment,
game_home varchar(128) NOT NULL default '',
game_date date NOT NULL default '0000-00-00',
game_time time NOT NULL default '00:00:00',
game_score varchar(12) NOT NULL default '',
game_lineup_home text NOT NULL,
game_lineup_guest text NOT NULL,
game_coach_home varchar(32) NOT NULL default '',
game_coach_guest varchar(32) NOT NULL default '',
game_tournament tinyint(4) NOT NULL default '0',
game_guest varchar(128) NOT NULL default '',
PRIMARY KEY (game_id)
) TYPE=MyISAM;

#

Dumping data for table 'tournament_game'

#

INSERT INTO tournament_game (game_id, game_home, game_date, game_time, game_score, game_lineup_home, game_lineup_guest, game_coach_home, game_coach_guest, game_tournament, game_guest) VALUES("1", "Glen Ellyn Philies", "2003-06-20", "17:00:00", "10:5", "Preorders link ------ You would just make a page that says title and logo of COMIC BOOK MONSTERS, and i will add the books that they can preorder on. All you guys have to do is set a up a nice page that says PREORDERS -and other links included for all pages-----which should be all professional, and i will take it form there to put pics etc. ", "Preorders link ------ You would just make a page that says title and logo of COMIC BOOK MONSTERS, and i will add the books that they can preorder on. All you guys have to do is set a up a nice page that says PREORDERS -and other links included for all pages-----which should be all professional, and i will take it form there to put pics etc. ", "Anderson", "Ericson", "1", "Aurora Lightning");
INSERT INTO tournament_game (game_id, game_home, game_date, game_time, game_score, game_lineup_home, game_lineup_guest, game_coach_home, game_coach_guest, game_tournament, game_guest) VALUES("2", "Elgin Heat", "2003-06-20", "15:00:00", "5:8", "Showing and hiding layers allows the display of new page content without the wait for a new document to load. It also allows a large amount of content to be contained within a document, yet with little scrolling required. Internet Explorer 4+, Netscape 4, Netscape 6+/Mozilla and Opera can all show and hide layers.", "Each layer you wish to show/hide is specified as a separate absolute-positioned DIV. You can place just about anything you want in the layers, including images, tables, lists, or other tags. The layers can be contained within a table.\r\n", "Smith", "Roach", "2", "Palatine Panthers");
INSERT INTO tournament_game (game_id, game_home, game_date, game_time, game_score, game_lineup_home, game_lineup_guest, game_coach_home, game_coach_guest, game_tournament, game_guest) VALUES("3", "Wheeling Mid-Suburban Panthers", "2003-06-20", "13:00:00", "11:3", "Showing and hiding layers allows the display of new page content without the wait for a new document to load. "Showing and hiding layers allows the display of new page content without the wait for a new document to load. ", "Kennedy", "Maison", "2", "Bartlett Hawks");

#

Table structure for table 'umpire_game'

#

DROP TABLE IF EXISTS umpire_game;
CREATE TABLE umpire_game (
umpire_id tinyint(6) NOT NULL default '0',
game_id smallint(9) NOT NULL auto_increment,
PRIMARY KEY (umpire_id,game_id),
KEY umpire_id (umpire_id,game_id)
) TYPE=MyISAM;

#

Dumping data for table 'umpire_game'

#

INSERT INTO umpire_game (umpire_id, game_id) VALUES("1", "1");
INSERT INTO umpire_game (umpire_id, game_id) VALUES("2", "1");
INSERT INTO umpire_game (umpire_id, game_id) VALUES("2", "2");
INSERT INTO umpire_game (umpire_id, game_id) VALUES("2", "3");
INSERT INTO umpire_game (umpire_id, game_id) VALUES("3", "3");
INSERT INTO umpire_game (umpire_id, game_id) VALUES("4", "2");

#

Table structure for table 'umpires'

#

DROP TABLE IF EXISTS umpires;
CREATE TABLE umpires (
umpire_id int(6) unsigned NOT NULL auto_increment,
umpire_firstname varchar(32) NOT NULL default '',
umpire_lastname varchar(32) NOT NULL default '',
umpire_address varchar(255) NOT NULL default '',
umpire_city varchar(32) NOT NULL default '',
umpire_state char(3) NOT NULL default '',
umpire_zip varchar(6) NOT NULL default '',
umpire_socialnumber varchar(20) NOT NULL default '',
umpire_email varchar(64) NOT NULL default '',
umpire_homephone varchar(15) NOT NULL default '',
umpire_timeoff varchar(32) NOT NULL default '',
umpire_cellphone varchar(18) NOT NULL default '',
umpire_workhours varchar(32) NOT NULL default '',
umpire_emergency_contact varchar(128) default NULL,
umpire_emergencyphone varchar(32) default NULL,
umpire_registration varchar(32) default NULL,
umpire_exprience varchar(16) default NULL,
umpire_organization varchar(128) default NULL,
umpire_status tinyint(1) NOT NULL default '0',
umpire_login varchar(16) NOT NULL default '',
umpire_password varchar(16) NOT NULL default '',
umpire_role varchar(64) NOT NULL default '',
umpire_code char(3) NOT NULL default '',
KEY umpire_id (umpire_id)
) TYPE=MyISAM;

#

Dumping data for table 'umpires'

#

INSERT INTO umpires (umpire_id, umpire_firstname, umpire_lastname, umpire_address, umpire_city, umpire_state, umpire_zip, umpire_socialnumber, umpire_email, umpire_homephone, umpire_timeoff, umpire_cellphone, umpire_workhours, umpire_emergency_contact, umpire_emergencyphone, umpire_registration, umpire_exprience, umpire_organization, umpire_status, umpire_login, umpire_password, umpire_role, umpire_code) VALUES("1", "John", "Doe", "any", "any", "any", "any", "any", "[e-mail]", "any", "any", "any", "any", "any", "any", "IHSA", "6+years", "SUA", "1", "johndoe", "sua125", "umpire", "DOE");
INSERT INTO umpires (umpire_id, umpire_firstname, umpire_lastname, umpire_address, umpire_city, umpire_state, umpire_zip, umpire_socialnumber, umpire_email, umpire_homephone, umpire_timeoff, umpire_cellphone, umpire_workhours, umpire_emergency_contact, umpire_emergencyphone, umpire_registration, umpire_exprience, umpire_organization, umpire_status, umpire_login, umpire_password, umpire_role, umpire_code) VALUES("2", "Jonathan", "Blue", "any", "any", "any", "any", "any", "[e-mail]", "any", "any", "any", "any", "any", "any", "NCAA", "8+years", "SUA", "1", "jonathanblue", "sua149", "umpire", "BLU");
INSERT INTO umpires (umpire_id, umpire_firstname, umpire_lastname, umpire_address, umpire_city, umpire_state, umpire_zip, umpire_socialnumber, umpire_email, umpire_homephone, umpire_timeoff, umpire_cellphone, umpire_workhours, umpire_emergency_contact, umpire_emergencyphone, umpire_registration, umpire_exprience, umpire_organization, umpire_status, umpire_login, umpire_password, umpire_role, umpire_code) VALUES("3", "Michael", "Jackson", "any", "any", "any", "any", "any", "[e-mail]", "any", "any", "any", "any", "any", "any", "ISA", "10 years", "SUA", "1", "michaeljackson", "sua235", "umpire", "JAC");
INSERT INTO umpires (umpire_id, umpire_firstname, umpire_lastname, umpire_address, umpire_city, umpire_state, umpire_zip, umpire_socialnumber, umpire_email, umpire_homephone, umpire_timeoff, umpire_cellphone, umpire_workhours, umpire_emergency_contact, umpire_emergencyphone, umpire_registration, umpire_exprience, umpire_organization, umpire_status, umpire_login, umpire_password, umpire_role, umpire_code) VALUES("4", "George", "Michael", "any", "any", "any", "any", "any", "[e-mail]", "any", "any", "any", "any", "any", "any", "IHSA", "3 years", "SUA", "1", "georgemichael", "sua99", "umpire", "MIC");

here is your select :

$result = db_query("select a.umpire_id, a.umpire_firstname, b.game_id from umpires a "
. "left join umpire_game b on a.umpire_id = b.umpire_id "
. "order by a.umpire_id");
while (list($umpire, $umpirename, $gid) = db_fetch_row($result)) 
{
$select = $game==$gid ? 'selected' : '' ; 
echo "<option value=\"$umpire\" $select> $umpirename </option>";
}

So I don't know if it will make the same "bug" for you or it is just my
fault

Anyway thanks for help

Stan

[barand]

function isUmpire($u, $g) {
	$sql2 = "select umpire_id, game_id from "
		. " umpire_game where umpire_id = $u and game_id=$g ";
	$result = mysql_query($sql2);
	$count = mysql_num_rows($result);
	mysql_free_result($result);
	return $count > 0;
}
function umpireOptions($game) {
	$sql1 = "select umpire_id, umpire_name from umpire order by umpire_id";

$result = mysql_query($sql1);	
while (list($id, $name) = mysql_fetch_row($result)) {
	$select = isUmpire($id,$game) ? 'selected' : '' ;  // this line selects the ones for $game
	echo "<option value=\"$id\" $select> $name </option>";
}
mysql_free_result($result);
}

Test data - that data for which the program works.

Substituting your umpire_games for mine gave the same bug. This hsould be OK.