How do you do multiple SQL statements in one mysql_query?

Question

Say I wanted to do UPDATE table SET name = 'bob' and UPDATE table SET age = 55 WHERE name = 'jim' how do I do them in the same mysql_query()?

EDIT: Since this question has a fair amount of views I'd like to point out that as of PHP 5.5 mysql_query and other mysql_* functions are now deprecated and shouldn't be used.

mysql_query() (and PHP's mysql driver in general) do not support multiple queries in a single query() call for security reasons: It eliminates one of the SQL injection attack vectors. In other words: YOU CAN'T DO IT. — Marc B, Jul 25 '11 at 19:46

score 13 · Accepted Answer · answered Jul 25 '11 at 19:39

I've never tried this, but I think you can use mysqli::multi_query. One of the good things about mysql_query rejecting multiple statements is that it immediately rules out some of the more common SQL injection attacks, such as adding '; DELETE FROM ... # to a statement. You might therefore want to be careful with multiple statements.

score 8 · Answer 2 · 2011-08-16T21:08:24.023

8

As it says on the top of the manual:

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

edited Aug 16 '11 at 21:08

answered Jul 25 '11 at 19:25

10

You can get derision pretty much anywhere else on the internet - Cheers to Leandro for *not* bringing it to SO. :P – Dan J Jul 25 '11 at 19:30

score 2 · Answer 3 · answered Jul 25 '11 at 19:34

You can do "conditional" update in this way:

create table test (
id int not null auto_increment primary key,
name varchar(50),
age tinyint
) engine = myisam;

insert into test (name) values ('jim'),('john'),('paul'),('mike');


update test
set age =
case 
when name = 'jim' then 10
when name = 'paul' then 20
else 30
end

Hope that it helps you.

How do you do multiple SQL statements in one mysql_query?

3 Answers3

Linked

Related