Is PDO the new standard?

[Jake Arkinstall]

Well, if you're used to an object returning database results, you'd want it FETCH_OBJECT.

If you're used to using mysql_fetch_array, you'd want FETCH_ASSOC or FETCH_ARRAY, depending on your goal.

For those used to grabbing everything from the db, they want FETCH_LAZY to make things faster.

There are more. Alot more.

But each are suited to a different situation.

A car company is going to be useless if they only sell cars suited for people under 4ft high.

[kyberfabrikken]

I have to agree with McGruff on the FETCH constants. It feels needlessly complicated, and there is nothing that prevents users to implement specific solutions atop the API. Apart from that, using constants as arguments is just bad style.

[ghurtado]

I am no big fan of long lists of constants either. But I think dismissing the entire PDO library because of that design flaw alone is a big stretch. Specially considering the alternative which we come from; the hodge-podge of DB specific functions.

Do you also recommend against using PDO entirely and going back to the stone age of database access functions we had before?

How would you better handle the 70+ different configuration options? So long as we don't have a better idea, it is not much use to complain, is it?

[Mastodont]

using constants as arguments is just bad style.

So - for example - filter_var with constants as options is a bad design? Preg_split? Array_multisort? Do you prefer to remember integer values for arguments? I don't.

[kyberfabrikken]

Do you prefer to remember integer values for arguments? I don't.

No, I prefer separate methods, if the semantics differ. I like $pdo->getFoo() over $pdo->get(PDO::FOO)

[ahundiak]

No, I prefer separate methods, if the semantics differ. I like $pdo->getFoo() over $pdo->get(PDO::FOO)

But what happens when you encounter:
$pdo->get(PDO::FOO | PDO::BAR);
Now you need:
$pdo->getFoo();
$pdo->getBar();
$pdo->getFooBar();

I just wrap the pdo objects in my own access object and hide all the constants in it.

[McGruff]

The constants could be tamed with, in order of preference:

(1) First, and most important, identify all the useless options and dump them. The problem immediately becomes much, much simpler. If anyone desperately needs some obscure configuration option they can extend the standard, off-the-shelf class.

(2) Create new types. Eg PDO::ATTR_PERSISTENT might suggest a new PersistentConnection class. Bang goes another constant.

(3) Do the config at instantiation ie set up once wherever you create the object graph and keep the actual app as simple as possible.

(4) As mentioned above, add new methods.

[Jake Arkinstall]

The simplest way of going round this is to set a default fetch type when instantiating the PDO object. Sure, there's a long list of constants to choose from - but when you're only going to type it once in your application, complaining about it is... it's just petty.

[ghurtado]

The constants could be tamed with, in order of preference:

I like it. The only thing I would add is that there are so many constants (I think over 70!) that some of these groups of constants might fit into one pattern and some may fit into another.

For an instance, the prepared statement bound parameter constants might be best dealt with by new methods (bindInt, bindString, etc...)

Some, like you mentioned, need to be thrown away entirely; such as the ability to choose the method of error reporting. IMHO it should just be Exceptions and exceptions only, if you really want your errors to show up as traditional PHP errors it is simple enough to wrap your methods in a catch() block.

[World Wide Weird]

There are four query methods because there are four different types of result: simple boolean with eg a create database query, a hash when you know a select query will only return one row, an iterator when you know it might not, and a (possibly badly-named) method to manipulate tables eg inserts & updates which will return the number of affected rows.

    function query($sql);  // returns bool
    function getRow($sql); // returns array
    function getRows($sql);  // returns iterator
    function manipulateRows($sql);  // returns int 

Another option would be to have a single query() method, parse the sql to discover the query type, and then return the appropriate type. At the time (it was a long time ago) I decided that was too much hard work and instead opted for different methods for different types of sql query. A single query method is certainly do-able and would present a simpler interface but it would return different things in response to different queries....

There's a simple way to do this. Years ago before PDO was introduced, I tried an ADO script in PHP (might have been phpADO) and found its configuration options unnecessarily complicated. So I wrote a very simple script that was similar to it but only had the features I needed.

What I did was have the query() method return a simple Recordset object regardless of the query type. In addition to a hash containing a rowset for SELECT queries, the object had lastInsertId, affectedRows, and other properties which I've forgotten. Depending on the query type, each property would be either null or an appropriate value for that query.

[PCSpectra]

I was refered to this thread from Devnetwork after I posted some code I hammered out yesterday.

Basically I have experienced the same problems as mentioned in here with PDO...I personally found the interface a mess and quite confusing and overly complex, so in attempt to simplify it I wrote this wrapper library.

http://code.google.com/p/pdowrapper/

One of the most challeneging aspects has been on how to handle the setAttribute() problem.

Initially I tried to figure out if it was possible to organize each setting with an associated object and thus call a explict member function as opposed to passing a constant which might not even apply to the selected driver, etc...

This has proven difficult as I am not overly familiar with many other RDBMS.

I wanted to somehow associate a setBufferedQueries method to only the MySQL connection object() but without introducing dependencies to the conneciton object (mainly the PDOStatement or connection object) it's difficult to set the attribute.

I would really appreciate some feedback in this regard.

Cheers,
Alex

[SpacePhoenix]

imho it is better to use the mysql_* over PDO as:

• Speed: PDO is slower then mysql_*. It might not make much difference for small to medium sized apps but once an app gets big enough the difference in speed will be noticed.
• Security: PDO may have an advantage over mysql_* with the use of prepared statements but I feel that any values for use in a query should be escaped and validated before being added to a query.
• Standardization: Each database has it's own variations of SQL, until all databases use exactly the same SQL command set, in exactly the same way.
• Baggage: To handle the various databases PDO must have the code for communicationg with them but if you know that you will only ever use MySQL then a lot of the code in "baggage" as far as your app is concerend as it would only need the code for accessing MySQL.

[PCSpectra]

Speed: PDO is slower then mysql_*. It might not make much difference for small to medium sized apps but once an app gets big enough the difference in speed will be noticed.

PDO is a binary extension...so while there is a slight over head...honestly the performance loss is minimal, considering you get data access abstraction.

PDO may have an advantage over mysql_* with the use of prepared statements but I feel that any values for use in a query should be escaped and validated before being added to a query.

What, you mean manually, so a programmer can forget and have a SQLi injection occur because of human error? :P

PDO handles escaping for you...

Each database has it's own variations of SQL, until all databases use exactly the same SQL command set, in exactly the same way

That is why PDO is said to be a data access layer not a database abstraction layer...besides it could be used in conjunciton with an OQL which would then offer a complete and robust database abstraction layer. You would be able to literally and seamlessly switch RDBMS with the flip of a switch. That is the eventual goal of Rapid Database.

To handle the various databases PDO must have the code for communicationg with them but if you know that you will only ever use MySQL then a lot of the code in "baggage" as far as your app is concerend as it would only need the code for accessing MySQL

If you know you are always going to use MySQL sure...but most developers (for one reason or another) like the option of being able to make the switch. I always use MySQL (I have never used another database in fact -- other than Access or SQLite) but I still prefer PDO "just in case".

Cheers,
Alex

[kyberfabrikken]

I feel that I have to correct you on these points, SpacePhoenix.

PDO may have an advantage over mysql_* with the use of prepared statements but I feel that any values for use in a query should be escaped and validated before being added to a query.

Firstly, it's a good idea to put the responsibility of escaping strings as close to the consumer of it as possible. Magic quotes is an example of what goes wrong, when you don't. Or you get sql-injection vulnerabilities.
Secondly, bound parameters are actually safer than embedding values by escaping them. That is because with bound parameters, the query and the data are transmitted separately, so they are never mixed, and thus no injection can ever get through.

PDO is slower then mysql_*. It might not make much difference for small to medium sized apps but once an app gets big enough the difference in speed will be noticed.

Is it? I have used PDO with some pretty big applications, and I didn't notice any bottle neck there. What do you base your assumptions on?

Each database has it's own variations of SQL, until all databases use exactly the same SQL command set, in exactly the same way.

PDO is not a database abstraction layer. It's a database connectivity layer.

To handle the various databases PDO must have the code for communicationg with them but if you know that you will only ever use MySQL then a lot of the code in "baggage" as far as your app is concerend as it would only need the code for accessing MySQL.

PDO is split up into multiple extensions; The main extension, and one for each database driver. If you only use MySql, then just install pdo + pdo_mysql.

Now, all that aside, it can be considered bad style to post the exact same message in multiple threads. If you feel that the same answer applies to two separate topics, you should post in one of them and then post a reference back in the other thread.

[Ren]

The design of PDO doesn't bother me as much as the stuff it cannot do.

Simple things like binding a short binary string to a parameter is impossible. Even though its in the standard SQL and RDBMs can do it.

INSERT INTO hashes(hash) VALUES(X'0123456789abcdef');

So had to resort to own parameter handling with the specific database APIs.

[SpacePhoenix]

I just had a look at the code for Joomla and wordpress and neither use PDO, both access mysql direct with both of them being used by many sites I don't think it can really be considered to be a new standard. I can't look at what invison powerboards or vBulletin use, anyone know?

[allspiritseve]

I just had a look at the code for Joomla and wordpress and neither use PDO, both access mysql direct with both of them being used by many sites I don't think it can really be considered to be a new standard. I can't look at what invison powerboards or vBulletin use, anyone know?

I'm not suprised that they would be slow to move to PDO, considering they're all CMSs or Forums that are aimed towards users, not developers. A better indicator would be what the popular PHP frameworks support.

[imaginethis]

I just had a look at the code for Joomla and wordpress and neither use PDO, both access mysql direct with both of them being used by many sites I don't think it can really be considered to be a new standard. I can't look at what invison powerboards or vBulletin use, anyone know?

Joomla, wordpress, and vbulletin were running on PHP 4.x servers well before there was a PHP 5 came along with the goodness of PDO. There are millions of websites out there that use antiquated code - maybe antiquated is too harsh of a word - so if quantity defines standard, then using the mysql functions are the standard. Pick up any book on learning PHP or run a search for PHP & MySQL tutorials, and hands down you will find more using the mysql functions over PDO so I think it's fair to say that the old way is still very much a standard even with the rising popularity of PDO.

[risoknop]

I'm not suprised that they would be slow to move to PDO, considering they're all CMSs or Forums that are aimed towards users, not developers. A better indicator would be what the popular PHP frameworks support.

Symfony is using Creole (though latest version already uses Doctrine I think). Anybody knows what Zend uses?

[imaginethis]

Symfony is using Creole (though latest version already uses Doctrine I think). Anybody knows what Zend uses?

Zend_Db using PDO and MySQLi for mysql databases.

[logic_earth]

Symfony is using Creole (though latest version already uses Doctrine I think). Anybody knows what Zend uses?

Symfony uses Propel which uses PDO now. Creole is dead.
http://creole.phpdb.org/trac/wiki/News/CreoleIsDead

[risoknop]

Symfony uses Propel which uses PDO now. Creole is dead.
http://creole.phpdb.org/trac/wiki/News/CreoleIsDead

Oh yes that's true, sorry for confusion.

But it seems newest Symfony also also offers Doctrine as alternative to Propel - http://www.symfony-project.org/book/doctrine/1_2/en/