When reading on php.net about MySQL functions. I encountered this message
Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:
I've read about PDO. How can I update my code to PDO using either MySQL or MSSQL?
I see a lot of code posted on SO implementing my_sql functions. And comments from others (including myself) pressing the questioners to abandon MySQL functions and start using PDO or MySQLI. This post is here to help. You can refer to it as it provides explanation to why they are deprecated and what PDO is, plus a minimal code example to implement PDO.
First of all:
Conversion from mysql functions to PDO is not a simple case of search and replace. PDO is an Object Oriented Programming add on for the PHP language. That means an other approach in writing the code as with the mysql functions. First why convert?
Why are mysql functions deprecated?
How to implement PDO
PDO offers one solution for connecting to multiple databases. This answer covers only MySQL and MSSQL servers.
Connecting to a MySQL database, prerequisites
This is fairly simple and doesn't require any pre set-up of PHP. Modern PHP installations are standard shipped with a module that allows PDO connections to MySQL servers.
Connecting to a MSSQL database, prerequisites
This is a more advanced set-up. You need
php_pdo_sqlsrv_##_nts.dll drivers. They are version specific hence the
##. At the moment of writing, Microsoft has released official drivers for PHP 5.5.x. The 5.6 drivers aren't yet officially released by Microsoft, but are available as non-official builds by others.
Connecting to a database using PDO To connect to a database you need to create a new PDO instance from the PDO construct.
The PDO constructor takes 1 required arguments and 3 optional.
Connecting to MySQL
Let's take a look at
$dsn: First it defines the driver (
mysql). Then the database name and finally the host.
Connecting to MSSQL
Let's take a look at
$dsn: First it defines the driver (
sqlsrv). Then the host and finally the database name.
When you create the instance a connection is made to the database. You only have to do this once during the execution of a PHP script.
PDO uses prepared statements. This is a real difference between PDO's approach and mysql functions. The latter was very susceptible to SQL-INJECTION. One would build a query like this:
When a malicious website or person posts the username
injector; DROP TABLE users. The results will be devastating. You needed to proof your code by escaping and encapsulating strings and variables with quotes. This had to be done for every query. On larger websites or poorly maintained code the risk of having a form that allowed SQL injection could become very high. Prepared statements eliminates the chance of first tier SQL injection like the example above.
The PDO drivers act as a man-in-the-middle between your PHP-server and database server, called a data-access abstraction layer. It doesn't rewrite your SQL queries, but do offer a generic way to connect to multiple database types and handles the insertion of variables into the query for you. Mysql functions constructed the query on execution of the PHP code. With PDO the query actually gets build on the database server.
A prepared SQL example:
Note the difference; Instead of a PHP variable using
$outside the string, we introduce a variable using
:within the string. Another way is:
How to perform the actual query
Your PDO instance provides two methods of executing a query. When you have no variables you can use
query(), with variables use
query()is immediately executed upon calling. Please note the object oriented way of the call (
The prepare method
The prepare method takes two arguments. The first is the SQL string and the second are options in the form of an Array. A basic example
In our SQL string example we've used a named variable called
:username. We still need to bind a PHP variable, integer or string to it. We can do this in two ways. Either build an array containing the named variables as
keyor use the method
bindValue. I will explain the array variant and the method
bindValuefor the sake of simplicity.
You can do something like this for named variables, where you provide the variable as array key:
And this for indexed variables (
When you have added all the variables you need you can call upon the method
execute()to perform the query. Thereby passing the array as argument to the function
The bindValue method allows you to bind values to the PDO instance. The method takes two required arguments and one optional. The optional arguments set the data-type of the value.
For named variables:
For indexed variables:
After binding the values to the instance, you can call upon
executewithout passing any arguments.
Fetching the results
Again I will only cover the basics for fetching results from the returned set. PDO is a fairly advanced add-on.
If you did a select query or executed a stored procedure that returned a result set:
fetchis a method that could take up to three optional arguments. It fetches a single row from the result set. By default it returns an array containing the column names as keys and indexed results. Our example query could return something like
fetchwill return this as:
To echo all output of a result set:
There are other options you can find here: fetch_style;
Fetches all rows in a single array. Using the same default option as
If you used a query that didn't return results like a insert or update query you can use the method
rowCountto retrieve the amount of rows affected.
A simple class:
How to use: