Page 1 of 1
Multiple table update
Posted: Tue Aug 04, 2009 12:57 am
by Dynex
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
Posted: Tue Aug 04, 2009 7:23 am
by jackpf
I believe you need to give each table an alias when updating multiple tables.
Re: Multiple table update
Posted: Tue Aug 04, 2009 1:23 pm
by Dynex
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]
Re: Multiple table update
Posted: Tue Aug 04, 2009 1:30 pm
by jackpf
Re: Multiple table update
Posted: Tue Aug 04, 2009 1:44 pm
by Dynex
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]
Re: Multiple table update
Posted: Tue Aug 04, 2009 1:46 pm
by jackpf
What's the error?
Re: Multiple table update
Posted: Tue Aug 04, 2009 1:53 pm
by Dynex
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.
Re: Multiple table update
Posted: Tue Aug 04, 2009 1:59 pm
by jackpf
I don't think you need the comma before "where".
Re: Multiple table update
Posted: Tue Aug 04, 2009 2:03 pm
by Dynex
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...
Re: Multiple table update
Posted: Wed Aug 05, 2009 12:05 am
by Dynex
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]
Re: Multiple table update
Posted: Wed Aug 05, 2009 1:51 pm
by Dynex
Got it working. Thanks people who helped.