Simple? SQL insert problem

[fatlip]

I am building a site for my hockey league. I have been dealing with CF for a number of years, but would in no way class myself as any sort of expert. I am not sure if I am just having a brainfart, or what with this one!

I have a simple form that pulls the rosters of a team based on the schedule and whether or not they are the home team or the away team.

I then want to insert (if checked) their player_id and (if player_id checked), the jersey number in which they wore from a populated list menu. This needs to be inserted into another table called scoresheet. The only thing that is important is that they are split between roster_home and roster_away (the team_id's have to be unique to the proper team)

The checkbox holds the player_id list as "roster_away or roster_home"
The list menu holds potential jersey numbers;

Seems simple enough, but I can't get the insert to work properly and align the player_id with the jersey numbers.

Help?

Here is my form:

<form name="game_rosters" action="" method="POST">
<table width="300" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
	<td width="70" colspan="3" align="center"><cfoutput>#getGameInfo.away_team#</cfoutput> Game Roster</td> 
</tr>
<tr>
	<td>Played?</td>
	<td>Jersey</td>
	<td>Player</td>
</tr>

	<cfoutput query="getAwayRoster">
<tr>
	<td width="70" align="center">
    	<input name="roster_away" type="checkbox" value="#getAwayRoster.player_id#"></td>
	<td width="80"> 
        <select name="away_player_jersey_number">
        <option value="#getAwayRoster2.player_jersey_number#" selected>#getAwayRoster2.player_jersey_number#</option>
        <cfloop index="awj" from="0" to="99">
        <option value="#awj#">#awj#</option>
        </cfloop>
        </select>
     </td>
	 <td width="150">#getAwayRoster.player_fname#&nbsp;#getAwayRoster.player_lname#</td>
</tr>
<tr>
      <td colspan="3" align="center">&nbsp;</td>
</tr>
              
        </cfoutput>
</table>

<table width="300" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
	<td width="70" colspan="3" align="center"><cfoutput>#getGameInfo.home_team#</cfoutput> Game Roster</td> 
</tr>
<tr>
	<td>Played?</td>
	<td>Jersey</td>
	<td>Player</td>
</tr>

	<cfoutput query="getHomeRoster">
<tr>
	<td width="70" align="center">
    	<input name="roster_home" type="checkbox" value="#getHomeRoster.player_id#"></td>
	<td width="80"> 
        <select name="home_player_jersey_number">
        <option value="#getHomeRoster2.player_jersey_number#" selected>#getHomeRoster2.player_jersey_number#</option>
        <cfloop index="hj" from="0" to="99">
        <option value="#hj#">#hj#</option>
        </cfloop>
        </select>
     </td>
	 <td width="150">#getHomeRoster.player_fname#&nbsp;#getHomeRoster.player_lname#</td>
</tr>
<tr>
      <td colspan="3" align="center"><input name="submit" type="submit" value="Submit" /></td>
</tr>
              
        </cfoutput>
</table>
</form>

[freshfitz]

I don't understand your checkbox is #getAwayRoster.player_id# but how are you tieing your player id to the checkbox? it is just pulling a random player id from the database if you want to know what player id it is pulling next to the check box put <cfoutput>#getAwayRoster.player_id#</cfouput>