How to use PDO database connection in PHP

How to use PDO database connection in PHP

Every developer should know that mysql functions for PHP are being depreciated and we must know use mysqli or PDO (PHP Data Objects). So, my today’s tutorial is about use PDO database connection in PHP. In the coming days, the PHP’s version 7 will be preferred in development.

What is in the next lines?

In the next lines, we will make a CRUD for PDO with database in PHP. So, let’s start the main part. Our main parts are

  1. Creating the Database connection
  2. Fetching/reading the data-set
  3. Insert the new entries in the database
  4. Update the existing records
  5. Delete the records

1-Creating the Database connection

We all are familiar with the old way of database connection; but this is now useless.

In term of the finding the errors, PDO is alsp the most powerful tool. We use try/catch method for handing with the errors.

PDO::ERRMODE_EXCEPTION will help us to firing the exceptions when they occer.

2-Fetching/reading the data set

PDO gives us the two ways for fetching the data.

a) query
b) execute

The Query way

In this way, we use query and manually escaping data with $dbcon->quote this method is equivalent to mysql_real_escape_string.

The Execute way

In the above code, we are using the prepare statement and after that we are executing it. Here, the SQL injection seems impossible. The reason is that $id will never directly get into the query. So, we used :user_id as a placeholder.

  • $data->fetch() simply shows array of records and you can make it more stylish with FETCH_OBJ like below.
    while($row = $data->fetch(PDO::FETCH_OBJ)) {
    print_r($row);
    }
  • while($row = $data->fetch(PDO::FETCH_OBJ)) {
    print_r($row);
    }
  • PDO::FETCH_ASSOC: It returns the array with its column names.
  • PDO::FETCH_BOTH: It returns array with column name and its index starts with 0. The fefault fetch style is BOTH
  • PDO::FETCH_OBJ: It returns anonymous object with the property name.
  • PDO::FETCH_NUM: It returns the array with index by column number.

3. Insert the new entries in the database

Now, we will learn that how to insert the records in a table. We can also include multiple records insertion in same time with different data.

4.Update the existing records

Updating the records is not so difficult. Here is the sample code snippet for it.

5.Delete the records

Now, the last thing that how we can delete the record with PDO in database.

So, that was the basic tutorial. Keep coding it and share it.