Query two tables and Update field

[ptmuldoon]

Hi:

New here to the boards, and hoping somebody can help me out. I'm pretty new at php, and think I'm close but not sure.

I'm trying to add to a users table field, the number of players the user has faced in a game. So I'm trying to count the number of players in a game_xx table, and then add that number to the users table. The game table users 'player' name, and the users table users a 'login' name. Thus, I'm pretty sure I need to match the player name with the login name as well.

I think I'm close but the below code is not adding it.

Thanks as well in advance for your help 🙂
PT

// Called from another file, but explains the get_one function
function get_one($string) {
	$q = mysql_query($string) or die('Failed to get_one: '.mysql_error());
	while ($row = mysql_fetch_array($q)) { $result = $row[0]; }
	return $result;
}

// Add total players in game to user table
$sql = 'SELECT player FROM game_'.$_SESSION['game_id'];
$ply_name = get_one($sql);

$sql = 'SELECT login FROM users';
$login = get_one($sql);

$sql = 'SELECT COUNT(player) FROM game_'.$_SESSION['game_id'];
$players = get_one($sql);

$sql = 'SELECT total_players FROM users WHERE \''.$login.'\' = \''.$ply_name.'\' ';
$total_plyr = get_one($sql);
$total_player = "$total_plyr + $players";

$sql = 'UPDATE users SET total_players = '.$total_player.' WHERE \''.$login.'\' = \''.$ply_name.'\' ';
$result = get_one($sql);

[greatepier]

(edited) sorry posted to wrong thread

[bpat1434]

Do you not have keys for each table? If you don't, I'd suggest you re-think your databases. Consider this situation:

Player A registers and creates an account. Player A then plays XX games and creates some really awesome stats for himself. He continues on his upward path until one day he realizes his name doesn't fit him anymore. He decides to change it. He updates his account and now his name is no longer "Player A" but rather "A+Playa". Now, all the stats for "Player A" aren't associated with "A+Playa" and someone can register using "Player A" and obtain the stats of the old "Player A" account.

Now, the easy way around this is to have your users table have a key (ID_USER) which is a foreign key to the games_XX table. Inside the games_XX table you have a field instead of the players actual name, it's their ID. Now, if any updates need to be made to the players name or registration info, his stats won't go out of whack.

Now, to help us more, we'd need to see how your tables are set up.

[bpat1434]

Do you not have keys for each table? If you don't, I'd suggest you re-think your databases. Consider this situation:

Player A registers and creates an account. Player A then plays XX games and creates some really awesome stats for himself. He continues on his upward path until one day he realizes his name doesn't fit him anymore. He decides to change it. He updates his account and now his name is no longer "Player A" but rather "A+Playa". Now, all the stats for "Player A" aren't associated with "A+Playa" and someone can register using "Player A" and obtain the stats of the old "Player A" account.

Now, the easy way around this is to have your users table have a key (ID_USER) which is a foreign key to the games_XX table. Inside the games_XX table you have a field instead of the players actual name, it's their ID. Now, if any updates need to be made to the players name or registration info, his stats won't go out of whack.

Now, to help us more, we'd need to see how your tables are set up.