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?

Answer 1

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); "); 

Answer 2

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