MYSQL CONNECTION USING PDO WITH PHP

Hello guys, In the previous tutorial we have seen how to connect to MySQL database with PHP using MySQLi. Today we will learn how to connect with PDO.

As a PHP programmer we have learned how to access database by using either MySQL and MySQLi. As of PHP 5.1, there's a better way. PHP Data Objects(PDO) provide methods for prepared statements and working with objects that will make you far more productive!

PDO Introduction:

       PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features.

Database Support:

      The extension can support any database that a PDO driver has been written for. The following drivers currently implement the PDO interface:

PDO_CUBRID	Cubrid
PDO_DBLIB	FreeTDS / Microsoft SQL Server / Sybase
PDO_FIREBIRD	Firebird
PDO_IBM IBM	DB2
PDO_INFORMIX	IBM Informix Dynamic Server
PDO_MYSQL	MySQL 3.x/4.x/5.x
PDO_OCI	Oracle Call Interface
PDO_ODBC	ODBC v3 (IBM DB2,unixODBC and win32 ODBC)
PDO_PGSQL	PostgreSQL
PDO_SQLITE	SQLite 3 and SQLite 2
PDO_SQLSRV	Microsoft SQL Server / SQL Azure
PDO_4D	4D

To find out which drivers you have in your system installed.

print_r(PDO::getAvailableDrivers());

In this tutorial I will tell you how to connect with MySQL with PDO_MYSQL driver.

Connecting to MySQL:

      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 also set some attributes after PDO construction with the setAttribute method:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

<?php $servername = "localhost"; $username = "username"; $password = "password"; try { $conn = new PDO("mysql:host=$servername;dbname=myDB;charset=utf8mb4", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo "Connected successfully"; } catch(PDOException $e) { echo "Connection failed: " . $e->getMessage(); } ?>

Note: If you get an error about character sets, make sure you add the charset parameter to the DSN. Adding the charset to the DSN is very important for security reasons, most examples you'll see around leave it out. MAKE SURE TO INCLUDE THE CHARSET!

Error handling:

Consider MySQLi  error  handling

1 2 3

<?php //connected to mysql $result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));

OR die is a pretty bad way to handle errors, yet this is typical mysql code. You can't handle die(); as 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.

PDO has 3 error handling modes.

• PDO::ERRMODE_SILENT acts like mysql_* where you must check each result and then look at $conn->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 unlike or die() the PDOException can be caught and handled gracefully if you choose to do so.

As already seen in the program you can you use try/catch block to handle exception

Performing a query :

1 2 3 4 5 6

<?php $stmt = $conn->query('SELECT * FROM myTable'); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $row['field1'].' '.$row['field2']; //etc... }

For the query we are going to specify a read of all fields from a given table. The  $query variable selects all rows in the table. You just need to use your table name.

Here you should see the all data as output from your table.

Note the use of PDO::FETCH_ASSOC in the fetch() code above.This tells PDO to return the rows as an associative array with the field names as keys.

Running Insert and Update or Delete statements:

1 2 3 4

<?php $affected_rows = $conn->exec("UPDATE myTable SET field='value'"); echo $affected_rows.' were affected'; ?>

Inserting new data, or updating existing data is one of the more common database operations.for PDO it would look like,This is same for Delete and Insert statements

Closing off the connection:

    You can close any connection by setting the handle to null.

$conn = null;

Prepared statements:

     A prepared statement is a precompiled SQL statement that can be executed multiple times by sending just the data to the server. It has the added advantage of automatically making the data used in the placeholders safe from SQL injection attacks.

Let us see an example

1 2 3 4

<?php $stmt = $conn->prepare("SELECT * FROM myTable WHERE id=? AND name=?"); $stmt->execute(array($id, $name)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

The prepare method sends the query to the server, and it's compiled with the '?' placeholders to be used as expected arguments. The execute method sends the arguments to the server and runs the compiled statement. Since the query and the dynamic parameters are sent separately, there is no way that any SQL that is in those parameters can be executed... so NO SQL INJECTION can occur!

There's a few other ways you can bind parameters as well. Instead of passing them as an array, which binds each parameter as a String type, you can use bindValue and specify the type for each parameter:

1 2 3 4 5 6 7

<?php $stmt = $conn->prepare("SELECT * FROM table WHERE id=? AND name=?"); $stmt->bindValue(1, $id, PDO::PARAM_INT); $stmt->bindValue(2, $name, PDO::PARAM_STR); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); ?>

Named Placeholders:
     Now if you have lots of parameters to bind, doesn't all those '?' characters make you dizzy and are hard to count? Well, in PDO you can use named placeholders instead of the '?':

1 2 3 4 5 6 7

<?php $stmt = $conn->prepare("SELECT * FROM table WHERE id=:id AND name=:name"); $stmt->bindValue(':id', $id, PDO::PARAM_INT); $stmt->bindValue(':name', $name, PDO::PARAM_STR); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); ?>

Some other helpful methods:

     There are a few more methods you'll want to know in order to do basic things with PDO.

$DBH->lastInsertId();

The ->lastInsertId() method is always called on the database handle, not statement handle, and will return the auto incremented id of the last inserted row by that connection.

I hope this tutorial helps you to migrate from MySQL and MySQLi to PDO. If you enjoyed this post, I’d be very grateful if you’d help it spread by emailing it to a friend, or sharing it on Twitter or Facebook.Next Week i will come with a new PHP solution. Thank you! Have a nice day ..😃😃