Dhwany Technology Pvt. Ltd
dhwany technology menu icon

Blogs

PHP data object (PDO) advance series: create database connection

PHP data object (PDO) advance series: create database connection

sachin     7 Apr 2018     PHP      NA     

PHP 🐘 data object (PDO) helps to create connection with the database. One of the most important advantages is that it can create connections with the multiple databases, not just MYSQL. It supports 12 deferent databases like MySQL, PostgreSQL, MS SQL Server, Oracle, Firebird, Sybase, IBM, Informix, SQLite, FreeTDS, 4D, Cubrid. It provides a data access layer which means no matter that what database you want to access and what system you are using. With the same function, you can execute the query to fetch data. It’s completely object-oriented with methods and properties. It doesn’t support specific syntax for the specific database. In some cases, just switch the database connection to change the database.

Security is the second biggest benefit of PDO. If someone tries to steal your data through SQL injection 💀. That kind of stuff can completely destroy your database. It uses to prevent SQL injection 💀 through its prepared statement. We will study more about this further. The most widely used PHP frameworks and CMS create database connection via POD only. It’s lightweight relational database management system (RDBMS) connection, available since PHP 5.0.

There are 3 main PDO classes.

1.    PDO: It represents the connection between PHP 🐘 and Database

2.    PDOStatement: It represents the prepared statement and after its excited result

3.    PDOExceptions: It represents the error handling. There are different options to handle errors.

 

Here I’m going to show that how can create the connection with different databases. Before the start, I assume that reader has basic knowledge of PHP 🐘 and MYSQL.

There is need to set a DSN. It basically a string that associated with the data structure to describe the connection with the data source. Its include database type, database name with hosting information. The PDO creates the connection with help of Data Source Name (DSN), username and password.

Now, set some variable like

$hostname = “localhost”;
$dbname = “your_db_name”;
$user = “user”;
$password= “password”;

So, I passed a driver concatenate with hosting information and database name. For example mssql:host=$hostname;dbname=$dbname. I’m going to create PDO instance and pass it into a variable called $DTPDO.

# MS SQL Server  
$DTPDO = new PDO("mssql:host=$host;dbname=$dbname, $user, $ password");
   
# MySQL with PDO_MYSQL
$DTPDO = new PDO("mysql:host=$host;dbname=$dbname", $user, $password);

# SQLite Database
$DTPDO = new PDO("sqlite:path/to/directory/database.db");

Just change the driver mssql:’ to change the connection of your database as you want. But SQLite database requires full path where the database is kept.

Notice that wraps your connection code in try/catch block. It means that executes the code in the try block and handle the exceptions in the catch block. If there is an error in the code. PDO has some predefined attribute to handle it. Here I'm discussing only those attributes which help you to fetch errors ‘PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION’. If it finds any type of unsupported connection, connection options or unsupported attribute. The error will be sent to catch block by the setting of given attribute PDO:: ATTR_ERRMODE.   

try{
       $DTPDO = new PDO("mysql:host=$host;dbname=$dbname", $user, $password);
       $DTPDO ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
       echo $DTPDO;  

       } catch(PDOException $e){
             echo 'ERROR: ' . $e->getMessage();
             }

In the next blog, we will discuss about the prepared statement and how it works 🏃. If do you have any query please comment on this blog. You can also connect with us on facebook  and twitter.  


comment icon
amartya 30 Apr 2018
thanks for useful and helpful post

Leave Comments