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.
$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 '
Note: no errors are reported by PHP.
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.
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.
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!)
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