How to replace MySQL functions with PDO?

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?

The mysql extension is ancient and has been around since PHP 2.0, released 15 years ago (!!); which is a decidedly different beast than the modern PHP which tries to shed the bad practices of its past. The mysql extension is a very raw, low-level connector to MySQL which lacks many convenience features and is thereby hard to apply correctly in a secure fashion; it's therefore bad for noobs. Many developers do not understand SQL injection and the mysql API is fragile enough to make it hard to prevent it, even if you're aware of it. It is full of global state (implicit connection passing for instance), which makes it easy to write code that is hard to maintain. Since it's old, it may be unreasonably hard to maintain at the PHP core level.

The mysqli extension is a lot newer and fixes all the above problems. PDO is also rather new and fixes all those problems too, plus more.

Due to these reasons* the mysql extension will be removed sometime in the future.

source Deceze

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.

The module is php_pdo_mysql.dll

Connecting to a MSSQL database, prerequisites

This is a more advanced set-up. You need php_pdo_sqlsrv_##_ts.dll or 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.

The module is php_pdo_sqlsrv_##_ts.dll for the thread safe variant The module is php_pdo_sqlsrv_##_nts.dll for the non-thread safe variant

Connecting to a database using PDO To connect to a database you need to create a new PDO instance from the PDO construct.

$connection = new PDO(arguments);

The PDO constructor takes 1 required arguments and 3 optional.

1. DSN or Data Source Name, mostly this is a string containing information about the driver, host and database name. Since PHP 7.4 it can also include username and password.
2. Username
3. Password
4. Options

Connecting to MySQL

$dsn = 'mysql:dbname=databasename;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

$dbh = new PDO($dsn, $user, $password);

Let's take a look at $dsn: First it defines the driver (mysql). Then the database name and finally the host.

Connecting to MSSQL

$dsn = 'sqlsrv:Server=127.0.0.1;Database=databasename';
$user = 'dbuser';
$password = 'dbpass';

$dbh = new PDO($dsn, $user, $password);

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.

You need to wrap the PDO instance creation in a try-catch clause. If the creation fails a back trace is shown revealing critical information about your application, like username and password. To avoid this catch the errors.

try 
{
    $connection = new PDO($dsn, $user, $password);
}
catch( PDOException $Exception ) 
{   
     echo "Unable to connect to database.";
     exit;
}

To throw errors returned by your SQL server add this options to your PDO instance using setAttribute: $connection->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

Performing queries

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:

$SQL = 'SELECT ID FROM users WHERE user = '.$username ;

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:

$SQL = 'SELECT ID, EMAIL FROM users WHERE user = :username';

Note the difference; Instead of a PHP variable using $ outside the string, we introduce a variable using : within the string. Another way is:

$SQL = 'SELECT ID, EMAIL FROM users WHERE user = ?';

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 prepare(). query() is immediately executed upon calling. Please note the object oriented way of the call (->).

$result = $connection->query($SQL);

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

$connection->prepare($SQL, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

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 key or use the method bindParam or bindValue. I will explain the array variant and the method bindValue for the sake of simplicity.

Array
You can do something like this for named variables, where you provide the variable as array key:

$queryArguments = array(':username' => $username);

And this for indexed variables (?):

$queryArguments = array($username);

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 execute.

$result = $connection->execute($queryArguments);

bindValue
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:

$connection->bindValue(':username', $username);

For indexed variables:

$connection->bindValue(1, $username);

After binding the values to the instance, you can call upon execute without passing any arguments.

$result = $connection->execute();

NOTE: You can only use a named variable once! Using them twice will result in a failure to execute the query. Depending on your settings this will or will not throw an error.

Fetching the results

Again I will only cover the basics for fetching results from the returned set. PDO is a fairly advanced add-on.

Using fetch and fetchAll

If you did a select query or executed a stored procedure that returned a result set:

fetch
fetch is 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

ID      EMAIL
1       someone@example.com

fetch will return this as:

Array
(
    [ID] => 1
    [0] => 1
    [EMAIL] => someone@example.com
    [1] => someone@example.com
)

To echo all output of a result set:

while($row = $result->fetch())
{
    echo $row['ID'];
    echo $row['EMAIL'];
}

There are other options you can find here: fetch_style;

fetchAll
Fetches all rows in a single array. Using the same default option as fetch.

$rows = $result->fetchAll();

If you used a query that didn't return results like a insert or update query you can use the method rowCount to retrieve the amount of rows affected.

A simple class:

class pdoConnection {
    public $isConnected;

    protected $connection;

    public function __construct($dsn, $username, $password, $options = array()) {
        $this->isConnected = true;
        try {
            $this->connection = new PDO($dsn, $username, $password, $options);
            $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->connection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); //sets the default to return 'named' properties in array.
        } catch (PDOException $e) {
            $this->isConnected = false;
            throw new Exception($e->getMessage());
        }
    }

    public function disconnect() {
        $this->connection = null;
        $this->isConnected = false;
    }

    public function query($SQL) {
        try {
            $result = $this->connection->query($SQL);
            return $result;
        } catch (PDOException $e) {
            throw new PDOException($e->getMessage());
        }
    }

    public function prepare($SQL, $params = array()) {
        try {
            $result = $this->connection->prepare($SQL);
            $result->execute($params);
            return $result;
        } catch (PDOException $e) {
            throw new PDOException($e->getMessage());
        }
    }
}

How to use:

$dsn = 'mysql:dbname=databasename;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

$db = new pdoConnection($dsn, $user, $password);

$SQL = 'SELECT ID, EMAIL FROM users WHERE user = :username';
$result = $db->prepare($SQL, array(":username" => 'someone'));

while($row = $result->fetch())
{
    echo $row['ID'];
    echo $row['EMAIL'];
}   

When you prepare the $sql_data array, you need to prefix the parameter name with a colon:

array('queryString' => $value);

should be:

array(':queryString' => $value);

In your first SELECT, you have AGINST instead of AGAINST.

Your second SELECT appears to be missing a table name after FROM, and a WHERE clause. The LIKE parameters are also not correctly formatted. It should be something like:

sql = "SELECT * FROM ".$db_prefix."_base WHERE article_title_".$lang." LIKE '%:queryString%' OR aricle_text_".$lang." LIKE '%:queryString%'";

Update 1 >>

For both SELECT statements, you need unique identifiers for each parameter, and the LIKE wildcards should be placed in the value, not the statement. So your second statement should look like this:

sql = "SELECT * FROM ".$db_prefix."_base WHERE article_title_".$lang." LIKE :queryString OR aricle_text_".$lang." LIKE :queryString2";

Note queryString1 and queryString2, without quotes or % wildcards. You then need to update your array too:

$sql_data = array(':queryString1' => "%$value%", ':queryString2' => "%$value%");

See the Parameters section of PDOStatement->execute for details on using multiple parameters with the same value. Because of this, I tend to use question marks as placeholders, instead of named parameters. I find it simpler and neater, but it's a matter of choice. For example:

sql = "SELECT * FROM ".$db_prefix."_base WHERE article_title_".$lang." LIKE ? OR aricle_text_".$lang." LIKE ?";

$sql_data = array("%$value%", "%$value%");

<< End of Update 1

I'm not sure what the second SELECT is for, as I would have thought that if the first SELECT didn't find the query value, the second wouldn't find it either. But I've not done much with MySQL full text searches, so I might be missing something.

Anyway, you really need to check the SQL, and any errors, carefully. You can get error information by printing the results of PDOStatement->errorCode:

$sth->execute($sql_data);
$arr = $sth->errorInfo();
print_r($arr);

Update 2 >>

Another point worth mentioning: make sure that when you interpolate variables into your SQL statement, that you only use trusted data. That is, don't allow user supplied data to be used for table or column names. It's great that you are using prepared statements, but these only protect parameters, not SQL keywords, table names and column names. So:

"SELECT * FROM ".$db_prefix."_base"

...is using a variable as part of the table name. Make very sure that this variable contains trusted data. If it comes from user input, check it against a whitelist first.

<< End of Update 1

You should read the MySQL Full-Text Search Functions, and the String Comparison Functions. You need to learn how to construct basic SQL statements, or else writing even a simple search engine will prove extremely difficult.

There are plenty of PDO examples on the PHP site too. You could start with the documentation for PDOStatement->execute, which contains some examples of how to use the function.

If you have access to the MySQL CLI, or even PHPMyAdmin, you can try out your SQL without all the PHP confusing things. If you are going to be doing any database development work as part of your PHP application, you will find being able to test SQL independently of PHP a great help.

The goal

As I see it, your aim in this case is twofold:

• create and maintain a single/reusable connection per database
• make sure that the connection has been set up properly

Solution

I would recommend to use both anonymous function and factory pattern for dealing with PDO connection. The use of it would looks like this :

$provider = function()
{
    $instance = new PDO('mysql:......;charset=utf8', 'username', 'password');
    $instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $instance->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    return $instance;
};

$factory = new StructureFactory( $provider );

Then in a different file or lower in the same file:

$something = $factory->create('Something');
$foobar = $factory->create('Foobar');

The factory itself should look something like this:

class StructureFactory
{
    protected $provider = null;
    protected $connection = null;

    public function __construct( callable $provider )
    {
        $this->provider = $provider;
    }

    public function create( $name)
    {
        if ( $this->connection === null )
        {
            $this->connection = call_user_func( $this->provider );
        }
        return new $name( $this->connection );
    }

}

This way would let you have a centralized structure, which makes sure that connection is created only when required. It also would make the process of unit-testing and maintenance much easier.

The provider in this case would be found somewhere at the bootstrap stage. This approach would also give a clear location where to define the configuration, that you use for connecting to the DB.

Keep in mind that this is an extremely simplified example. You also might benefit from watching two following videos:

• Global State and Singletons
• Don't Look For Things!

Also, I would strongly recommend reading a proper tutorial about use of PDO (there are a log of bad tutorial online).