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

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 */

$sql_create_home_listing = 'INSERT INTO homes ( customer_id,
) 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']);
$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);


/* Commit the changes */

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.

13.10.2009 19:47:09
Have you run the sql for the homes insert directly on mysql?
rojoca 13.10.2009 20:37:52

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.

13.10.2009 19:54:37
How do I set the option for PDI to throw exceptions?
user189386 13.10.2009 19:56:04
After my 'execute' command, I run 'die($dbh->errorCode());'. This outputs '00000'.
user189386 13.10.2009 20:01:42
@MarkR, I'm using a transaction now - see updated post above. Very strange, it's still only inserting into the 'invoices' table and not the 'homes' table.
user189386 13.10.2009 20:17:00
@TomH, run $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) after you create the database connection to throw exceptions.
mcrumley 13.10.2009 20:42:39

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.

13.10.2009 20:21:09

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!)

13.10.2009 20:40:19

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

13.10.2009 20:51:49