SQL not updating multiple databases...whats wrong with my sql?

Question

I'm trying to run one SQL command to update multiple databases. If I copy 'n paste sql code below directly into PHPMYADMIN it executes just fine, but when I run the sql through php it doesn't update?

If I run the updates for each database separately via my php script they both update fine, so I'm confused?

What am I doing wrong?

SQL Code:

UPDATE rst.users a, rst.user_type b 
SET a.first_name='Timsd',a.last_name='Lebaronsd',a.password='timsd',
 a.email='[email protected]', a.user_type_id='5',a.language_code='en_US', 
   a.timezone='Pacific/Midway', create_ts = '2010-07-16 12:33:31' 
WHERE a.user_type_id = b.user_type_id AND b.account_id = 1 AND a.users_id = 90; 

use externalusers; 

UPDATE externalusers.user 
SET fullname="'Timsd' 'Lebaronsd'", emailaddress="'[email protected]'" 
WHERE rst_id = 90 AND rst_account_id = 1;

Naktibalda · Accepted Answer · 2010-07-16 18:49:07Z

3

mysql_query() and similar functions can't execute multiple statements for security reasons.
Use mysqli_multi_query() if you really want to execute multiple statements with single call.

P.S. It's not a PHP feature, but a feature of mysql C API.

answered Jul 16, 2010 at 18:49

Naktibalda

14.2k5 gold badges37 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ronedog Over a year ago

Could I place the SQL inside a stored procedure, then in the 1st sql statement call the stored procedure that would do the update to the other database?

Ronedog Over a year ago

Ok, I dumped the stored procedures idea and decided to implement the mysqli solution. Works great now. Thanks for your help.

Cfreak · Accepted Answer · 2010-07-16 18:48:11Z

0

PHP has a security feature that prevents you from executing multiple queries with a single mysql_query() call. Unfortunately there's no way around it with the native mysql_* functions.

However I believe you can do it if you use PDO or MySQLi. I highly recommend PDO.

answered Jul 16, 2010 at 18:48

Cfreak

19.4k6 gold badges52 silver badges64 bronze badges

2 Comments

Ronedog Over a year ago

Could I place the SQL inside a stored procedure, then in the 1st sql statement call the stored procedure that would do the update to the other database?

Cfreak Over a year ago

you could or you could make two database connections.

Toby Allen · Accepted Answer · 2010-07-16 20:55:41Z

0

To add to answers from others its important to say that you are not actually executing a single SQL statement but 2 (or actually probably 3), one after the other. PHPMYADMIN is simply breaking them on ; and executing them in sequence.

answered Jul 16, 2010 at 20:55

Toby Allen

11.3k12 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

SQL not updating multiple databases...whats wrong with my sql?

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related