Executing multiple SQL queries in one statement with PHP

Question

How to join those multiple queries into one (can I?)

$query = "DELETE FROM aktywne_kody WHERE kodsms ='$kodSMSgracza' AND typkodu ='$id'";
mysql_query($query) or die(mysql_error());

$query = "INSERT INTO uzyte_kody (gracz, kodsms, typkodu) VALUES ('$nickGracza', '$kodSMSgracza', '$id')";
mysql_query($query) or die("Błąd MySQL X04");

$query = "INSERT INTO do_odebrania (gracz, itemDATA, itemQTY) VALUES ('$nickGracza', '$itemDATA', '$itemQTY')";
mysql_query($query) or die("Błąd MySQL X05");

By the way is it better if I do mysql_close($db) after all queries are done?

Make one $query and just make sure you add an ; after each line. — Pitchinnate
– Pitchinnate, Commented Dec 20, 2012 at 21:35
Why do you need this? Most of the time readability is more important than little efficiencies. — Muatik
– Muatik, Commented Dec 20, 2012 at 21:35
Please don't use mysql_query in new applications. It's deprecated and dangerous. At the very least use PDO to ensure your data is escaped properly before you end up in severe trouble because of a SQL injection bug. — tadman
– tadman, Commented Dec 20, 2012 at 21:40
Joining them with ; gives 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 'INSERT INTO uzyte_kody (gracz, kodsms, typkodu) VALUES ('UltraMC', '00000000', '' at line 1 — John Smith
– John Smith, Commented Dec 20, 2012 at 21:50
To follow on from @tadman about avoiding mysql_query, mysqli gives you multi_query, which allows precisely this. — TRiG
– TRiG, Commented May 31, 2019 at 9:42

Kerem · Accepted Answer · 2015-12-05 03:12:11Z

35

Pass 65536 to mysql_connect as 5th parameter.

Example:

$conn = mysql_connect('localhost','username','password', true, 65536 /* here! */) 
    or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
    INSERT INTO table1 (field1,field2) VALUES(1,2);

    INSERT INTO table2 (field3,field4,field5) VALUES(3,4,5);

    DELETE FROM table3 WHERE field6 = 6;

    UPDATE table4 SET field7 = 7 WHERE field8 = 8;

    INSERT INTO table5
       SELECT t6.field11, t6.field12, t7.field13
       FROM table6 t6
       INNER JOIN table7 t7 ON t7.field9 = t6.field10;

    -- etc
");

When you are working with mysql_fetch_* or mysql_num_rows, or mysql_affected_rows, only the first statement is valid.

For example, the following codes, the first statement is INSERT, you cannot execute mysql_num_rows and mysql_fetch_*. It is okay to use mysql_affected_rows to return how many rows inserted.

$conn = mysql_connect('localhost','username','password', true, 65536) or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
    INSERT INTO table1 (field1,field2) VALUES(1,2);
    SELECT * FROM table2;
");

Another example, the following codes, the first statement is SELECT, you cannot execute mysql_affected_rows. But you can execute mysql_fetch_assoc to get a key-value pair of row resulted from the first SELECT statement, or you can execute mysql_num_rows to get number of rows based on the first SELECT statement.

$conn = mysql_connect('localhost','username','password', true, 65536) or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
    SELECT * FROM table2;
    INSERT INTO table1 (field1,field2) VALUES(1,2);
");

edited Dec 5, 2015 at 3:12

Kerem

11.6k5 gold badges61 silver badges58 bronze badges

answered Dec 21, 2012 at 0:34

Husni

1,0659 silver badges12 bronze badges

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

4 Comments

Marcin Orlowski Over a year ago

If anyone wonders why this works - it's not a trick it's just undocumented (in PHP docs) feature of mySQL client. In sources you can easily find #define CLIENT_MULTI_STATEMENTS 65536 /* Enable/disable multi-stmt support */ which is exactly what OP wanted.

Jonas Äppelgran Over a year ago

For code readability; Pass CLIENT_MULTI_STATEMENTS as mysql_connect's 5th parameter.

Pacerier Over a year ago

@Husni, Why do you use a "magic number" like 65536 instead of proper constant names?

kiwicomb123 Over a year ago

why not use a "prepare" statement to avoid SQL injection attacks? (Its only a bit of extra code in PHP)

Alupotha · Accepted Answer · 2016-01-06 05:18:53Z

This may be created sql injection point "SQL Injection Piggy-backed Queries". attackers able to append multiple malicious sql statements. so do not append user inputs directly to the queries.

Security considerations

The API functions mysqli_query() and mysqli_real_query() do not set a connection flag necessary for activating multi queries in the server. An extra API call is used for multiple statements to reduce the likeliness of accidental SQL injection attacks. An attacker may try to add statements such as ; DROP DATABASE mysql or ; SELECT SLEEP(999). If the attacker succeeds in adding SQL to the statement string but mysqli_multi_query is not used, the server will not execute the second, injected and malicious SQL statement.

PHP Doc

Jonas Äppelgran · Accepted Answer · 2014-04-08 08:59:54Z

3

With mysqli you're able to use multiple statements for real using mysqli_multi_query().

Read more on multiple statements in the PHP Docs.

answered Apr 8, 2014 at 8:59

Jonas Äppelgran

2,74729 silver badges34 bronze badges

Comments

MayTheSchwartzBeWithYou · Accepted Answer · 2012-12-20 21:43:20Z

0

You can just add the word JOIN or add a ; after each line(as @pictchubbate said). Better this way because of readability and also you should not meddle DELETE with INSERT; it is easy to go south.

The last question is a matter of debate, but as far as I know yes you should close after a set of queries. This applies mostly to old plain mysql/php and not PDO, mysqli. Things get more complicated(and heated in debates) in these cases.

Finally, I would suggest either using PDO or some other method.

answered Dec 20, 2012 at 21:43

MayTheSchwartzBeWithYou

1,1771 gold badge16 silver badges32 bronze badges

Collectives™ on Stack Overflow

Executing multiple SQL queries in one statement with PHP

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related