6 Replies - 1662 Views - Last Post: 12 September 2012 - 04:55 PM Rate Topic: -----

#1 thelung  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 27-August 12

PHP, PDO, MYSQLI opinions please

Posted 27 August 2012 - 06:29 PM

I've been teaching myself PHP for a little while and I've gotten a real good hang of coding up some meaningful little applications using mainly the 'old' style php mysql extension. However, I've been getting myself into more and more and more complicated coding and have even started myself on my own project that so far as had me working in my spare time for the last few months.
I'm close to 9,000 lines of code into it and am possibly halfway to being done. But, I'm getting paranoid. So frequently when I'm referring to php.net to figure out what I'm doing I keep seeing these notices that recommend I use PDO or mysqli and that the use of all those great functions that I've come to love are 'discouraged'.
Are these extensions really that important to be using? Are you guys using these extensions in place of the old mysql extension? Is the old mysql extension going to go away in a not so distant version of PHP? And, should I go back and recode all my php/mysql transactions to be using this PDO extension that I can't really seem to get the hang of all that well. I've put an honest effort into using it, but all my end-user pages are driven by my hand-coded PHP classes and it seems sooo hard to use PDO from classes.
Please help me figure out what I should be doing. Thanks in advance to anybody willing to help!

Is This A Good Question/Topic? 0
  • +

Replies To: PHP, PDO, MYSQLI opinions please

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3710
  • View blog
  • Posts: 5,958
  • Joined: 08-June 10

Re: PHP, PDO, MYSQLI opinions please

Posted 27 August 2012 - 07:57 PM

*
POPULAR

Yes, most "aware" PHP developers are moving away from the old MySQL API towards MySQLi or PDO. The advantages they offer make them very hard to ignore.

What you should know about the old MySQL API is that it's... old. It was originally developed for MySQL 3, and the last version of MySQL it fully supported was 4.0. When MySQL 4.1 arrived, the old MySQL API was no longer updated to provide support for the new features (most notably: prepared statements), and the Improved MySQL extension (MySQLi) took over as the "current" MySQL API.

PDO was developed as a unified API for all databases, not only MySQL. It allows you to use the same exact classes and methods to interact with all the databases it supports. This will save time later, if/when you start using other database systems, as you won't have to learn an entirely new API to interact with it.

Of the two, I'd say PDO has an advantage, as it's not tied only to MySQL.

Quote

Are these extensions really that important to be using?

Yes. One of the most important things that PDO and MySQLi offer are prepared statements. They improve the security of queries involving user supplied data (which is like 99% of queries in a PHP site) by not injecting them into the query at all. This is very important, seeing as how many PHP developers simply ignore, or are unaware of, things like SQL Injection.

Other things that come to mind here are support for transactions, and the ability to deal with queries that return multiple result sets. Neither of those will work in the old MySQL API, and both can matter a great deal when using complex databases.

Quote

Is the old mysql extension going to go away in a not so distant version of PHP?

As far as I'm aware, no definite decision has been made when the old MySQL API will be removed. I'm thinking they'll keep it around for a lot longer than they would most deprecated features, to support old code. However the fact that it has been marked deprecated in the docs does mean that the PHP devs want it removed sooner than later.

Of course, even if it were to be removed in the very next versions, it'll probably be a decade until you start seeing popular hosting services dropping support for PHP versions that do support the extension. If not longer. We saw how long people will hold on to legacy code when PHP 5 was originally released. For years afterwards, PHP 4 was still the dominant version on PHP hosts. Even now a lot of PHP hosting services will still offer PHP 4 support.

So don't worry that your mysql_query code will suddenly become unusable. That won't happen for a long time.

Quote

And, should I go back and recode all my php/mysql transactions to be using this PDO extension that I can't really seem to get the hang of all that well.

That depends on how you want to proceed with your application. If you've already completed a large portion of it in a way that won't make it easy for you to switch, then you'd probably be best of completing that application using the same method you've been using so far. If you switch to PDO now, then I'm guessing you'd end up in situations where both methods are being used during the same request, which is a waste of resources. (There should only ever be one MySQL connection per request.)

However for future applications, I strongly recommend you switch to PDO or MySQLi.

Quote

I've put an honest effort into using it, but all my end-user pages are driven by my hand-coded PHP classes and it seems sooo hard to use PDO from classes.

It's pretty different, that is true, but it's well worth the effort of learning properly. Of course, we are always willing to help out with specific problems :)

As for using PDO from within classes, that can be easily managed. A lot of people like to pass PDO objects into the constructors of objects that use them, but in my experience that just leads to a lot of unnecessary code being added. I like to use a Singleton class that manages my PDO connection, which allows very easy access to it everywhere in the code. - I posted an example of that here, if you are interested.

P.S.
I've move this thread into the PHP forum. It's more at home here than in the generic Web Development section.

This post has been edited by Atli: 27 August 2012 - 08:00 PM

Was This Post Helpful? 7
  • +
  • -

#3 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2889
  • View blog
  • Posts: 10,010
  • Joined: 08-August 08

Re: PHP, PDO, MYSQLI opinions please

Posted 28 August 2012 - 07:49 PM

I'd say switch as soon as possible to prepared statements. Don't be one of those guys keeping crappy legacy code going long past its expire date. Remember, the more/faster developers move away from the old junk the sooner it can be scrapped on a larger scale and the better off we'll all be. Plus, you get better security for your site with less effort!
Was This Post Helpful? 3
  • +
  • -

#4 thelung  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 27-August 12

Re: PHP, PDO, MYSQLI opinions please

Posted 11 September 2012 - 03:18 PM

Thanks a lot Atli!
I've taken to heart your meaningful response to my question and have started using PDO in my project. Luckily, the way that I have organized the structure of the application has been to segregate almost all of the functional elements of it into their own directories which contain their own specific classes and files so that in future revisions I could simply replace an appropriate directory with an updated version when I felt necessary and have no effect of the other portions of the site/app. Thus allowing me to rewrite an entire section of the application without having to adapt the rest of the application to fit it in. I'm finding out that a little bit of forethought is a fine gift to give yourself when something like this arises.

View PostAtli, on 27 August 2012 - 07:57 PM, said:

PDO was developed as a unified API for all databases, not only MySQL. It allows you to use the same exact classes and methods to interact with all the databases it supports. This will save time later, if/when you start using other database systems, as you won't have to learn an entirely new API to interact with it.


One of the reasons that I had thought to go ahead with implementing PDO in my current project is because I not only want to offer it as an actual website, but also to provide a version of it that could be downloaded and installed on other's servers if they chose to host their own instead of using my site. I figured that since PDO supported so many different databases that it would be absolutely fantastic if my code could support all those other databases, as well. Man, that would have really made me feel like a badass. But, something I've been running into a lot is that there still seems to be a lot of inconsistencies with PDO's mysql support and it's support for other databases. Just one example, PDO::lastInsertId seems to have a lot of variances in it's use depending on which sql server it will be interacting with. So, in order for me to prepare the application to be used with postgresql and mssql I will have to come up with some way to safely check for server version, possibly in the pdo connection string and use some ifelse statements of try/catch blocks when using this function, which I do almost frequently. I am wondering if you happen to know of any workarounds to having to do something like this or if you know of any links that outline discussions of how other programmer's may be are dealing with these kinds of issues with PDO?

Irregardless, I'm going to stick it out with PDO. I have faith that the PHP community will work out some kind of patch to be applied to those cross-database issues in the future.
Thanks a lot for your meaningful input. It is greatly appreciated!
Was This Post Helpful? 0
  • +
  • -

#5 thelung  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 27-August 12

Re: PHP, PDO, MYSQLI opinions please

Posted 11 September 2012 - 03:27 PM

View PostCTphpnwb, on 28 August 2012 - 07:49 PM, said:

Don't be one of those guys keeping crappy legacy code going long past its expire date.


Thanks for the advice, I definitely don't want to be a part of holding php back. I gave that a bit of thought, too. I don't want to be one of 'those guys' in a community where I'm still trying to be one of 'you guys'. :shuriken: Hopefully soon I'll putting forth some meaningful stuff and giving something back.
Was This Post Helpful? 0
  • +
  • -

#6 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3710
  • View blog
  • Posts: 5,958
  • Joined: 08-June 10

Re: PHP, PDO, MYSQLI opinions please

Posted 12 September 2012 - 01:09 AM

View Postthelung, on 11 September 2012 - 10:18 PM, said:

But, something I've been running into a lot is that there still seems to be a lot of inconsistencies with PDO's mysql support and it's support for other databases. Just one example, PDO::lastInsertId seems to have a lot of variances in it's use depending on which sql server it will be interacting with.

That's true. Not all of the functionality can be made 100% cross-compatible, since the different SQL servers implement some things very differently. Like with lastInsertID, for example, MySQL and SQL Server have AUTO_INCREMENT and IDENTITY fields, whereas PostgreSQL uses named sequences. For the latter you need to specify the name of the sequence, which PDO can't do automatically.

The bigger issue is that not all SQL statements are cross-compatible either. A lot of queries can be written in a standard way that'll work most of the time, but you'll sometimes need to do small alterations to the SQL to make it work with a specific database. Moreover, you'll often want to use different SQL statements on different servers for various reasons.

Using PDO doesn't really mean you'll never have to write database specific code. It's more about being able to write the code using the same, familiar API.

What I like to do is not actually use PDO directly in the application code, but instead write wrappers around the database interaction specific to each database. It can be based on a generic one, so cross-compatible code can be reused.

For example, say I had some news feed code that needed the ability to load a specific entry, and a page of entries. That could be written like this:
<?php
namespace db;

use \db\DatabaseConnection AS DB;

class News 
{  
    public function loadById($id) 
    {
        $sql = "SELECT 
                    n.id, n.title, n.text, 
                    n.author_id, a.name AS author_name
                FROM news AS n
                JOIN author AS a
                    ON n.author_id = a.id
                WHERE n.id = ?";
        $stmt = Database::get()->prepare($sql);
        $stmt->bindValue(1, $id, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetch(PDO::FETCH_OBJ);
    }
    
    public function loadPage($index=0, $perPage=10)
    {
        $startIndex = $index * $perPage;
        $sql = "SELECT 
                    n.id, n.title, n.text, 
                    n.author_id, a.name AS author_name
                FROM news AS n
                JOIN author AS a
                    ON n.author_id = a.id
                ORDER BY n.id
                LIMIT :limit OFFSET :offset";
        $stmt = DB::get()->prepare($sql);
        $stmt->bindValue(":limit", $perPage, PDO::PARAM_INT);
        $stmt->bindValue(":offset", $startIndex, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetch(PDO::FETCH_OBJ);
    }
}


However, there is really no "standard" way to return partial result sets (the LIMIT x OFFSET y clause), so while the "loadPage" function will work in that form for databases like MySQL, PostgreSQL and SQLite, it will not work for MS SQL Server and Oracle. For those two you'll have to use other methods.

To allow a MS SQL Server version of this, an extension of this class could be defined it it's own namespace, specific to SQL Server classes.
<?php
namespace db\sqlsrv;

use \db\DatabaseConnection AS DB;
use \db\News as GenericNews;

class News extends GenericNews 
{  
    public function loadPage($index=0, $perPage=10)
    {
        $firstIndex = $index * $perPage;
        $first = $firstIndex + 1;
        $last = $firstIndex + $perPage;
        
        $sql = "SELECT * FROM (
                    SELECT
                        ROW_NUMBER() OVER (ORDER BY n.id) AS rownum
                        n.id, n.title, n.text, 
                        n.author_id, a.name AS author_name
                    FROM news AS n
                    JOIN author AS a
                        ON n.author_id = a.id
                ) AS subtbl
                WHERE rownum BETWEEN :first AND :last";
        
        $stmt = DB::get()->prepare($sql);
        $stmt->bindValue(":first", $first, PDO::PARAM_INT);
        $stmt->bindValue(":last", $last, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_OBJ);
    }
}


The loadById() function is inherited from the generic version, but the loadPage() is rewritten specifically for SQL Server. (Oracle should also support that query, by the way.)


In that situation, the trick would be loading the appropriate class. The best solution to that I've found is to write a factory class that loads either the generic or database specific class if it exists. Like:
<?php
namespace db;

class DatabaseFactory
{
    public static function create($className)
    {
        // Try to load a DB specific version.
        $path = implode(
            DIRECTORY_SEPARATOR, [__DIR__, DB_TYPE, $className . ".php"]
        );
        if (file_exists($path)) {
            $class = "db\\" . DB_TYPE . "\\" . $className;
            return new $class();
        } else {
            // Try to load the generic version.
            $path = implode(
                DIRECTORY_SEPARATOR, [__DIR__, $className . ".php"]
            );
            if (file_exists($path)) {
                $class = "db\\" . $className;
                return new $class();
            } else {
                throw new Exception("Invalid database class");
            }
        }
    }
}


Or you could try some trickery with an autoloader. That might work as well.
Was This Post Helpful? 2
  • +
  • -

#7 thelung  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 27-August 12

Re: PHP, PDO, MYSQLI opinions please

Posted 12 September 2012 - 04:55 PM

Wow, thanks a lot. I'm glad you posted this as this was kind of the direction that I was thinking I would have to go with it, only I wasn't sure of some of those specifics, and you had clarified at least two of the issues that I was wondering about. It's almost like you knew my next step. That is, of course, if I choose to try to support other databases; which might be a better idea to try after I've got a complete working version using mysql. Especially as it is the only one I feel like I'm relatively competent with, at the moment.
Thanks a mil, Atli. I think this is going to go into my bookmarks for a little while as I build up enough momentum with PDO to take on the task of database cross-compatibility.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1