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
This should answer your question - http://www.brainbell.com/tutors/php/php ... eries.html.

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.