pgsql vs. mysqli vs. PDO vs. Zend_DB in PHP [StartupCTO]

Last Updated: 13 Oct 2023

|

Author: 111.225.148.67

Page locked

This page is currently locked for editing by another user. You have to wait until this user finishes editing or the lock expires.

Currently locked by: 3.144.161.116
Lock expires at: May 8, 2024 06:47 PM (188 min)

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

= pgsql vs. mysqli vs. PDO vs. Zend_DB in PHP =

If your PHP app needs to talk to a database, there's a lot of debate as to whether you should use PHP's native database extensions (e.g. [[http://us.php.net/manual/en/book.mysqli.php|mysqli]] and [[http://us.php.net/manual/en/book.pgsql.php|pgsql]]) or PHP's new [[http://us2.php.net/pdo|PHP Data Objects]]. mysqli and pgsql talk directly to their respective databases, whereas PDO is a database abstraction layer; it provides a common interface to talk to several different databases. 

Many developers say that you should use PDO, but I disagree. The two primary reasons for using PDO are:
 * You can connect to any major database with the same interface, which makes your application portable across databases. Simply change the connection string, and voila, you're talking to SQL Server instead of MySQL!
 * Because you're only using one interface, you don't have to remember different PHP commands for dealing with different databases. No need to worry that [[http://www.php.net/mysqli_connect|mysqli_connect()]] takes an entirely different set of arguments than [[http://www.php.net/function.pg-connect|pg_connect()]]. 

Those are both very valid points, and on the surface they sound great. But consider this:
 * If you're building anything but the most trivial application, you're going to need to use the fancy features of your database (stored procedures, sequences, check constraints, etc.), and every database supports a different set of fancy features. The native functions built into PHP have much better support for the fancy features, whereas PDO (and other database abstraction layers) generally don't, as it would violate their principle of being database neutral.
 * While being able to switch databases sounds great in theory, how often have you //actually// done it? While there may be business reasons for switching from Oracle to MySQL (e.g. saving $$$), the databases that PHP apps most commonly use are all free or open source. It's almost always easier to install a copy of whatever database you need than go change your code (and then retest everything!) to get it to work with a database from a different vendor.
 * Same thing goes for you, as a developer: how many databases do you actually use on a day-to-day basis? In the entire time I've been working with PHP, I've only used two in any serious production capacity: MySQL and PostgreSQL. PDO might be more attractive if I were consantly switching between four or five different databases, but its not that hard to remember just the ''mysqli_*'' and ''pg_*'' commands.

I don't hate PDO, and have used it in the past for simple apps, but I generally think you're better off using a native extension.

=== What about Zend_DB? ===

As of this writing (May 2009), Zend has a new database interface module out, called [[http://framework.zend.com/manual/en/zend.db.html|Zend_DB]]. It's sort of like a database abstraction layer combined with a set of database helper functions. It has methods like ''insert()'' and ''update()'' which let you automatically insert/update data without having to write a lot of SQL, as well as all the standard methods (e.g. ''query()'') that any good abstraction layer provides. It actually //uses// the PHP database engines under the hood (mysqli, pgsql, PDO, etc.), and even gives you direct access to them via the ''getConnection()'' method. 

I've been using Zend_DB recently, mainly with the mysqli engine, and so far I like it. It provides ready-made versions of a lot of the helper functions that I usually write myself, like ''quote()'' and ''quoteIdentifier()''. And since it provides direct access to the underlying connection, it doesn't limit me from using advanced functionality if I need it. Still, Zend_DB does introduce one more layer your data has to go through, so it may be overkill for some projects. But it's certainly worth a look.

Page locked

About Me

Recent Changes

Get In Touch

Follow