How Can I Execute Multiple SQL Queries in a Single Statement Using PHP?

Executing Multiple SQL Queries in One Statement with PHP

In this programming conundrum, the user seeks a method to combine multiple SQL queries into a single statement. They have provided sample code that consists of three separate queries:

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

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

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

Solution

To execute multiple SQL queries in one statement using PHP and MySQL, the key is to pass 65536 as the fifth parameter during the mysql_connect initialization. Here's an 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
");

In this example, all of the queries will be executed sequentially as part of a single statement. It's important to note that subsequent commands such as mysql_fetch_*, mysql_num_rows, and mysql_affected_rows will only be valid for the first executed query.

mysql_close Question

Regarding the question about closing the database connection, it is generally recommended to call mysql_close($db) after finishing all SQL queries to release the connection and free up resources.

The above is the detailed content of How Can I Execute Multiple SQL Queries in a Single Statement Using PHP?. For more information, please follow other related articles on the PHP Chinese website!