Two values in select form?

[JJohnsenDK]

Hey

I want to transfer two different values from one select option form. Because i need to update two fields in my database when a player have been selected in the dropdown menu. The database structure:

formation:
formation_ID: 1 - Player_ID: 2 - Position_ID: 4
formation_ID: 2 - Player_ID: 5 - Position_ID: 8
formation_ID: 3 - Player_ID: 9 - Position_ID: 10

Lets say that I in the dropdown menu select player_ID 3 in the position_ID 11

Then 3 should be inserted in player_ID and 11 in position_ID in database. Is it posible to transfer to values from one select option?

Here is the code:

[code]
<form action="<?php $_SERVER['PHP_SELF']; ?>" method="POST">
<p>
<select name="position">
<?php
include('config.php');
$sql2 = "SELECT positions_ID, position FROM postions";
$result2 = mysql_query($sql2) or die(mysql_error());
if (mysql_num_rows($result2)==0){
echo "There were no results";
}else{
while($row2 = mysql_fetch_array($result2)){
?>
<option value="<?=$row2['positions_ID'];?>"><?=$row2['position'];?></option><br>
<?php
}
}
?>
</select>
<select name="name">
<?php
$sql = "SELECT fname, lname, player_id FROM player";
$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result)==0){
echo "There were no results";
}else{
while($row = mysql_fetch_array($result)){
?>
<option value="<?=$row['player_id']; ?>"><?=$row['fname']." ".$row['lname']; ?></option><br>
<?php
}
}
?>
</select>
<select name="side">
<option value="1">1</option>
<option value="2">2</option>
</select>
</p>
<input type="submit" name="sumbit" value="send" />
</form>

<?php
//Opdatere databasen med info fra form
$update_sql = "INSERT INTO formation
SET
player_ID = '".$_POST['name']."',
positions_ID = '".$_POST['position']."',
game_ID = '".$_GET['game_id']."',
season_ID = '".$_GET['season_id']."',
side = '".$_POST['side']."'";
$update = mysql_query($update_sql) or die(mysql_error());
?>
[/code]

Note: i havent set the insert into values because i need to know how to insert two values first.

[AndyB]

That's confusing. The simple answer to 'how do I insert two values' begins with some method of actually getting two values.  Your form as presently shown has only the 'position' (that's the name of the form input) and yet selects a 'player'.

[JJohnsenDK]

aaah yes... but how do i insert a new record for all 11 fields i want to insert into the database?

[AndyB]

In order to move ahead with a solution for you, can you explain what these 11 fields are and how any data gets into them ... the better description you provide of your problem, the more likely it is that someone can propose a sensible solution.

Is 'formation_ID' anything more than an autoincrementing record id?

[JJohnsenDK]

Okay... im gonna descript what i need into the last detail.

Im trying to make a script which can update my formation database. This formation database holds formations for soccer teams, which have 11 players on each team so 22 players for every game.

First i have this database which holds the formations for soccer teams:

formation:
formation_ID: 1 - game_ID: 1 - season_ID: 1 - player_ID: 2 - position_ID: 1 - side: 1
formation_ID: 2 - game_ID: 1 - season_ID: 1 - player_ID: 5 - position_ID: 3 - side: 1
formation_ID: 3 - game_ID: 1 - season_ID: 1 - player_ID: 7 - position_ID: 4 - side: 1
formation_ID: 4 - game_ID: 1 - season_ID: 1 - player_ID: 8 - position_ID: 1 - side: 2
formation_ID: 5 - game_ID: 1 - season_ID: 1 - player_ID: 9 - position_ID: 3 - side: 2
formation_ID: 6 - game_ID: 1 - season_ID: 1 - player_ID: 11 - position_ID: 4 - side: 2

formation_ID = auto increment.
game_ID gets the match number. For example if its the 5. game in the season game_ID is 5.
season_ID gets the year of the season from anohter table.
player_ID gets the player name from anohter table.
position_ID gets the position name from anohter table. for example: Attacker.
side: 1 = home team - 2 = visitor team.

So im trying to set the startformation for home and visitor team by having 11 dropdown menus where i can select which player i want in one of the 22(11 for home team and 11 for visitor) positions and then insert it into the database. I can get this to work if i only use one dropdown menu. Like in this code:

[code]
<form action="<?php $_SERVER['PHP_SELF']; ?>" method="POST">
<p>
<select name="position">
<?php
include('config.php');
$sql2 = "SELECT positions_ID, position FROM postions";
$result2 = mysql_query($sql2) or die(mysql_error());
if (mysql_num_rows($result2)==0){
echo "There were no results";
}else{
while($row2 = mysql_fetch_array($result2)){
?>
<option value="<?=$row2['positions_ID'];?>"><?=$row2['position'];?></option><br>
<?php
}
}
?>
</select>
<select name="name">
<?php
$sql = "SELECT fname, lname, player_id FROM player";
$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result)==0){
echo "There were no results";
}else{
while($row = mysql_fetch_array($result)){
?>
<option value="<?=$row['player_id']; ?>"><?=$row['fname']." ".$row['lname']; ?></option><br>
<?php
}
}
?>
</select>
</p>
<input type="submit" name="sumbit" value="send" />
</form>

<?php
//Opdatere databasen med info fra form
$update_sql = "INSERT INTO formation
SET
player_ID = '".$_POST['name']."',
positions_ID = '".$_POST['position']."',
game_ID = '".$_GET['game_id']."',
season_ID = '".$_GET['season_id']."'";
$update = mysql_query($update_sql) or die(mysql_error());
?>
[/code]

What i cant get to work is to have 22 dropdown insted of just one(not cool that its necessary to go into this page 22 times to select the two formations for one game). Thats why i want 22 dropdown menus. 11 for home and 11 for visitor team. I can with no problem make 11 dropdowns with a for loop but then the INSERT query doesnt work. Maybe a function of some kind could work here? Never worked much with functions so i couldnt make one myself, but i think it could come in handy in this case??

Hope this is descripting egough.

[JJohnsenDK]

Anyone who can help me on this one?