PHP dynamic output/insert query

[Voo]

Hope someone can help me out with this one.

Table 1 contains 58 players.
Table 2 contains 68 teams.
Table 3 contains fixtures based on Table 1 and Table 2.

What I have is a form that outputs each record from Table 1 as text and then a combo box with all the teams from Table 2. So for every record in Table 1, there is a combo box with all the teams listed in it on the webpage.

The user looks at each name and then chooses a team from the combo box. What I need to do is take all 58 names and add the team that was chosen from the combo box to Table 3.

I'm assuming I need some kind of an array to read in all the chosen teams from the combo box for each name but I have no idea how to go about this or what the SQL query would look like to do this.

	$submit = $_POST['submit'];
	$fixture_draw_id = $_POST['f_fixture_draw_id'];
	$fixture_team_id = $_POST['f_fixture_team_id'];
	$fixture_player_id = $_POST['f_fixture_player_id'];

	if ( $submit )
	{
		$query = mysql_query("INSERT INTO $fixtures ( fixture_draw_id, fixture_team_id, fixture_player_id ) values ( '$fixture_draw_id', '$fixture_team_id', '$fixture_player_id' )");

		echo "Added.<br>";
		echo "<a href='index.php?action=addfixture'>Add another</a><br><br><a href='index.php'>Back to Main Page</a>";
	}
	else
	{
		echo "<form method='post' action=''>\n";
		echo "<table border='0' cellspacing='1' cellpadding='1' summary='' class='black'>\n";

		$result = mysql_query("SELECT * FROM $draws ORDER BY draw_id DESC LIMIT 0,1");
		while($r=mysql_fetch_array($result))
		{    
			$draw_id=$r["draw_id"];
			$draw_date=$r["draw_date"];
		}

		echo "<tr>\n<td>Draw: </td>\n<td><input type='hidden' name='f_fixture_draw_id' value='$draw_id'>$draw_date</td>\n</tr>\n";
		echo "<tr>\n<td>&nbsp;</td>\n</tr>\n";

		$result = mysql_query("SELECT * FROM $players ORDER BY player_name ASC");
		while($r=mysql_fetch_array($result))
		{
			$player_id = $r["player_id"];
			$player_name = $r["player_name"];

			echo "<tr>\n<td>$player_name</td>\n<td><select name='f_fixture_team_id'>";
			echo "<option value='-'>-- Choose Team --</option>";

			$result1 = mysql_query("SELECT * FROM $teams ORDER BY team_name ASC");
			while($r1=mysql_fetch_array($result1))
			{
				$team_id = $r1["team_id"];
				$team_name = $r1["team_name"];

				echo "<option value='$team_id'>$team_name</option>";
			}

			echo "</select></td>\n</tr>\n";
		}

		echo "<tr>\n<td><br><input type='submit' name='submit' value='Submit'><br><br><a href='index.php'>Back to Main Page</a></td>\n</tr>\n";
		echo "</table>\n</form>\n";
	}

Obviously as it stands, when Submit is pressed it will only read in the last entry, and not all 58. I need this to be dynamic so that no matter how many records there are in Table 1, it will add however many.

Hope this makes sense and any help appreciated.

 

[LazyManc]

I'm not sure I fully understand what you're trying to do, but it sounds like each select box in your form needs a unique name, otherwise it's just overwriting the previous select value with the next one.

When you generate your selects for the form, use an array-type name - add square brakets (and optionally number them with something useful like the player id):

<?php for ($i = 1; $i <= count($players); $i++): ?>
<select name="player[<?php echo $playerId; ?>]">
 ... snip ...

This will return your results from the form as a numbered array, accessible with

$selectedOption = $_POST["player[$playerId]"];

 

[marc2003]

you want 58 rows on the page? the player name displayed each with it's own select box? you don't want to nest the queries like you have. you're querying the database for team details on every loop through the players.

just querying the database once is enough. something like this perhaps?

$result = mysql_query("SELECT * FROM $players ORDER BY player_name ASC");
while($r=mysql_fetch_array($result)) {
	$players_array[$r['player_id']] = $r['player_name'];
}
$result1 = mysql_query("SELECT * FROM $teams ORDER BY team_name ASC");
while($r1=mysql_fetch_array($result1)) {
	$teams_array[$r1['team_id']] = $r1['team_name'];
}

foreach($teams_array as $team_id => $team_name) {
	$options .= '<option value="'.$team_id.'">'.$team_name.'</option>'."\n";
}

foreach($players_array as $player_id => $player_name) {
	echo '<p>'.$player_name;
	echo '<select name="names['.$player_id.']">';
        echo $options;
	echo '</select>';
	echo '</p>';
}
echo '<input type="submit" name="submit">';

no doubt this is horribly inefficient as well. awaits flaming from php pros.

edit: here's a little demo of what it should look/behave like. (using my own arrays obviously)

http://www.marc2003.ukfsn.org/3dmark/test.php

 

[Voo]

Thanks for this marc2003, makes sense looking through the code there, i'll give it a whirl

 

[Voo]

Ok, using your code marc2003 i get the output i need which does look more efficient than the method I was using, however, how do i need feed that into a database?

The code i was using to do this (if it was one entry) is below but do i need to put this in a loop so it goes through all 58 player_id?

$query = mysql_query("INSERT INTO $fixtures ( fixture_draw_id, fixture_team_id, fixture_player_id ) values ( '$fixture_draw_id', '$fixture_team_id', '$fixture_player_id' )");

 

[marc2003]

well you have an array

$_POST['names']

to work with. you can loop through it using foreach as above and it contains player_id => team_id.

i guess you'd have to loop through it and add each each record in a separate query. my overall php/mysql is weak so i haven't a clue if there is another way.

are you sure you really want a form with 58 inputs on?

 

[Voo]

I already have it setup to do 1 input at a time but once a month all 58 names would need a team assigned to them so it makes sense to have all 58 on a page to make the process quicker.