PHP/MySQL With Multiple Queries

php/mysql with multiple queries

Thanks to php.net I've come up with a solution : you have to use (mysqli_multi_query($link, $query)) to run multiple concatenated queries.

 /* create sql connection*/
$link = mysqli_connect("server", "user", "password", "database");

$query = "SQL STATEMENTS;"; /*  first query : Notice the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS;"; /* Notice the dot before = and the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS;"; /* Notice the dot before = and the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS"; /* last query : Notice the dot before = at the end ! */

/* Execute queries */

if (mysqli_multi_query($link, $query)) {
do {
    /* store first result set */
    if ($result = mysqli_store_result($link)) {
        while ($row = mysqli_fetch_array($result)) 

/* print your results */    
{
echo $row['column1'];
echo $row['column2'];
}
mysqli_free_result($result);
}   
} while (mysqli_next_result($link));
}

EDIT - The solution above works if you really want to do one big query but it's also possible to execute as many queries as you wish and execute them separately.

$query1 = "Create temporary table A select c1 from t1"; 
$result1 = mysqli_query($link, $query1) or die(mysqli_error());

$query2 = "select c1 from A"; 
$result2 = mysqli_query($link, $query2) or die(mysqli_error());

while($row = mysqli_fetch_array($result2)) {

echo $row['c1'];
    }  

How to Select multiple queries in PHP to MySQL

You can resolve the problem with Mysql UNION statement.

$query = "(SELECT * FROM `table` WHERE `ip` LIKE '1.1.1.1') UNION (SELECT * FROM `table` WHERE `ip` LIKE '2.2.2.2')";

MySQL multiple queries / best solution to get all passed categories

In your case you can try:

SELECT p.*,ANY_VALUE(t.slug) AS slug from posts p 
INNER JOIN relationships r ON r.objectId = p.id
INNER JOIN terms t ON t.id = r.termId
WHERE t.slug IN ('art','poetry','modern','sculpture') 
GROUP BY p.id having count(*) > 1 OR slug in ('art','poetry')

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

Mysql slow with multiple queries at same time

Add a PK column and index all the columns where parameters are passing in queries. Index with unique or normal as applicable.

PHP MySQL Multiple SQL Queries or JOIN for each query

If you want to get id, category_name and product_name each time, you should make ONE method and avoid to make one select, then a foreach on each product and an other select to get all value.

So try something like this maybe :

function getProductsData() {

    $querie = 'SELECT 
                 p.id, p.name,
                 pc.name as Category
               FROM products as p
               LEFT JOIN product_categories as pc on pc.id = p.category_id
               ORDER BY p.name; -- optionnal
              ';

    // Get the data and return them
    // You should get an array with one row = 
    // id (of the product) / name (of the product) / Category (the name of the category)


}

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.

How to run multiple insert query in SQL using PHP in one go?

The problem is, I need to insert data to first table and if its successfully inserted then only run query for the second table.

This means you need a transaction.

A transaction is a set of queries that either all execute ok or if one fails - they all fail. This is to ensure you don't end up with crap data in your tables.

Do not

• Do not use multiquery.
• Do not use mysql_* function(s).
• Do not use bulk inserts.

People telling you to do that just have absolutely no clue what they're doing, ignore them.

Do

• Use PDO
• Use prepared statements
• Prepare the statement(s) ONCE, use them MULTIPLE times

Sample code - do NOT copy paste

$dsn = 'mysql:dbname=testdb;host=127.0.0.1;charset=utf8mb4';
$user = 'dbuser';
$password = 'dbpass';

$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$insert['first'] = $pdo->prepare("INSERT INTO table SET col1 = :val, col2 = :val2");
$insert['second'] = $pdo->prepare("INSERT INTO another_table SET col1 = :val, col2 = :val2"); 

$pdo->beginTransaction();

$insert['first']->bindValue(':val', 'your value');
$insert['first']->bindValue(':val2', 'anothervalue');
$insert['first']->execute();

$insert['second']->bindValue(':val', 'your value');
$insert['second']->bindValue(':val2', 'anothervalue');
$insert['second']->execute();

$pdo->commit();

The code above will save the data in two tables ONLY if both inserts are successful.

---

Related Topics