Multiple table update

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Dynex
Forum Commoner
Posts: 31
Joined: Mon Feb 09, 2009 7:24 pm

Multiple table update

Post 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
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Multiple table update

Post by jackpf »

I believe you need to give each table an alias when updating multiple tables.
Dynex
Forum Commoner
Posts: 31
Joined: Mon Feb 09, 2009 7:24 pm

Re: Multiple table update

Post 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]
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Multiple table update

Post by jackpf »

This should answer your question - http://www.brainbell.com/tutors/php/php ... eries.html.
Dynex
Forum Commoner
Posts: 31
Joined: Mon Feb 09, 2009 7:24 pm

Re: Multiple table update

Post 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]
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Multiple table update

Post by jackpf »

What's the error?
Dynex
Forum Commoner
Posts: 31
Joined: Mon Feb 09, 2009 7:24 pm

Re: Multiple table update

Post 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.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Multiple table update

Post by jackpf »

I don't think you need the comma before "where".
Dynex
Forum Commoner
Posts: 31
Joined: Mon Feb 09, 2009 7:24 pm

Re: Multiple table update

Post 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...
Dynex
Forum Commoner
Posts: 31
Joined: Mon Feb 09, 2009 7:24 pm

Re: Multiple table update

Post 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]
Dynex
Forum Commoner
Posts: 31
Joined: Mon Feb 09, 2009 7:24 pm

Re: Multiple table update

Post by Dynex »

Got it working. Thanks people who helped.
Post Reply