I see a lot of code hosted on SO implementing my_sql functions. And comments from others (including me) by clicking on the questionnaires to drop MySQL functions and start using PDO or MySQLI. This post is here to help. You can refer to it because it gives an explanation of why they are outdated and what PDO is, plus a minimal code example for implementing PDO.
Primarily:
Converting from mysql functions to PDO is not a simple case of searching and replacing. PDO is object-oriented programming added for the PHP language. This means a different approach when writing code, as in mysql functions. Why convert first?
Why are mysql functions deprecated?
The mysql extension is ancient and has existed since the release of PHP 2.0, released 15 years ago (!!); which is a completely different beast than modern PHP, which is trying to get rid of the bad practices of its past. The mysql extension is a very raw, low-level MySQL connector that lacks many convenient functions, and therefore it is difficult to use correctly in safe mode; this is bad for noobs. Many developers do not understand SQL injection, and the mysql API is fragile enough to make it difficult to prevent it, even if you know about it. It is populated with a global state (for example, an implicit connection), which makes it easy to write code that is difficult to maintain. Since it is old, it can be unreasonably difficult to maintain at the core level of PHP.
The mysqli extension is much newer and fixes all of the above issues. PDO is also fairly new and fixes all of these problems as well as 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 only covers MySQL and MSSQL servers.
Connecting to a MySQL database, prerequisites
It is quite simple and does not require PHP pre-configuration. Modern PHP installations come standard with a module that allows you to connect PDOs to MySQL servers.
php_pdo_mysql.dll module
MSSQL Database Connection, Prerequisites
This is a more advanced setting. You need php_pdo_sqlsrv_##_ts.dll or php_pdo_sqlsrv_##_nts.dll drivers . They are version dependent, hence ## . At the time of writing, Microsoft has released official drivers for PHP 5.5.x. The 5.6 drivers are not yet officially released by Microsoft, but others are available as unofficial assemblies.
php_pdo_sqlsrv_##_ts.dll for safe stream php_pdo_sqlsrv_##_nts.dll for non-protein safe option
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 accepts 3 required arguments and 1 optional.
- A DSN or data source name is basically a string containing information about the name of the driver, host, and database.
- Username
- Password
- Options
MySQL connection
$dsn = 'mysql:dbname=databasename;host=127.0.0.1'; $user = 'dbuser'; $password = 'dbpass'; $dbh = new PDO($dsn, $user, $password);
Let's look at $dsn : first, it defines the driver ( mysql ). Then the database name and finally the node.
Connection to MSSQL
$dsn = 'sqlsrv:Server=127.0.0.1;Database=databasename'; $user = 'dbuser'; $password = 'dbpass'; $dbh = new PDO($dsn, $user, $password);
Let's look at $dsn : first, it defines the driver ( sqlsrv ). Then the node and finally the database name.
When you create an instance, a connection to the database is created. You need to do this only once during the execution of the PHP script.
You need to wrap the PDO instance creation in a try-catch clause. If the creation does not work, a backtrace is displayed that displays critical information about your application, such as username and password. To avoid this, catch errors.
try { $connection = new PDO($dsn, $user, $password); } catch( PDOException $Exception ) { echo "Unable to connect to database."; exit; }
To throw the errors returned by your SQL server, add these parameters to your PDO instance using setAttribute : $connection->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
Query execution
PDO uses prepared statements. This is the real difference between the PDO approach and mysql functions. The latter was very susceptible to SQL-INJECTION. One could build a query like this:
$SQL = 'SELECT ID FROM users WHERE user = '.$username ;
When a malicious website or person sends username injector; DROP TABLE users injector; DROP TABLE users . The results will be devastating. You had to validate your code by escaping and encapsulating strings and variables with quotation marks. This had to be done for every request. On larger websites or poorly maintained code, the risk of getting a form that allows SQL injection can become very high. Prepared statements eliminate the likelihood of introducing first-level SQL, as in the example above.
PDO drivers act as the person in the middle between your PHP server and the database server, called the data access abstraction layer. It does not rewrite your SQL queries, but offers a general way to connect to several types of databases and handles the insertion of variables into the query for you. Mysql functions built a request to execute PHP code. With PDO, a query is actually created on the database server.
Prepared SQL example:
$SQL = 'SELECT ID, EMAIL FROM users WHERE user = :username';
Pay attention to the difference; Instead of a PHP variable using $ outside the string, we introduce the variable with : inside the string. Another way:
$SQL = 'SELECT ID, EMAIL FROM users WHERE user = ?';
How to fulfill the actual request
Your PDO instance provides two methods for executing a request. When you don't have variables, you can use query() when using prepare() variables. query() immediately executed on a call. Pay attention to the object-oriented way of calling ( -> ).
$result = $connection->query($SQL);
Preparation method
The preparation method takes two arguments. The first is an SQL string, and the second is array options. Basic example
$connection->prepare($SQL, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
In our example SQL string, we used a named variable named :username . We still need to bind a PHP variable, integer or string to it. We can do this in two ways. Either create an array containing named variables like key , or use the bindParam or bindValue . I will simply explain the array variant and the bindValue method for simplicity.
Array
You can do something similar for named variables, where you specify the variable as an array:
$queryArguments = Array(':username' => $username);
And this is for indexed variables ( ? ):
$queryArguments = Array($username);
When you have added all the necessary variables, you can call the execute() method to execute() request. Thus, passing an array as an argument to the execute function.
$result = $connection->execute($queryArguments);
bindValue
The bindValue method allows you to bind values to a PDO instance. The method takes two required arguments and one optional. Optional arguments specify the data type of the value.
For the named variables:
$connection->bindValue(':username', $username);
For indexed variables:
$connection->bindValue(1, $username);
After binding the values to the instance, you can call execute without passing any arguments.
$result = $connection->execute();
NOTE. You can only use a named variable once! Using them twice will result in an inability to fulfill the request. Depending on your settings, this will or will not cause an error.
Getting Results
Once again, I will only talk about the basics for getting results from the returned set. PDO is a pretty advanced addition.
Using fetch and fetchAll
If you execute a select query or execute a stored procedure that returns a result set:
fetch
fetch is a method that can take up to three optional arguments. It extracts one row from the result set. By default, it returns an array containing column names in the form of keys and indexed results. Our sample request may 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 display the entire output of the result set:
while($row = $result->fetch()) { echo $row['ID']; echo $row['EMAIL']; }
Here you can find other options: fetch_style ;
fetchAll
Selects all rows in a single Array object. Using the same default option as fetch .
$rows = $result->fetchAll();
If you used a query that did not return results, such as an insert or update query, you can use the rowCount method to retrieve the number of rows affected.
Simple class:
class pdoConnection { public $isConnected; protected $connection; public function __construct($dsn, $username, $password, $host, $dbname, $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 Exception($e->getMessage()); } } public function prepare($SQL, $params=array()) { try { $result = $this->connection->prepare($SQL); $result->execute($params); return $result; } catch(PDOException $e) { throw new Exception($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']; }