Difference between revisions of "PDO Tutorial for MySQL Developers"
| Line 1: | Line 1: | ||
| − | ==Why use PDO?== | + | == Why use PDO? == |
mysql_* functions are getting old. For a long time now mysql_* has been at odds with other common SQL database programming interfaces. It doesn't support modern SQL database concepts such as prepared statements, stored procs, transactions etc... It's method for escaping parameters with <code>mysql_real_escape_string</code> and concatenating into SQL strings is error prone and old fashioned. The other issue with mysql_* is that it has had a lack of attention lately from developers, it is not being maintained... Which could mean things like security vulnerabilities are not getting fixed, or it may stop working altogether with newer versions of MySQL. Also lately PHP community have seen fit to start a soft deprecation of mysql_* which means you will start seeing a slow process of eventually removing mysql_* functions altogether from the language (Don't worry this will probably be awhile before it actually happens!). | mysql_* functions are getting old. For a long time now mysql_* has been at odds with other common SQL database programming interfaces. It doesn't support modern SQL database concepts such as prepared statements, stored procs, transactions etc... It's method for escaping parameters with <code>mysql_real_escape_string</code> and concatenating into SQL strings is error prone and old fashioned. The other issue with mysql_* is that it has had a lack of attention lately from developers, it is not being maintained... Which could mean things like security vulnerabilities are not getting fixed, or it may stop working altogether with newer versions of MySQL. Also lately PHP community have seen fit to start a soft deprecation of mysql_* which means you will start seeing a slow process of eventually removing mysql_* functions altogether from the language (Don't worry this will probably be awhile before it actually happens!). | ||
| Line 7: | Line 7: | ||
PDO is enabled by default in PHP installations now, however you need two extensions to be able to use PDO: PDO, and a driver for the database you want to use like pdo_mysql. installing the MySQL driver is as simple as installing the php-mysql package in most distributions. | PDO is enabled by default in PHP installations now, however you need two extensions to be able to use PDO: PDO, and a driver for the database you want to use like pdo_mysql. installing the MySQL driver is as simple as installing the php-mysql package in most distributions. | ||
| − | ==Connecting to MySQL== | + | == Connecting to MySQL == |
old way: | old way: | ||
| Line 38: | Line 38: | ||
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); | $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); | ||
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); | $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); | ||
| + | </source> | ||
| + | |||
| + | == Error Handling == | ||
| + | Consider your typical mysql_* error handling: | ||
| + | <source> | ||
| + | <?php | ||
| + | //connected to mysql | ||
| + | $result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link)); | ||
| + | </source> | ||
| + | |||
| + | OR die is a pretty bad way to handle errors! yet this is typical mysql code. you can't handle die(); it will just end the script abruptly and then echo the error to the screen which you usually do '''NOT''' want to show to your end users, and let nasty hackers discover your schema. | ||
| + | |||
| + | PDO has three error handling modes. | ||
| + | # ''PDO::ERRMODE_SILENT'' acts like mysql_* where you must check each result and then look at <code>$db->errorInfo();</code> to get the error details. | ||
| + | # ''PDO::ERRMODE_WARNING'' throws PHP Warnings | ||
| + | # ''PDO::ERRMODE_EXCEPTION'' throws PDOException, in my opinion this is the mode you should use. It acts very much like <code>or die(mysql_error());</code>, when it isn't caught. But unlike <code>or die()</code> the PDOException can be caught and handled gracefully if you choose to do so. | ||
| + | |||
| + | <source> | ||
| + | <?php | ||
| + | try { | ||
| + | //connect as appropriate as above | ||
| + | $db->query('hi'); //invalid query! | ||
| + | } catch(PDOException $ex) { | ||
| + | echo "An Error occured!"; //user friendly message | ||
| + | some_logging_function($ex->getMessage()); | ||
| + | } | ||
| + | </source> | ||
| + | |||
| + | '''NOTE:''' you do not have to handle with try catch right away! you can catch it anytime appropriate. it may make more sense to catch it at a higher level like outside the function that calls the PDO stuff: | ||
| + | |||
| + | <source> | ||
| + | <?php | ||
| + | function getData($db) { | ||
| + | $stmt = $db->query("SELECT * FROM table"); | ||
| + | return $stmt->fetchAll(PDO::FETCH_ASSOC); | ||
| + | } | ||
| + | |||
| + | //then much later | ||
| + | try { | ||
| + | getData($db); | ||
| + | } catch(PDOException $ex) { | ||
| + | //handle me. | ||
| + | } | ||
| + | </source> | ||
| + | |||
| + | or you may not want to handle the exception with try/catch at all, and have it work much like or die(); does. You can hide the dangerous error messages in production by turning display_errors off and just reading your error log. | ||
Revision as of 13:41, 30 July 2011
Why use PDO?
mysql_* functions are getting old. For a long time now mysql_* has been at odds with other common SQL database programming interfaces. It doesn't support modern SQL database concepts such as prepared statements, stored procs, transactions etc... It's method for escaping parameters with mysql_real_escape_string and concatenating into SQL strings is error prone and old fashioned. The other issue with mysql_* is that it has had a lack of attention lately from developers, it is not being maintained... Which could mean things like security vulnerabilities are not getting fixed, or it may stop working altogether with newer versions of MySQL. Also lately PHP community have seen fit to start a soft deprecation of mysql_* which means you will start seeing a slow process of eventually removing mysql_* functions altogether from the language (Don't worry this will probably be awhile before it actually happens!).
PDO has a much nicer interface, you will end up being more productive, and write safer and cleaner code. PDO also has different drivers for different SQL database vendors which will allow you to easily use other vendors without having to relearn a different interface. (though you will have to learn slightly different SQL probably). Instead of concatenating escaped strings into SQL, in PDO you bind parameters which is easier cleaner way of securing queries. Binding parameters also allow for a performance increase when calling the same SQL query many times with slightly different parameters. PDO also has multiple methods of error handling. The biggest issue I have seen with much mysql_* code is that it lacks consistent handling, or no handling at all! With PDO in exception mode, you can get consistent error handling which will end up saving you loads of time tracking down issues.
PDO is enabled by default in PHP installations now, however you need two extensions to be able to use PDO: PDO, and a driver for the database you want to use like pdo_mysql. installing the MySQL driver is as simple as installing the php-mysql package in most distributions.
Connecting to MySQL
old way:
new way: all you gotta do is create a new PDO object. PDO's constructor takes at most 4 parameters, DSN, username, password, and an array of driver options.
A DSN is basically a string of options that tell PDO which driver to use, and the connection details... You can look up all the options here PDO MYSQL DSN.
You can also pass in several driver options as an array to the fourth parameters. I recommend passing two parameters, One which puts PDO into exception mode, which I will explain in the next section. The other is to turn off prepare emulation which is enabled in MySQL driver by default, but really should be turned off to use PDO safely.
You can also set some attributes after PDO construction with setAttribute method:
Error Handling
Consider your typical mysql_* error handling:
OR die is a pretty bad way to handle errors! yet this is typical mysql code. you can't handle die(); it will just end the script abruptly and then echo the error to the screen which you usually do NOT want to show to your end users, and let nasty hackers discover your schema.
PDO has three error handling modes.
- PDO::ERRMODE_SILENT acts like mysql_* where you must check each result and then look at
$db->errorInfo();to get the error details. - PDO::ERRMODE_WARNING throws PHP Warnings
- PDO::ERRMODE_EXCEPTION throws PDOException, in my opinion this is the mode you should use. It acts very much like
or die(mysql_error());, when it isn't caught. But unlikeor die()the PDOException can be caught and handled gracefully if you choose to do so.
NOTE: you do not have to handle with try catch right away! you can catch it anytime appropriate. it may make more sense to catch it at a higher level like outside the function that calls the PDO stuff:
or you may not want to handle the exception with try/catch at all, and have it work much like or die(); does. You can hide the dangerous error messages in production by turning display_errors off and just reading your error log.
