How to Setup MySQL Connection Using PDO in PHP

How to Setup MySQL Connection Using PDO in PHP

by

In previous article, we have discussed that MySQL is depreciated and their alternative options are MySQLi and PDO(PHP Data Object) . In that you have learned how to set up connection using MySQLi in PHP with procedural and object-oriented approach.

In this tutorial you will get how to set up a PHP MySQL connection using PDO for database integration. PDO is an interface which is used for database access.

Advantages:

  •  Features to create prepared statement to secure from SQL injection.
  • Object oriented usage.
  •  PDO use OOPs API.
  •  PDO can be used with many databases. This extension supports all databases that the PDO driver has been written for mean one API can be used for all type of RDBMS.
  •  PDO is easy to migrate from one database to other.

How to connect to MySQL using PDO

Connection

In this approach connection is set up by creating instance (object) of PDO Class and passing driver name, database name, database user and password.

<?php
$conn = new PDO("mysql:host=localhost;dbname=$dbname",$username,$password);
?>

Now with above script MySQL connection is set up using PDO in PHP. If there is any error in database connection and we want to handle it, then we use try/catch method for exception (specified error) handling.

Connection Errors Handling

<?php
try
{
    $conn = new PDO("mysql:host=localhost;dbname=$dbname",$username,$password);
    $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
    echo "Error! : ".$e->getMessage();
    die();
} ?>

If there is an error in database connection
$conn->setAttribute is used to set attribute for database handling.
PDO::ATTR_ERRMODE will generate error reporting.
PDO::ERRMODE_EXCEPTION will throw exception.

Exceptions

PDO::ATTR_ERRMODE : attribute is used for error reporting. This attribute may have one of the given below value. You can easily get its work when you read its name.
PDO::ERRMODE_SILENT : This is default value. If we pass this value for above given attribute, then exception not throw when an error occurs during database connection.
PDO::ERRMODE_WARNING : When this value is passed for above given attribute then a warning is display when an error occurs and script execute continuously.
PDO::ERRMODE_EXCEPTION: This value will generate exception when an error occurs.
ex:

$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

Always prefer PDO over MySQLi in your web application development for more security.

I hope you will be able to use PDO connection in your web application after reading this article.If you have any query please feel free to write in comment box.

Leave a Comment