PHP Database Class

Question

Is it best to use a pre created class / api for database interaction (e.g Pear MDB2 or php's PDO) or create your own?

I will be only be using mysql with 1 database and perform relatively simple SELECT, INSERT, UPDATE, DELETE queries.

Answer 1

I've done both - using a pre-created class is probably best for maintainability's sake, as it's usually easy to switch in different database backends. However, I like the ease-of-use of my own database classes, so for my personal projects I usually write my own.

I wouldn't write my own for an outside job unless I was comfortable with supporting it - remember that if you write your own and the customer wants to change databases, you're going to be the one who gets the call to write an extension.

Answer 2

There are many quality APIs for common RDBMS and it is usually a good idea to use those unless you have specific requirements that those APIs do not implement. A good rule of thumb is to not reinvent the wheel, but rather extend it when necessary.

The Zend Framework has many RDBMS APIs available and is quite popular. The framework is designed so that you do not need to use the entire framework. You just need to use an include statement to include the necessary class(es) in your code.

Answer 3

For something as critical as database interaction I'd go with a pre-made solution like PDO or mysqli. Why go through all the trouble of rewriting a database access class when everything you need is already created? If you need functionality that isn't included you could always extend it.

Answer 4

For small personal projects, I prefer to use my own DB interface class over a pre-built, but other people swear by pre-built APIs. By creating your own class, you are almost guaranteed to be reinventing the wheel. Using a pre-built API typically only requires that you learn how to issue your commands within that API. If you are working with a team of programmers, I recommend using a well documented API and incorporating its use into your best practices and procedures. In my experience I have felt that team programming and home-brewed DB classes don't mix well.

One thing I will say, if you have never written a class like this, I recommend doing so as a learning opportunity just to get some behind the scenes comprehension. The experience I gleaned from writing my own class has definitely helped me more than a few times when debugging work done by other programmers who used an API that I was unfamiliar with.

[Edit - Fixed some really bad grammar]

Answer 5

Definitely either use PDO or mysqli directly for your situation.

The only reason I would ever use a DB-abstraction layer, is if you plan to write distributable software that must run on multiple RDBM's.

If you know your target (just mysql) and you're going to write an app you manage, skip the db abstraction layer altogether. When you do finally get into a situation where you need to switch, you will often have to rewrite a lot of queries anyway (unless you use perfect standard SQL, which is kinda supported everywhere..).

Refactoring is key, abstraction might just over-complicate things.

Answer 6

I'm not a big fan of frameworks but they do offer some benefits. At work, we use the Zend Framework (Zend_DB) for database connectivity. I see at least two benefits of using the framework:

1. Easy to Zend_Cache on top of Zend_DB.
2. Easy to use the Zend logging APIs to log SQL queries to FirePHP (includes prepared query and time for query to run).

Those two features can be quite useful.