Multiple table update
Moderator: General Moderators
Multiple table update
Hey guys, im trying to update multiple tables with the same update button. Here is the code so far: http://pastebin.com/m302cbd78
Re: Multiple table update
I believe you need to give each table an alias when updating multiple tables.
Re: Multiple table update
What do you mean each table needs an alias?
Updated Code:
[sql]$db->Query("UPDATE `players`,`playersSettings`,`playersStats` SET `realName` = '".$db->escapeString($_POST['realName'])."', `screenName` = '".$db->escapeString($_POST['screenName'])."', `picture` = '".$db->escapeString($_POST['picture'])."', `maxClicks` = '".$db->escapeString($_POST['maxClicks'])."', `maxBankDepositPercent` = '".$db->escapeString($_POST['maxBankDepositPercent'])."', `maximumBankTotal` = '".$db->escapeString($_POST['maximumBankTotal'])."', `maximumBankDeposits` = '".$db->escapeString($_POST['maximumBankDeposits'])."', `tgLastRoom` = '".$db->escapeString($_POST['tgLastRoom'])."', `cityLastRoom` = '".$db->escapeString($_POST['cityLastRoom'])."', `ip` = '".$db->escapeString($_POST['ip'])."', `maxAttacksPerTurn` = '".$db->escapeString($_POST['maxAttacksPerTurn'])."', `moneyPerTurn` = '".$db->escapeString($_POST['moneyPerTurn'])."', `parent` = '".$db->escapeString($_POST['parent'])."', `bp_max` = '".$db->escapeString($_POST['bp_max'])."', `last_login` = '".$db->escapeString($_POST['last_login'])."', `days_inactive` = '".$db->escapeString($_POST['days_inactive'])."', WHERE (players.id = '".$player->id."' AND players.id = playersSettings.id AND players.id = playersStats.id) ") OR die(mysql_error()); [/sql]
Updated Code:
[sql]$db->Query("UPDATE `players`,`playersSettings`,`playersStats` SET `realName` = '".$db->escapeString($_POST['realName'])."', `screenName` = '".$db->escapeString($_POST['screenName'])."', `picture` = '".$db->escapeString($_POST['picture'])."', `maxClicks` = '".$db->escapeString($_POST['maxClicks'])."', `maxBankDepositPercent` = '".$db->escapeString($_POST['maxBankDepositPercent'])."', `maximumBankTotal` = '".$db->escapeString($_POST['maximumBankTotal'])."', `maximumBankDeposits` = '".$db->escapeString($_POST['maximumBankDeposits'])."', `tgLastRoom` = '".$db->escapeString($_POST['tgLastRoom'])."', `cityLastRoom` = '".$db->escapeString($_POST['cityLastRoom'])."', `ip` = '".$db->escapeString($_POST['ip'])."', `maxAttacksPerTurn` = '".$db->escapeString($_POST['maxAttacksPerTurn'])."', `moneyPerTurn` = '".$db->escapeString($_POST['moneyPerTurn'])."', `parent` = '".$db->escapeString($_POST['parent'])."', `bp_max` = '".$db->escapeString($_POST['bp_max'])."', `last_login` = '".$db->escapeString($_POST['last_login'])."', `days_inactive` = '".$db->escapeString($_POST['days_inactive'])."', WHERE (players.id = '".$player->id."' AND players.id = playersSettings.id AND players.id = playersStats.id) ") OR die(mysql_error()); [/sql]
Re: Multiple table update
This should answer your question - http://www.brainbell.com/tutors/php/php ... eries.html.
Re: Multiple table update
Ok, So i added the aliases but still get a syntax error on the WHERE ...
[sql]$db->Query("UPDATE players a, playersSettings b, playersStats c SET `a.username` = '".$db->escapeString($_POST['username'])."', `b.realName` = '".$db->escapeString($_POST['realName'])."', `b.screenName` = '".$db->escapeString($_POST['screenName'])."', `b.picture` = '".$db->escapeString($_POST['picture'])."', `b.maxClicks` = '".$db->escapeString($_POST['maxClicks'])."', `b.maxBankDepositPercent` = '".$db->escapeString($_POST['maxBankDepositPercent'])."', `b.maximumBankTotal` = '".$db->escapeString($_POST['maximumBankTotal'])."', `b.maximumBankDeposits` = '".$db->escapeString($_POST['maximumBankDeposits'])."', `b.tgLastRoom` = '".$db->escapeString($_POST['tgLastRoom'])."', `b.cityLastRoom` = '".$db->escapeString($_POST['cityLastRoom'])."', `b.ip` = '".$db->escapeString($_POST['ip'])."', `b.maxAttacksPerHour` = '".$db->escapeString($_POST['maxAttacksPerTurn'])."', `b.moneyPerHour` = '".$db->escapeString($_POST['moneyPerTurn'])."', `b.parent` = '".$db->escapeString($_POST['parent'])."', `b.bp_max` = '".$db->escapeString($_POST['bp_max'])."', `b.last_login` = '".$db->escapeString($_POST['last_login'])."', WHERE (a.id = '".$player->id."' AND players.id = playersSettings.id AND players.id = playersStats.id) ") OR die(mysql_error()); [/sql]
[sql]$db->Query("UPDATE players a, playersSettings b, playersStats c SET `a.username` = '".$db->escapeString($_POST['username'])."', `b.realName` = '".$db->escapeString($_POST['realName'])."', `b.screenName` = '".$db->escapeString($_POST['screenName'])."', `b.picture` = '".$db->escapeString($_POST['picture'])."', `b.maxClicks` = '".$db->escapeString($_POST['maxClicks'])."', `b.maxBankDepositPercent` = '".$db->escapeString($_POST['maxBankDepositPercent'])."', `b.maximumBankTotal` = '".$db->escapeString($_POST['maximumBankTotal'])."', `b.maximumBankDeposits` = '".$db->escapeString($_POST['maximumBankDeposits'])."', `b.tgLastRoom` = '".$db->escapeString($_POST['tgLastRoom'])."', `b.cityLastRoom` = '".$db->escapeString($_POST['cityLastRoom'])."', `b.ip` = '".$db->escapeString($_POST['ip'])."', `b.maxAttacksPerHour` = '".$db->escapeString($_POST['maxAttacksPerTurn'])."', `b.moneyPerHour` = '".$db->escapeString($_POST['moneyPerTurn'])."', `b.parent` = '".$db->escapeString($_POST['parent'])."', `b.bp_max` = '".$db->escapeString($_POST['bp_max'])."', `b.last_login` = '".$db->escapeString($_POST['last_login'])."', WHERE (a.id = '".$player->id."' AND players.id = playersSettings.id AND players.id = playersStats.id) ") OR die(mysql_error()); [/sql]
Re: Multiple table update
What's the error?
Re: Multiple table update
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE players.id = '217' AND players.id = playersSettings.id AND players.id = ' at line 19
So, it is passing the correct player.id in the first command(217). But im not sure about the rest.
So, it is passing the correct player.id in the first command(217). But im not sure about the rest.
Re: Multiple table update
I don't think you need the comma before "where".
Re: Multiple table update
Yup, that was the syntax error. But now its telling me " Unknown column 'a.username' in 'field list'"
So, the aliases must be set up wrong...or they didnt pick up...
So, the aliases must be set up wrong...or they didnt pick up...
Re: Multiple table update
Sorry, for got to post new code.
[sql] $db->Query("UPDATE players a, playersSettings b, playersStats c SET `a.username` = '".$db->escapeString($_POST['username'])."', `b.realName` = '".$db->escapeString($_POST['realName'])."', `b.screenName` = '".$db->escapeString($_POST['screenName'])."', `b.picture` = '".$db->escapeString($_POST['picture'])."', `b.maxClicks` = '".$db->escapeString($_POST['maxClicks'])."', `b.maxBankDepositPercent` = '".$db->escapeString($_POST['maxBankDepositPercent'])."', `b.maximumBankTotal` = '".$db->escapeString($_POST['maximumBankTotal'])."', `b.maximumBankDeposits` = '".$db->escapeString($_POST['maximumBankDeposits'])."', `b.tgLastRoom` = '".$db->escapeString($_POST['tgLastRoom'])."', `b.cityLastRoom` = '".$db->escapeString($_POST['cityLastRoom'])."', `b.ip` = '".$db->escapeString($_POST['ip'])."', `b.maxAttacksPerHour` = '".$db->escapeString($_POST['maxAttacksPerTurn'])."', `b.moneyPerHour` = '".$db->escapeString($_POST['moneyPerTurn'])."', `b.parent` = '".$db->escapeString($_POST['parent'])."', `b.bp_max` = '".$db->escapeString($_POST['bp_max'])."', `b.last_login` = '".$db->escapeString($_POST['last_login'])."' WHERE players.id = '".$db->escapeString($_POST['id'])."' AND playersSettings.id = players.id AND playersStats.id = players.id ") OR die(mysql_error());[/sql]
[sql] $db->Query("UPDATE players a, playersSettings b, playersStats c SET `a.username` = '".$db->escapeString($_POST['username'])."', `b.realName` = '".$db->escapeString($_POST['realName'])."', `b.screenName` = '".$db->escapeString($_POST['screenName'])."', `b.picture` = '".$db->escapeString($_POST['picture'])."', `b.maxClicks` = '".$db->escapeString($_POST['maxClicks'])."', `b.maxBankDepositPercent` = '".$db->escapeString($_POST['maxBankDepositPercent'])."', `b.maximumBankTotal` = '".$db->escapeString($_POST['maximumBankTotal'])."', `b.maximumBankDeposits` = '".$db->escapeString($_POST['maximumBankDeposits'])."', `b.tgLastRoom` = '".$db->escapeString($_POST['tgLastRoom'])."', `b.cityLastRoom` = '".$db->escapeString($_POST['cityLastRoom'])."', `b.ip` = '".$db->escapeString($_POST['ip'])."', `b.maxAttacksPerHour` = '".$db->escapeString($_POST['maxAttacksPerTurn'])."', `b.moneyPerHour` = '".$db->escapeString($_POST['moneyPerTurn'])."', `b.parent` = '".$db->escapeString($_POST['parent'])."', `b.bp_max` = '".$db->escapeString($_POST['bp_max'])."', `b.last_login` = '".$db->escapeString($_POST['last_login'])."' WHERE players.id = '".$db->escapeString($_POST['id'])."' AND playersSettings.id = players.id AND playersStats.id = players.id ") OR die(mysql_error());[/sql]
Re: Multiple table update
Got it working. Thanks people who helped.