In the following code, transaction fails and roll back is invoked.
However, in terms of the output:
If I remove the closeCursor(), payer`s balance is printed as -200$.
If I specify the closeCursor(), payer`s balance is printed as 0 which is the expected behavior.
This happens because result from the first select statement isn't being cleared before the second one is run.
Why do we need to close the connection for the roll back to be reflected in output? In both cases, roll back is invoked, so the value 0 is specified in DB. So I expect the balance to be printed as 0.
I dont see the relation between closing a connection DB, and getting latest DB values. Fetch() gets the value from DB, so if the value is 0, how come "it thinks" it`s -200?
This applies only to SQLite. MySQL will print 0 regardless if you use closeCursor().
<?php
try {
require_once '../../includes/pdo_connect.php';
// Set up prepared statements transfer from one account to another
$amount = 0;
$payee = 'John White';
$payer = 'Jane Black';
$debit = 'UPDATE savings SET balance = balance - :amount WHERE name = :payer';
####1st SQL#### $getBalance = 'SELECT balance FROM savings WHERE name = :payer';
$credit = 'UPDATE savings SET balance = balance + :amount WHERE name = :payee';
$pay = $db->prepare($debit);
$pay->bindParam(':amount', $amount);
$pay->bindParam(':payer', $payer);
$check = $db->prepare($getBalance);
$check->bindParam(':payer', $payer);
$receive = $db->prepare($credit);
$receive->bindParam(':amount', $amount);
$receive->bindParam(':payee', $payee);
// Transaction
$db->beginTransaction();
$pay->execute();
if (!$pay->rowCount()) {
$db->rollBack();
$error = "Transaction failed: could not update $payer's balance.";
} else {
// Check the remaining balance in the payer's account
$check->execute();
$bal = $check->fetchColumn();
########## $check->closeCursor();
// Roll back the transaction if the balance is negative
if ($bal < 0) {
$db->rollBack();
$error = "Transaction failed: insufficient funds in $payer's account.";
} else {
$receive->execute();
if (!$receive->rowCount()) {
$db->rollBack();
$error = "Transaction failed: could not update $payee's balance.";
} else {
$db->commit();
}
}
}
} catch (Exception $e) {
$error = $e->getMessage();
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>PDO Transaction</title>
<link href="../../styles/styles.css" rel="stylesheet" type="text/css">
</head>
<body>
<h1>PDO Transactions</h1>
<?php
if (isset($error)) {
echo "<p>$error</p>";
}
?>
<table>
<tr>
<th>Name</th>
<th>Balance</th>
</tr>
####2nd SQL####<?php foreach ($db->query('SELECT name, balance FROM savings') as $row) { ?>
<tr>
<td><?php echo $row['name']; ?></td>
<td>$<?php echo number_format($row['balance'], 2); ?></td>
</tr>
<?php } ?>
</table>
</body>
</html>
