PHP & MySQL PDO: PDO binding values for MySQL IN statement - Bind an array to an IN() condition?

The answer there was that, for a variable sized list in the in clause, you'll need to construct the query yourself.

Consider your array like:

$values = array(1, 2, 3, 4, 5); $count = count($values); $criteria = sprintf("?%s", str_repeat(",?", ($count ? $count - 1 : 0))); $sql = sprintf("DELETE FROM table where column NOT IN(%s)", $criteria); $pdo->sth = prepare($sql); $pdo->sth->execute($values); SQL should be: DELETE FROM table where column IN (?,?,?,?,?)

Rollback or Commit with PDO transaction using PHP and MySQL - SELECT query with PDO - PDO with INSERT INTO through prepared statements

A transaction should end with either a rollback() or a commit(), (only one of them). In case you are using MySQL, make sure you are not using MyISAM engine for tables, as it doesn't support transactions. It usually support InnoDB. Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.

<?php class DB { public static function transactional($closure) { $dbh = null; try { $dbh = new PDO("mysql:host=HOST_NAME;dbname=DB_NAME", "DB_USER_NAME", "DB_USER_PASSWORD"); /*** Set the PDO error mode to exception (will throw exception if any error occurred) ***/ /*** Follow the link for more info: http://php.net/manual/en/pdo.setattribute.php ***/ $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $dbh->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_EMPTY_STRING); /*** Transaction block starting here ***/ $dbh->beginTransaction(); $closure($dbh); $dbh->commit(); } catch(Throwable $ex) { $dbh->rollback(); throw $ex; } } } DB::transactional(function (PDO $PDO) { $PDO->prepare("INSERT INTO records (`RegistrationID`) VALUES(:RegistrationID)")->execute(['RegistrationID' => 'RegistrationID 1']); $PDO->prepare("INSERT INTO records (`RegistrationID`) VALUES(:RegistrationID)")->execute(['RegistrationID' => 'RegistrationID 2']); // $PDO->query used to get results $result = $PDO->prepare("SELECT * FROM records WHERE `RegistrationID` LIKE :RegistrationID"); $result->execute(['RegistrationID' => '%RegistrationID%']); echo "<pre>"; print_r($result->fetchAll()); echo "</pre>"; Below line will throw an exception and data will not persist into database. To persist data into database, comment below line. $x = null + null * $PDO->dd(); });

Output be as follows: Array ( [0] => Array ( [id] => 12 [0] => 12 [RegistrationID] => RegistrationID 1 [1] => RegistrationID 1 ) [1] => Array ( [id] => 13 [0] => 13 [RegistrationID] => RegistrationID 2 [1] => RegistrationID 2 ) )

PHP + MySQL transactions examples

MySLQL transactions can be used when you want to make sure all the statements you specify are executed. Only the tables of InnoDB storage engine support transactions.

In a transaction, if at least one statement fails, all the changes will be rolled back and database will be in its initial state (There are some statements that can not be rolled back: Will be discussed at the end).

In web applications, it’s common that you want to either run set of statements or do no change since success of only few statements can cause data corruption.

A transaction is a set of inter-dependent SQL statements that needs to execute in all-or-nothing mode. A transaction is successful if all SQL statements executed successfully. A failure of any statement will trigger the system to rollback to the  original state to avoid data inconsistency.

Suppose we have a process where need to execute 3 queries. If an error occurs in the second step, the third step should not continue and first step must reversed. In addition, if an error occurs in the third step, then first and second step must be reversed.

When you use PDO to create a connection to the MySQL database that supports the transaction, the auto-commit mode is set. It means that every query you issue is wrapped inside an implicit transaction.

Notice that not all storage engines in MySQL support transactions e.g., MyISAM does not support the transaction, however, InnoDB does. So you can't use MyISAM engine for MySQL if you want transaction support.

To handle MySQL transaction in PHP, you use the following steps:

• Start the transaction by calling the beginTransaction() method of the PDO object.
• Place the SQL statements and the  commit() method call in a try block.
• Rollback the transaction in the catch block by calling the rollBack() method of the PDO object.

Below is a simple PHP script that shows how we can achieve the functionality.

<?php
define("DB_DEFAULT_HOST", "localhost");
define("DB_DEFAULT_DATABASE", "test_database");
define("DB_DEFAULT_USER", "root");
define("DB_DEFAULT_PASSWORD", "");
$dbh = null;
try {
    $dbh = new PDO(
        "mysql:host=".DB_DEFAULT_HOST.";dbname=".DB_DEFAULT_DATABASE,
        DB_DEFAULT_USER,
        DB_DEFAULT_PASSWORD
    );
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->beginTransaction();

    $query = $dbh->prepare("SELECT COUNT(*) as count FROM student WHERE id > :id");
    $query->bindValue("id", 0);
    $query->execute();
    $count = $query->fetch()[0];
    echo "OLD COUNT=$count<br/>";

    $stmt = $dbh->prepare("INSERT INTO student (name,roll) VALUES(?,?)");
    $stmt->execute(['Name', 'Roll']);
    /* Will commit data to database, if you don't call this, data will not save */
    $dbh->commit();

    $count = $dbh->query("SELECT COUNT(*) as count FROM student WHERE id > 0")->fetch()[0];
    echo "NEW COUNT=$count<br/>";

    $students = $dbh->query("SELECT * FROM student WHERE id > 0")->fetchAll(PDO::FETCH_OBJ);
    echo "<pre>"; print_r($students); echo "</pre>";

    /* SEARCH CAPABILITY IN ARRAY DATA BIND */
    $id_list = [1, 2, 3];
    $in = rtrim(str_repeat('?,', count($id_list)), ',');
    $query = $dbh->prepare("SELECT * FROM student WHERE id in ($in)");
    $query->execute($id_list);
    $students = $query->fetchAll(PDO::FETCH_OBJ);
    echo "<pre>"; print_r($students); echo "</pre>"; die();
}
catch (\Exception $ex) {
    echo "MySQL_Error=" . $ex->getMessage();
    $dbh->rollBack();
}

And output is like below:

OLD COUNT=2
NEW COUNT=3
Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [name] => Prtiom First Year
            [roll] => P1
        )

    [1] => stdClass Object
        (
            [id] => 2
            [name] => Pritom Second Year
            [roll] => P2
        )

    [2] => stdClass Object
        (
            [id] => 21
            [name] => Some_Name
            [roll] => 3040
        )

)

Note that, with this idea, if a query fails, an Exception must be thrown:

• PDO can do that, depending on how you configure it
• See PDO::setAttribute
• and PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION
• You might have to test the result of the function used to execute a query, and throw an exception yourself.

Unfortunately, there is no magic involved. You cannot just put an instruction somewhere and have transactions done automatically: you still have to specific which group of queries must be executed in a transaction.

For example, quite often you'll have a couple of queries before the transaction (before the begin) and another couple of queries after the transaction (after either commit or rollback) and you'll want those queries executed no matter what happened (or not) in the transaction.

Rollback or Commit with PDO transaction using php and mysql

A transaction should end with either a rollback() or a commit(), (only one of them).
In case you are using MySQL, make sure you are not using MyISAM engine for tables, as it doesn't support transactions. It usually support InnoDB.
Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.

$dbh = null;
try {
    $dbh = new PDO("mysql:host=HOST_NAME;dbname=DB_NAME", "DB_USER_NAME", "DB_USER_PASSWORD");

    /*** Set the PDO error mode to exception (will throw exception if any error occurred) ***/
    /*** Follow the link for more info: http://php.net/manual/en/pdo.setattribute.php ***/
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_EMPTY_STRING);

    /*** Creating a test table if not exists ***/
    $table = "CREATE TABLE IF NOT EXISTS test_transaction ( " .
        "id MEDIUMINT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY, ".
        "name VARCHAR(25) NOT NULL, designation VARCHAR(25) NULL )";
    $dbh->exec($table);

    /*** Transaction block starting here ***/
    $dbh->beginTransaction();

    /*** Inserting some data ***/
    $dbh->exec("INSERT INTO test_transaction VALUES(NULL, 'Pritom #1', '')");
    $dbh->exec("INSERT INTO test_transaction VALUES(NULL, 'Pritom #2', '')");

    /*** The following query result 2 outputs ***/
    $result = $dbh->query("SELECT * FROM test_transaction");
    echo "<pre>";
    print_r($result->fetchAll());
    echo "</pre>";

    /*** Again inserting some more data ***/
    $dbh->exec("INSERT INTO test_transaction VALUES(NULL, 'Pritom #1', 'Designation #1')");
    $dbh->exec("INSERT INTO test_transaction VALUES(NULL, 'Pritom #2', 'Designation #2')");
    $dbh->exec("INSERT INTO test_transaction VALUES(NULL, 'Pritom #3', 'Designation #3')");

    /*** The following query result 5 outputs ***/
    $result = $dbh->query("SELECT * FROM test_transaction");
    echo "<pre>";
    print_r($result->fetchAll());
    echo "</pre>";

    /*** Throwing an exception to test if transaction really works ***/
    throw new Exception("All data will be erased during this transaction");
}
catch(Exception $ex) {
    $dbh->rollback();

    /*** The following query result 0 outputs as transaction failed ***/
    $result = $dbh->query("SELECT * FROM test_transaction");
    echo "<pre>";
    print_r($result->fetchAll());
    echo "</pre>";

    echo "Exception: ".$ex->getMessage();
    die();
}

Output be as follows:

Array
(
    [0] => Array
        (
            [id] => 29
            [0] => 29
            [name] => Pritom #1
            [1] => Pritom #1
            [designation] => 
            [2] => 
        )

    [1] => Array
        (
            [id] => 30
            [0] => 30
            [name] => Pritom #2
            [1] => Pritom #2
            [designation] => 
            [2] => 
        )

)

Array
(
    [0] => Array
        (
            [id] => 29
            [0] => 29
            [name] => Pritom #1
            [1] => Pritom #1
            [designation] => 
            [2] => 
        )

    [1] => Array
        (
            [id] => 30
            [0] => 30
            [name] => Pritom #2
            [1] => Pritom #2
            [designation] => 
            [2] => 
        )

    [2] => Array
        (
            [id] => 31
            [0] => 31
            [name] => Pritom #1
            [1] => Pritom #1
            [designation] => Designation #1
            [2] => Designation #1
        )

    [3] => Array
        (
            [id] => 32
            [0] => 32
            [name] => Pritom #2
            [1] => Pritom #2
            [designation] => Designation #2
            [2] => Designation #2
        )

    [4] => Array
        (
            [id] => 33
            [0] => 33
            [name] => Pritom #3
            [1] => Pritom #3
            [designation] => Designation #3
            [2] => Designation #3
        )

)

Array
(
)

Exception: All data will be erased during this transaction