How can multiple MySQL queries be efficiently executed in PHP using PDO?

When executing multiple MySQL queries in PHP using PDO, you can use prepared statements and transactions to ensure efficient and secure execution. By preparing the queries once and then executing them multiple times with different parameters, you can reduce overhead and improve performance. Additionally, wrapping the queries in a transaction can help maintain data integrity by ensuring that either all queries are executed successfully or none at all. 

// Establish a connection to the database
$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'username';
$password = 'password';
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}

// Begin a transaction
$pdo->beginTransaction();

try {
    // Prepare the first query
    $stmt1 = $pdo->prepare("INSERT INTO table1 (column1) VALUES (:value1)");
    
    // Bind parameters and execute the first query
    $value1 = 'some value';
    $stmt1->bindParam(':value1', $value1);
    $stmt1->execute();
    
    // Prepare the second query
    $stmt2 = $pdo->prepare("INSERT INTO table2 (column2) VALUES (:value2)");
    
    // Bind parameters and execute the second query
    $value2 = 'another value';
    $stmt2->bindParam(':value2', $value2);
    $stmt2->execute();
    
    // Commit the transaction if all queries were successful
    $pdo->commit();
} catch (PDOException $e) {
    // Rollback the transaction if any query fails
    $pdo->rollBack();
    die('Error executing queries: ' . $e->getMessage());
}

Keywords

PHP PDO MySQL multiple queries efficiency

Related Questions