Why is my PHP transaction not working?

Question

I am working on a school project creating a CMS for my portfolio. I am having problems with my update function. I have a feeling that this is due to the way I build my PDO Transaction. My database has a project table, a category table, and an associative table content_category. I can just insert my projects into these tables. What I want to do is insert into the table of my projects, then delete all entries from the content_category table and finally insert the entries of the current category into this associative table to complete the transaction. I get the return statement "Project Update". But the tables are not updated. Any ideas?

Here is the code:

This is a function in my Project class.

public function update(){ try { $conn = getConnection(); $conn->beginTransaction(); $sql = "UPDATE project SET project_title = :title, project_description = :desc, project_isFeatured = :feat, project_mainImage = :image WHERE project_id = :id"; $st = $conn->prepare($sql); $st->bindValue(":id", $this->id, PDO::PARAM_INT); $st->bindValue(":title", $this->title, PDO::PARAM_STR); $st->bindValue(":desc", $this->description, PDO::PARAM_STR); $st->bindValue(":feat", $this->isFeatured, PDO::PARAM_BOOL); $st->bindValue(":image", $this->mainImage, PDO::PARAM_INT); $st->execute(); $sql = "DELETE from content_category WHERE content_id = :id"; $st = $conn->prepare($sql); $st->bindValue("id", $this->id, PDO::PARAM_INT); $st->execute(); $sql = "INSERT into content_category (content_id, cat_id) VALUES (?,?)"; $st = $conn->prepare($sql); foreach($this->categories as $key=>$value){ $st->execute(array(intval($projectID), intval($value))); } $conn->commit(); $conn = null; return "Project updated"; } catch(Exception $e) { echo $e->getMessage(); $conn->rollBack(); return "Error... Unable to update!"; } } 

Answer 1

To ensure that you do not encounter a PDO error, you should configure the PDO error report as follows:

 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

PDO has functions such as prepare() that either return false or throw a PDOException depending on which error mode is set. Thus, this will throw an exception, and you will definitely find out if you have problems!

In addition, if your database does not support transactions (e.g. MyISAM), the beginTransaction() function will return false. So, maybe add a check, for example:

 if($conn->beginTransaction()) { // Do transaction here } else { echo("Unable to use transactions with this database."); } 

Oddly enough, according to the PHP documentation , you will get an exception if your database does not support transactions ...

Unfortunately, not every database supports transactions, so PDO needs to be run in the so-called auto-commit mode when the connection is first opened. Auto-commit means that each query that you run has its own implicit transaction if the database supports it, or there is no transaction if the database does not support transactions. If you need a transaction, you must use the PDO :: beginTransaction () method to initiate it. If the underlying driver does not support transactions, a PDOException is thrown (regardless of error handling settings: this is always a serious error condition) .

Answer 2

Your database engine for tables should be INNODB. If you use phpMyAdmin, MyISAM is used by default. (I don’t know if this will result in updates failing or if the transaction line simply will not be ignored. Editing: I am pretty sure that the documentation says that it will throw an error and will not do anything if you start the Transaction on myISAM)

Answer 3

Commit returns TRUE on success or FALSE on failure. You can check it out. Also check errorCode .