Standard connection libraries for MySQL, MSSQL, and Oracle in PHP

Question

Hello,

I'm looking for a standard way to connect to databases in PHP. We've all been there - first start with some rudimentary code to connect/query/iterate/insert/disconnect, then the code grew as the program grew, and it ended up with a mess that's hardly reusable.

I know there are many PEAR, PECL, and other PHP libraries/classes out there that can fit my description - but which ones are maintained, used, and have proven to be bug-free and efficient?

Answer 1

These two are the best in my opinion. I can't claim to have tried them all though :)

• MDB2 (PEAR DB's successor)
• AdoDB

If on Linux you'll need FreeTDS to connect to MSSQL, regardless of the library you end up choosing.

Answer 2

if you're using PHP 5 try out PDO

Answer 3

try Object Relational Mapping libraries such as Propel and Doctrine, both uses PDO as database abstraction layer so they pretty much work on all engine.

Answer 4

When looking at new DB connection/query objects while trying to decide who to about writing or using new libraries, we decided it was best to write our own. In the end it probably doesn't have the flexibility that many other libraries have, but we have added features such as GetAll() which retrieves all of the rows in a keyed array or GetAllKeyed() which returns a keyed array with the ID as the key. Another great one is GetOne() for use when your select only has 1 column. These have all reduced the amount of code greatly.

One other feature is that when you do a query, it determines what type of query it is (INSERT, UPDATE, SELECT, DELETE, etc) and then returns appropriate information (such as INSERT, the last insert id or DELETE the number of rows deleted).

But we have also copied features such as Prepare & Execute from PEAR:DB.

Answer 5

I'm supprised *Zend_Db* hasn't been mentioned yet...

• PEAR's MDB2: very stable, also provides a layer that implements all MDB2-supported features in all databases where it can at least be simulated. I've used this one for years with much success.
• Zend Framework's Zend_Db: I've just started using the higher levels of Zend's entire DB infrastructure, but it seems to be quite stable and extremely well thought out.
• PHP5's native PDO: I've not used it at all, but I believe it is the simplest of all of these. In fact, both MDB2 and *Zend_Db* can use PDO as an underlying layer.

All of the above implement prepare and execute. Of the above, MDB2 is the most mature, as it's been around for a long time and is based on DB and MDB. *Zend_Db* appears to be the most well thought out. I know there are others, but I don't have experience or any knowledge about any of them.