Reusing a set of results

[Maskeeper]

Hello Again,

So:
- I've created a form which contains 7 pulldown menus each consisting of a roster of names.

- Each of these 7 pulldowns is identical, and is created by querying the database for a list of names meeting a specific criteria, and looping through them- adding each one as an option in the form pulldown.

Simple enough.

I have a for loop that controls the 7 passes required to create these 7 pulldowns. My original plan was to query the database once, and then walk through the list in each of the 7 iterations of the loop. However I couldn't seem to figure out how to reset the pointer back to the head of the results without redoing the query.

I would assume that at the end of each loop there would be a command I could send that would reset the results back to the top row so I could walk through and extract each of them again.

For the sake of completeness, here's my code that works currently, but is less than elegant. I'd like to move the query outside the loop and just reuse the $resultLineup1 material over and over without having to re query each time:

for ($i=1;$i<=7;$i++)
{
	echo "<select name='player$i'>\n";
	echo "<option value='0'></option>";

$queryLineup1 = "SELECT * FROM GAME_Lineup, LEAGUE_Roster 
	WHERE GAME_Lineup.Game_Date = '$today' 
	AND GAME_Lineup.Team_ID = '$Team_1' 
	AND GAME_Lineup.Player_Eligible = '1'
	AND GAME_Lineup.Player_ID = LEAGUE_Roster.Player_ID
	ORDER BY Player_FirstNm";
$resultLineup1 = mysqli_query($cxn,$queryLineup1)
	or die ("Couldn't execute queryLineup1.");



while ($row = mysqli_fetch_assoc($resultLineup1))
{
	extract($row);
	echo "<option value='$Player_ID'>$Player_FirstNm $Player_LastNm</option>";
}
echo "</select>\n";
echo "<BR>";
}

[cahva]

You have forgotten these magical things that are called variables 😉

Ofcourse when the options are always the same, it's only wise to put them to a variable(array in this case).

$queryLineup1 = "SELECT * FROM GAME_Lineup, LEAGUE_Roster 
		WHERE GAME_Lineup.Game_Date = '$today' 
		AND GAME_Lineup.Team_ID = '$Team_1' 
		AND GAME_Lineup.Player_Eligible = '1'
		AND GAME_Lineup.Player_ID = LEAGUE_Roster.Player_ID
		ORDER BY Player_FirstNm";
$resultLineup1 = mysqli_query($cxn,$queryLineup1)
    or die ("Couldn't execute queryLineup1.");

$options = array();
$options[] = "<option value='0'></option>";
while ($row = mysqli_fetch_assoc($resultLineup1))
{
    $options[] = "<option value='{$row['Player_ID']}'>{$row['Player_FirstNm']} {$row['Player_LastNm']}</option>";
}
$optjoin  = implode("\n",$options);

for ($i=1;$i<=7;$i++)
{
	echo "<select name='player$i'>\n";
	echo $optjoin;
	echo "</select>\n";
	echo "<BR>";
}

[NogDog]

While I'd probably go with the above idea of creating an array that can be iterated through as needed (or an object, perhaps?), it should be noted that you can reset the pointer of a result set via the [man]mysqli_data_seek/man function.

[bradgrafelman]

NogDog is right - you can jump around in the result set.

I would also point out, however, that if you store the data in an array that you can iterate through as needed, it would prevent you from having to send and receive data to/from the DB server multiple times per result set.

This could be especially beneficial in instances where the DB server and the web server are two different machines.