Array and loop Help Needed

[ptmuldoon]

I've put together the below code, but something's amiss somewhere. The code's suppose to do the following:

1. get all the information from a 'games' table. One of the fields in the table is id
2. Then it should loop through the database, and get each table of game_XX
3. Then look at each game_XX table and get the current, next and last players.

Currently, its listing each game, but the the current, next, and last player is screwed up. I think I may have the query screwed up to the each game, but unsure. Or maybe theres a simpler approach, but with my limited skills thats all I could figure out at this point.

$sql = "SELECT * FROM games WHERE id > 1";
$result = mysql_query($sql);
$games = array();  

for ($i = 1; $i < mysql_num_rows($result); $i++) {

//Get the Games
$games[$i] = mysql_fetch_assoc($result);

//Get the Individual Game Data
$sql = "SELECT * FROM game_".$games[$i]['id']." WHERE id != 1 AND state != 'dead' ";
$result2 = mysql_query($sql);
$player_data = array();

while ($row = mysql_fetch_assoc($result2)) {
		$player_data[] = $row;
}

for ($row = 0; $row < mysql_num_rows($result2); $row++){
	foreach($player_data[$row] as $key => $value){    

		// Get the Current Player Info
		if($player_data[$row]['state'] == 'trading'){
			$cid = $player_data[$row]['id'];
			$cplayer = $player_data[$row]['player'];
		}

		// Get the Next Player Info.
		if($player_data[$row]['state'] == 'trading'){
			$next = $row + 1;
			if($next = $row < mysql_num_rows($result)){
				$next = 2;
			}
			$nid = $player_data[$next]['id'];
			$nplayer = $player_data[$next]['player'];
		}

		// Get the Last Player Info.
			if($player_data[$row]['state'] == 'trading'){
			$last = $row - 1;
			if($last < 0){
				$last = $row < mysql_num_rows($result);
			}
			$lid = $player_data[$last]['id'];
			$lplayer = $player_data[$last]['player'];
		} 		
	}	
}
}

Also, if it matters, I'm working with the Smarty XTemplate, so the above is later beign parsed as follows:

// assign array data
$xtpl->assign('GAME', $games[$i]);
$xtpl->assign('CPLAYER', $cplayer);
$xtpl->assign('NPLAYER', $nplayer);
$xtpl->assign('LPLAYER', $lplayer);