MySQL Transactions

r_honey

I could not find any functions in PHP that provide transaction support for mySQL. So, are there any such functions available??

Moreover, are there any classes available that provide OO interface to mysql interaction via PHP???

etully

When you sign up for PHPBuilder.com, there are clear instructions that you are to search google first. If you can't find anything, then use the search function at PHPBuilder.com. If you still can't find anything, then post.

Not only does Google have 412 Billion pages with examples of how to use PHP with MySQL, and not only does PHPBuilder have an entire forum just for MySQL, but a simple search on PHPBuilder would yield close to 823 Million threads where people have asked questions about how to use PHP with MySQL.

To answer your question: Yeah, PHP and MySQL are like two peas in a pod.

r_honey

And yes, I did some frantic searching on php.net & mysql.com (could not think of better places than that) before posting in the hope of getting some answers.

And I came to phpbuilder.com, coz I am following a book written by 5 people who are moderators here. And even that book does not say anything about MySQL transactions in PHP.

alimadzi

If you're using PHP 5, I would recommend PDO:

http://us.php.net/pdo

Scroll down to the section about "Transactions and auto-commit". Hope this helps.

MarkR

Yes, you can use transactions with MySQL, but you have to use a table type which supports them. The default (MyISAM) does not - see the MySQL docs for more information.

If you use a non-transactional table type, rollbacks will have no effect and other processes may see changes before they are committed.

Mark

r_honey

After some frantic searching, I found about the following PHP libraries & PEAR packages:
mysqli, PDO, ADODB, PEAR DB & PEAR MDB2.

Well, I am not necessarily looking at DB portability. I am looking at a library that provides some efficiency, makes more programming sense & structure than that provided by raw PHP mysql functions & is easy to maintain.

Going by that criteria, I have zeroed on to ADODB & PDO. So, can anyone tell me which of these (from all the 5 listed above) will be more suitable for my criteria?

Also, does any of them has specific mysql Engine requirements???

laserlight

I believe you can always rely on something like BEGIN TRANSACTION in the absence of transaction support in the PHP extension or library. PDO has support for beginning transactions from PHP code and is object based. It provides some amount of portability (but not much, since the individual SQL statements may differ from DBMS to DBMS).

r_honey

Going by the advices above & my own searching, I am narrowing down to just PDO. I again stress that I am not lookking for DB portability. I am looking for some efficiency, & more importantly, a well-structured class hierarchy so that it can be used & maintained easily in PHP code...

Any suggestions???

alimadzi

Sounds like you've found it. Go ahead and use PDO. Any further questions?

r_honey

Thanx!!! Some people are still suggesting me to go for ADODB, but I believe I will settle for PDO as of now...

One final thing (I hope its final 🙂 ) Is there any specific Engine requirement for PDO. As stated in one of the posts above, transaction support is not provided by the default engine, MyISAM. So, the options left are MERGE, MEMORY, InnoDB & BDB (I dont know if other engines also ecist).

My Hosting provider has withdrawn support for BDB. And my analysis shows that InnoDB is the best of the rest. Am I correct???

Moreover, how can I know the engine my code is running on in PHP? Can I select the engine using PHP also???

alimadzi

PDO is bundled with PHP 5.1+, so if you're running a recent version then you're good to go. As far as determining the table type, you can specify it when creating your tables or make the change after the fact. And yes, I would recommend InnoDB... even if Oracle bought it:

http://www.oracle.com/innodb/

Best of luck!

r_honey

Thanx...
I hope this discussion would help prevent me maintainence nightmares later...

alimadzi

r_honey wrote:
Thanx...
I hope this discussion would help prevent me maintainence nightmares later...

What do you mean? Do you have other questions or concerns?

r_honey

I just said Thanx!!!