SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 47

Hybrid View

  1. #1
    SitePoint Addict AfroNinja's Avatar
    Join Date
    Oct 2006
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is PDO the new standard?

    My original site was built in 2003 using a set of custom mysql_ type functions. I've been reading about this PDO object, and seems like a much better approach- abstraction, transactions, and prepared queries. Is this the new standard for php database access?

    Are there other options I should be aware of?

    Any noticeable overhead to using this method?
    The Flash Gaming Network
    Editorial reviews for the latest flash games!
    Afro Ninja Productions
    Original flash games and content from a guy with an afro

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I wouldn't call it a standard. Most sites are still done procedural style with the mysql functions, or using the mysqli object directly. Those that use frameworks are just as likely to be using ORMs like Propel or Doctrine and there are even more general abstraction packages competing with PDO like Creole.

  3. #3
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PDO is a fine choice, it's not any slower (as far as I know) than native db functions, and provides a uniform database interface. While it's not a true database abstraction layer (it's actually a database access abstraction layer), because queries must still be written in native SQL, it has some advantages over using db-functions. It's easy to use, object-oriented, can be set to use exceptions (which is imo better than fatals), and promotes security (through prepared statements). It's also got a decent adoption rate, so I guess it's safe to say that it's becoming a standard, even though the older codebase is still written with mysql_*, but that's expected -- you're not gonna rewrite an old project just to replace these database calls.

    best regards

  4. #4
    SitePoint Evangelist
    Join Date
    Aug 2005
    Location
    Winnipeg
    Posts
    498
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wouldn't say PDO is an abstraction as it doesn't solve the problem of switching DB's like AdoDB does. It simplifies your database API and adds functionality like prepared statements, etc.
    The only constant in software is change itself

  5. #5
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PCSpectra View Post
    I wouldn't say PDO is an abstraction as it doesn't solve the problem of switching DB's like AdoDB does. It simplifies your database API and adds functionality like prepared statements, etc.
    Well, it is database access abstraction layer, not database abstraction layer, that's what I said. The differerence is that the former only abstracts the database interface in the PHP code, while the latter also abstracts (or should abstract) the actual communication.

    That being said, ADOdb isn't much more abstract than PDO. You still execute raw queries, which are database-specific. You have SelectLimit() which helps you limit your result sets independantly of database. And you can create tables from an abstract representation. And there are some helpers for dates. But that's about it, at least it was when I used ADOdb.

    Doctrine (or Propel) for example trully abstract the database. You can switch the database transparently, you don't have to worry about queries (most of the time at least). But these are ORM frameworks and work on a different layer, so I don't think they count in this discussion. Also, they both use PDO (Propel from version 1.3).

    regards

  6. #6
    simple tester McGruff's Avatar
    Join Date
    Sep 2003
    Location
    Glasgow
    Posts
    1,690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Personally I think PDO is a horrible mess and I wouldn't recommend it to anybody. A connection object could (and should) be much simpler.

  7. #7
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by McGruff View Post
    Personally I think PDO is a horrible mess and I wouldn't recommend it to anybody. A connection object could (and should) be much simpler.
    Those are class constants... It's not like they're in the global namespace like other extensions. What about that is a horrible mess?

    So what if it's not perfect - what other solution would you recommend that's built-in PHP? Going back to the database specific functions? I don't think so...

  8. #8
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, technically everything is an abstraction! No wait, or is that a distraction? The word is redundant, my friends! But sometimes redundancy is good.

    In any case, I think the man meant to say "data access layer" but what with his mind up so high in the clouds pondering the cosmos and such, he could not but help to take the action of inserting the abstraction distraction without a retraction or rolling back on that transaction!

  9. #9
    SitePoint Addict AfroNinja's Avatar
    Join Date
    Oct 2006
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    summary: it's ok to use PDO

    The Flash Gaming Network
    Editorial reviews for the latest flash games!
    Afro Ninja Productions
    Original flash games and content from a guy with an afro

  10. #10
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I actually would say that PDO is the new standard. Dan mentioned some ORM packages like Doctrine and Propel, but both of those packages use PDO under the hood. There isn't any additional overhead related with using PDO because it's a bundled library with PHP5 written in C. In fact, it actually has performance gains over the traditional mysql_* functions because it uses native database drivers and is slated to be even faster in PHP 5.3 and 6 with some further improvements.

    So yes - It's much more than "OK" to use it. It's what you SHOULD use. Period.

  11. #11
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not hide PDO behind a facade? Or is there a specific reason you'd prefer to use the db specific functions, apart from the ugly interface?

  12. #12
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    A connection object could (and should) be much simpler.
    Well, how exactly would you have done it?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  13. #13
    simple tester McGruff's Avatar
    Join Date
    Sep 2003
    Location
    Glasgow
    Posts
    1,690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    interface Connection 
        function 
    query($sql);  // returns bool
        
    function getRow($sql); // returns array
        
    function getRows($sql);  // returns iterator
        
    function manipulateRows($sql);  // returns int
        
    function lastInsertId(); 
        function 
    getInfo(); 
        function 
    getError(); 
        function 
    ping(); 
        function 
    escape($string); 
        function 
    close(); 


  14. #14
    SitePoint Evangelist ghurtado's Avatar
    Join Date
    Sep 2003
    Location
    Wixom, Michigan
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What???

    First you warn that PDO is a "horrible mess" that nobody should use. Fair enough, but you are expected to support the arguments of your position.

    As far as I can tell, this is just taking PDO methods and renaming them, how is this is an improvement to PDO?

    Quote Originally Posted by McGruff View Post
    PHP Code:
    interface Connection 
        function 
    query($sql);  // that's just PDO::query
        
    function getRow($sql); // that's just PDOStatement::fetch
        
    function getRows($sql);  // that's just PDOStatement::fetchAll
        
    function manipulateRows($sql); 
    /* I have no idea what a method called "manipulateRows" would do.
     Is this like a SQL update? why do you need to provide the SQL then? */
        
    function lastInsertId(); // PDO::lastInsertId
        
    function getInfo(); // very ambigous name for a method, could do anything, really
        
    function getError(); // PDO::errorInfo 
        
    function ping(); // what is this?
        
    function escape($string); // that's just PDOStatement::quote
        
    function close();
     
    /* PDO doesn't eed a close() method since it closes the
     connection in the destructor. So your interface is more bloated 
     by at least one method */

    You still haven't addressed how you would handle configuration parameters better.
    Last edited by ghurtado; Oct 3, 2008 at 23:01. Reason: toning it down
    Garcia

  15. #15
    simple tester McGruff's Avatar
    Join Date
    Sep 2003
    Location
    Glasgow
    Posts
    1,690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ghurtado View Post
    how is this is an improvement to PDO?
    First, I don't claim this is the last word on a database connection but I was asked what I use - this is it.

    The getInfo() method returns info about the most recently executed query (inserts, updates, et al). Actually, in order to keep things as simple as possible, that's the kind of thing I'd argue *not* to add to a connection class unless you really need it. I did, so in it went.

    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.

    PHP Code:
        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. Would that be confusing? I'm not sure which is definitively better.

    What I want from a database connection is essentially just a post box with a big hole I can stuff sql into. Nice and simple. One day I woke up to find PDO_Postbox being installed all around town, each with a manual the size of a small novel. I find myself, like Oliver-Twist, standing up and saying: "please sir can I have a little less?"

    Configuration parameters: I'd just ignore them, unless I really needed something. For example, how many types of FETCH *do* we really need..?

  16. #16
    We're from teh basements.
    Join Date
    Apr 2007
    Posts
    1,205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by McGruff View Post
    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.

    PHP Code:
        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.

  17. #17
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by McGruff View Post
    PHP Code:
    interface Connection 
        ...
        function 
    escape($string); 
        ... 

    String escaping should be responsibility of connection? That beats me. (As well as row manipulation ...)

    PHP Code:
     interface Connection {
         function 
    open($args);
         function 
    lastInsertId();
         function 
    getState();
         function 
    getError();
         function 
    close();


  18. #18
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mastodont View Post
    String escaping should be responsibility of connection? That beats me.
    Escaping method depends on the rdbms and even on the current connection (charset), so that makes perfect sense. I would take bound parameters over manual escaping any time, though.

  19. #19
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Connection could be argument of escape method (as in mysql_real_escape_string), yes. Bound parameters are very well, too.

    But "manual" escaping should be IMHO done automatically, somewhere deep in DAO. Pseudocode:
    PHP Code:
    if isStringField(...) escapeField(...); 

  20. #20
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Exactly.

    PDO is a much better layout than that. Come on, would you use that? I wouldn't.

    Try again or consider your point invalid. Basically my point is that you are complaining about PDO, yet couldn't improve it even if you did have the chance.

    We can all complain about things but unless you are willing to do something about it, there's no point.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  21. #21
    simple tester McGruff's Avatar
    Join Date
    Sep 2003
    Location
    Glasgow
    Posts
    1,690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You know what maybe you should (both) tone it down a little bit. I wouldn't mind discussing this further but not if you're going to behave like that.

  22. #22
    SitePoint Evangelist ghurtado's Avatar
    Join Date
    Sep 2003
    Location
    Wixom, Michigan
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My honest apologies if my post was offensive and on the passionate side, McGruff. I had no ill intentions, just a strong disagreement. I have edited my post to make it a little more polite.
    Garcia

  23. #23
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  24. #24
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  25. #25
    SitePoint Evangelist ghurtado's Avatar
    Join Date
    Sep 2003
    Location
    Wixom, Michigan
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Garcia


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •