PHP to begin deprecation of ext/mysql -- start moving your development to PDO now

[essellar]

While there won't be any immediate effect, the PHP developers have finally decided to begin the long, slow, soft process of deprecating the mysql extension. Anyone doing development in PHP using MySQL as a database back-end should have stopped using the mysql methods long ago (it has been superceded by two newer methods over the years, first by mysqli, then by PHP Data Objects (PDO)), but it has taken until now for the first warning shot to be fired.

For those not aware of the dangers, using the PHP mysql extension is one of the quickest and easiest ways of making your site vulnerable to SQL injection attacks. In addition, it forces you to handle all of the details of sanitising (preparing) data for storage in your database, and the follies of string escaping have provided a consistent source of laughs over the years (have you ever seen the ever-growing forest of backslashes as you page through search results?).

There have been better ways of doing things for quite a while now. Unfortunately, there are also a lot of code snippets and tutorials on the web that still use the mysql extension, and many of those are on sites that claim to be authoritative (I'm looking at you, W3Schools). And now, knowing that ext/mysql is going to go away, there is no longer any excuse for using it in new development. Stay far, far away from any tutorial or library that encourages you to use the mysql extension -- if you are searching for PHP snippets or tutorials, make sure you include PDO in your search query.

Just one small suggestion when using PDO, though -- do not use the ? (ordered parameters) syntax when preparing statements; use the :variable (named parameters) syntax instead. It'll keep you out of the asylum.

 

[misson]

I'd give slightly different advice about positional parameters. A positional parameter is fine if a statement has only one parameter and will only ever have one parameter. Multiple positional parameters are also fine if the statement is programmatically generated and executed, such as for an ORM. More than one parameter in a hand-written statement and named is definitely the way to go.

 

[callumacrae]

I <3 this thread

 

[Skizzerz]

So do I -- stickied.

 

[alfred4w]

nice...8)

 

[miguelkp]

Here is the best article/tutorial about PHP PDO usage (including examples) I've found, for now al least:

http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html

 

[misson]

That one has a few issues (use of SELECT *, extraneous information, information disclosure in error handling code). As is often the case with tutorials online, I've yet to see one that should be used to the exclusion of others. It's usually best to read multiple tutorials, so you don't get stuck in one way of thinking.

• PDO Tutorial for MySQL Developers
• "Why you Should be using PHP's PDO for Database Access" (more a how-to than a why)
• "Writing MySQL Scripts with PHP and PDO"

In the end, it's most important to read the PDO manual pages for any information left out of the tutorials.

 

[miguelkp]

True: best comparing various tutorials, instead of using only one. And thanks for the links.
I also use the last one you put (the official php.net documentation). The best, obviously, but first time I read it, I was a bit lost.

So what I like in the one I put is that it follows similar structure than the old mysql extension tutorials. I mean, it explains kinda 'step-by-step' how to connect, how to work with opened database (queries) and how to close connection; ie, it tells you what functions (or rather, methods) you have to look for in php.net to get the finest information. That way is the one I'm using right now and I'm finding easier to move from old mysql extension to PDO sentences.

 

[xdevx10m]

I switched to mysqli 2 years ago. PDO is nice but mysqli is much easier and it supports prepared statements as well.
http://php.net/manual/en/mysqli.prepare.php

For those who stucked with old mysql ext this tool can help a lot. (Don't forget to backup your old app just in case)
http://forge.mysql.com/wiki/Converting_to_MySQLi

 

[misson]

PDO is nice but mysqli is much easier and it supports prepared statements as well.[/URL]

mysqli is simpler as a drop-in replacement for the outdated mysql extension, as the interfaces are similar, but PDO is generally considered the easier to use and more expressive extension, requiring fewer method calls to achieve the same affect. The API Overview on MySQL.com says: "PDO has its advantages, such as a clean, simple, portable API". The reason to use mysqli over PDO is that the former exposes advanced MySQL features. PDO is a generic DB interface, so its MySQL driver can't expose many MySQL specific features without causing compatibility issues (differences between RDBMSs in SQL support cause enough problems).

Things are slightly better for mysqli in PHP 5.4, when support for the Traversable interface was added to mysqli_result. If you must have 5.3 compatibility, however, PDO's support for Traversable is a big win when it comes to abstraction. You can use a PDOStatement in the place of an array or other collection type if all that's needed is to iterate over it (with foreach). You can use PDOStatement::setFetchMode to fetch results as objects for some real magic.

 

[mariannaguide56]

+1 for this post

 

[simon.evanz48]

Another great little gem is called Doctrine2 very good at enabling developers to "separate their concerns" and abstracting data away from application logic.
Doctrine2 is a ORM and it uses its own database abstraction layer called DBAL.

Check out this website for a insight into Doctrine & database abstraction layers: http://gonzalo123.wordpress.com/2011/07/11/database-abstraction-layers-in-php-pdo-versus-dbal/

 

[essellar]

It's important to note that Doctrine2/DBAL uses PDO; it isn't a replacement or an installable PHP language extension. (Mostly, it seems to be about moving transactions/ACID into code to make up for missing/inconsistent database features.)