PDO (PHP Data Objects)

In the last lesson we learnt Traits . In this lesson we will learn mysql database connection using PDO and we will perform some basic sql queries like (insert, select, update, delete). You might be thinking at this time that if mysql, mysqli are already available then why to learn this PDO ? Since you also know for everything there is its expiry date, mysql , mysqli are crossing its expiry date and has been declared deprecated , will be completely removed from upcoming versions.

These technological developments are carried out to remove the bugs ,enhancing the software performance w.r.t the earlier version of the software.

There are some benefits of using the latest PDO extensions.

  • PDO is object oriented from its core.
  • It is compatible with other databases like postgresSql, Oracle, SQLite e.t.c.
  • From an attacker point of view, it is harder to break which means improved security.

How to connect mysql database using PDO

In order to connect to database we will be using credentials like, DB_HOSTNAME, DB_USERNAME, DB_PASSWORD and DB_NAME .

 

It is best practice to use try-catch block where there are chances of exception occurrence.

Lets create a database named “pdophp” in your phpmyadmin and test your database connection.

So let’s starts with our first sql query i.e insert query.

Insert data into database using PDO

So first create a table in the pdophp database using the below query.

I am writing both the PDO and procedural query to understand this easily.

Insert query (PDO) : 

   $sql = “insert into users (name,phone,city,date_added) values (:name,:phone,:city,:date)";

Insert query (Procedural mysqli)

$sql = "insert into users (name,phone,city,date_added) values (‘$name’, ‘$phone’, ‘$city’, ‘$date’)”;

If you compare both the queries you will find that in PDO QUERY we used :name,:phone,:city,:date as a template , 

While in PROCEDURAL QUERY we use direct values ‘$name’,’$phone’,’$city’,’$date’.Let’s see complete insertion code using PDO.

In the above insert program you have noticed that PDO::PARAM_STR . This third parameter in bindParam is used to filter the data before sending to database. There are many others filtration parameter like PDO::PARAM_STR as.

• PDO::PARAM_INT ( for integer).
• PDO::PARAM_STR (for string).
• PDO::PARAM_NULL allows only NULL datatype.
• PDO::PARAM_BOOL allows only boolean (true/false) values.

Retrieve data from database (Select query)

Fetching data from database is similar to inserting data into database. Have look at the below program you will understand how easy it is.

In the above select example you can understand easily till the step $query -> execute(); This is same illustrated in insert query.

$results = $query -> fetchAll(PDO::FETCH_OBJ);

PDO::FETCH_OBJ is used to fetch data as object. If you want to fetch data in array you can use PDO::FETCH_ASSOC.

Update data in database using PDO

Delete data from database using PDO

 

When our query gets executed then you need to close the database connection. Since in PHP the database gets close automatically but if you need to close the database connection at any point during code you may use .

$dbc = null;

Leave a Reply

Your email address will not be published. Required fields are marked *