PHP: SQL Prepared statement transaction not working correctly. It's inserting 1 SQL statement, not both.

Question

I'm finding that the PDO Transaction is only commiting 1 of my 2 SQL statement. For some reason, my PHP script is not inserting into my MySQL database 'homes' table BUT it does insert into the 'invoices' table - even though I'm using a PHP PDO database transaction.

Code below:

$conn_str = DB . ':host=' . DB_HOST . ';dbname=' . DB_NAME;
$dbh = new PDO($conn_str, DB_USERNAME, DB_PASSWORD);

/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();

$sql_create_home_listing = 'INSERT INTO homes ( customer_id,
    account_type_id,
    address,
    city,
    state,
    zip,
    display_status
) VALUES (?,?,?,?,?,?,true)';

$stmt = $dbh->prepare($sql_create_home_listing);
$stmt->bindParam(1, $customer_id);
$stmt->bindParam(2, $account_type_id);
$stmt->bindParam(3, $_SESSION['street']);
$stmt->bindParam(4, $_SESSION['city']);
$stmt->bindParam(5, $_SESSION['state']);
$stmt->bindParam(6, $_SESSION['zip']);
$stmt->execute();
$home_id = $dbh->lastInsertId();

// another SQL statement
$sql_create_invoice = "INSERT INTO invoices (customer_id, account_type_id, price, cc_authorized, home_id) VALUES (?,?,?,?,?)";
$cc_authorized = false;
$anotherStmt = $dbh->prepare($sql_create_invoice);
$anotherStmt->bindParam(1, $customer_id);
$anotherStmt->bindParam(2, $account_type_id);
$anotherStmt->bindParam(3, $account_plan_price);
$anotherStmt->bindParam(4, $cc_authorized);
$anotherStmt->bindParam(5, $home_id);

$anotherStmt->execute();

/* Commit the changes */
$dbh->commit();

How is it possible that only the 'invoices' table is getting the insert and not both the 'invoices' table AND the 'homes' table?

Note: no errors are reported by PHP.

Answer 1

Firstly, check whether you really have any errors in PHP - it's notoriously crap at telling you. There is an option you can set on PDO objects to throw an exception on database error - I recommend you set it.

It sounds to me like it is inserting the row, but you're in a transaction which is never committed so it gets rolled back, and the row is never visible (Your isolation mode is READ_COMMITTED or higher).

In that case you need to re-examine how your application uses transactions and try to see if you can get it properly consistent. Using transactions is nontrivial; it needs either a lot of code to get things right, or some well thought out wrapper code or something. If you don't understand any of that, leave autocommit on.

Answer 2

Check that your tables are transactional (InnoDB vs MyISAM as an example..).

Might want to do a try catch, so that if there is an error you can rollback. This may give you some insight.

Answer 3

Is true a valid value for display_status ?

MySQL does not have a bool type, nor does it have true as a predefined function or constant. So my guess is that it's a syntax error.

Get some decent error handling. Set the option which throws when there's a SQL error (See PDO docs!)

Answer 4

I found the problem. On my homes table I had a field deemed as 'unique' but it was not and preventing the insert from happening