PHP PDO What are the difference between exec() and commit()

Question

Can anyone explain what are the differences between PDO::commit() and PDO::exec() methods in PHP PDO library class.

For instance **assume PDO connection was established

$pdo = pdo_connection and params

$pdo->beginTransaction();

$sql = "INSERT INTO USERS VALUES ('u1','u2')";

$pdo->exec($sql);

$pdo->commit();

I want to know which stage data will be saved permanently in DB. and What are the differences between commit and exec

Answer 1

To understand what is going on, you need to know about database transactions.

From Wikipedia:

A simple transaction is usually issued to the database system in a language like SQL wrapped in a transaction, using a pattern similar to the following:

1. Begin the transaction
2. Execute a set of data manipulations and/or queries
3. If no errors occur then commit the transaction and end it
4. If errors occur then rollback the transaction and end it

In your example code, you start a new transaction with beginTransaction().

Then execute one query with exec(). In here you could execute a bunch more queries.

Note, at this stage nothing outside of your transaction can see any changes made by your queries inside the transaction. Only after committing the transaction will anything else be able to see those changes.

When inside a transaction, there is also the ability to roll back, which means abort any changes that have happened to the data since starting the transaction.

I want to know which stage data will be saved permanently in DB.

Hopefully, it's clear from the above that only after the commit() call will the changes be saved permanently.

Answer 2

commit commits the transaction which was begun with beginTransaction. exec only executes the query it has as a parameter so the actual change in db will take place after commit. For example if something fails between beginTransaction and commit, there will be no change in DB. You can have multiple exec() between begin and commit and they will be executed either all or none.