How do you do multiple SQL statements in one mysql_query?
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开发者_JAVA技巧 deprecated and shouldn't be used.
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.
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.
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.
精彩评论