Multiple mysqli prepared statements with transactions

Question

I'm trying to figure out how to use sql transactions with mysqli prepared statements. I haven't been able to find any examples that use multiple prepared statements (that aren't OO), so I'm not really sure how to use transactions with them. This is the closest I could figure:

    mysqli_autocommit($database, FALSE);

    $transferq = 'INSERT INTO money (user_id, bank, onhand, type, amount, source) VALUES (?, ?, ?, ?, ?, ?)';
    $transferstmt = mysqli_stmt_init($database);
    mysqli_stmt_prepare($transferstmt, $transferq);
    mysqli_stmt_bind_param($transferstmt, 'iiisis', $userid, $newbank, $newmoney, $type, $amount, $source);
    mysqli_stmt_execute($transferstmt);

    $insertq = 'UPDATE users SET money=?, bank=? WHERE user_id=' . $userid . ' LIMIT 1';
    $insertstmt = mysqli_stmt_init($database);
    mysqli_stmt_prepare($insertstmt, $insertq);
    mysqli_stmt_bind_param($insertstmt, 'ii', $newmoney, $newbank);
    mysqli_stmt_execute($insertstmt);

    mysqli_commit($database);

But, I have no idea if that would even work. My biggest issue, though, is I'm not sure how to check if the queries failed or not (and therefore whether or not to commit). I saw an example that I think did something like

if(mysqli_stmt_execute($stmt)){
    mysqli_commit($database);
}else{
    mysqli_rollback($database);
}

But I can't really do that since I have multiple prepared statements to execute.

How is this supposed to work?

score 1 · Accepted Answer · answered Nov 07 '14 at 19:04

Maybe I did not understand your question, but what about this?

mysqli_autocommit($database, FALSE);

$transferq = 'INSERT INTO money (user_id, bank, onhand, type, amount, source) VALUES (?, ?, ?, ?, ?, ?)';
$transferstmt = mysqli_stmt_init($database);
mysqli_stmt_prepare($transferstmt, $transferq);
mysqli_stmt_bind_param($transferstmt, 'iiisis', $userid, $newbank, $newmoney, $type, $amount, $source);
if (not mysqli_stmt_execute($transferstmt) ){
    mysqli_rollback($database);
    return;
}

$insertq = 'UPDATE users SET money=?, bank=? WHERE user_id=' . $userid . ' LIMIT 1';
$insertstmt = mysqli_stmt_init($database);
mysqli_stmt_prepare($insertstmt, $insertq);
mysqli_stmt_bind_param($insertstmt, 'ii', $newmoney, $newbank);

if (not mysqli_stmt_execute($insertstmt) ){
    mysqli_rollback($database);
    return;
}

mysqli_commit($database);

The next level if form of this object-oriented using of mysqli or PDO (without transactions, as example of way of work with database):

class my_database{
    private static $inner_link_to_driver;
    protected static function factory( ){
        if (not static::$inner_link_to_driver){
            static::$inner_link_to_driver = new ...(USER, SERVER, PASSWD, PORT);
        }
        return static::$inner_link_to_driver;
    }
    public static function do_something($params, &$message ){
        $query = "...";
        $stmt = static::factory()->prepare($query);
        if (not $stmp ){
            $message = 'Error prepare query '.$query.PHP_EOL.static::factory()-> ..(get_error);
            return FALSE;
        }

        if (not $stmt->execute($params) ){
            $message = 'Error execute query '.$query.PHP_EOL.static::factory()-> ..(get_error);
            return FALSE;
        }
        return TRUE;
    }
}

Multiple mysqli prepared statements with transactions

1 Answers1