Executing Multiple SQL Queries in One Statement with PHP

PHP code to execute multiple queries with a single mysql_query() call

From 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.

Move to mysqli, which has support for multiple statements.

Executing multiple SQL queries in one statement with PHP

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

Running two separate SQL queries on one php page

I think your solution is simple, just change either one of your $sql to another name of variable (ex: $sql1), so you can fetch both of your query with $sql and $sql1, the undefined index happen because you store new query to the same variable, which replace the first query, so with 2 queries with different name it will not cause the undefined index, also, you don't have to separate the query, you can use

$conn = dbConnect('read', 'pdo');
// prepare the SQL query
$sql = "SELECT * FROM wbcsightings ........
....
$sql1 = "'SELECT * FROM wbcnews.......
....

and for fetch you can use

<?php $i=0; foreach ($conn->query($sql) as $row) { if($i==3) break;?>
......
<?php $i=0; foreach ($conn->query($sql1) as $row) { if($i==3) break;?>
......

hope this will help

Run multiple SQL Server queries in php

You can not pass more than one statement to sqlsrv_query(). In your case you have two options:

• Generate one complex T-SQL statement. In this case, the number of placeholders must match the number of passed parameters.
• Execute each statement separately

Complex statement:

<?php
...
$sql = "
    INSERT INTO TABLE (column) VALUES (?); 
    INSERT INTO TABLE_NEW(column, column, column) Values (?, 'INSERT', 'WEBSERVER')
";
$params = array(
    $_POST["addSomething"],
    $_POST["addSomething"]
);
$stmt = sqlsrv_query($conn, $sql, $params);
if ($stmt === false ) {
   # Your code here
} else {
   # Your code here
}
...
?>

Notes:

PHP Driver for SQL Server supports multiple result sets. If you use one complex statement and one or more of your statements are SELECT statements, you need to call sqlsrv_next_result() to make active the next result (first result is active by default).

Execute multiple queries at once in Mysql using PHP

What you are looking for is TRANSACTIONS assuming you are not using MyISAM since it does not supports Transactions.

The concept of transactions is that either all the queries will execute or no query would execute at all.

In simple words all-or-nothing is what Transactions do

This is a basic example using mysqli

mysqli_query("START TRANSACTION");

$query1 = mysqli_query("INSERT INTO TABLE1(id) VALUES(2)");
$query2 = mysqli_query("INSERT INTO TABLE2(id) VALUES(3)");

if ($query1 and $query2) {
   mysqli_query("COMMIT"); //Commits the current transaction
} else {        
   mysqli_query("ROLLBACK");//Even if any one of the query fails, the changes will be undone
}

NOTE: This was just a simple example.It would better if you implement using try and catch blocks handling then exceptions properly.

Take a look at PHP DOCS

---

Related Topics