Course Banner


PHP PDO

What is PDO ?

PDO is acronym of PHP Data Objects. It is an extension of PHP. It defines an easy interface for accessing databases in PHP. It facilitates a data-access abstraction layer for working with databases in PHP. PDO was introduced with PHP version 5.

How PDO Works

The PDO works as a bridge between PHP and a database server. Normally there are 3 stems followed in this.
1. Prepared : PDO prepared statement is written.
2. BindParam: In the second step bind statement is written. Here the data is bound with the fields. This bindParam is used for data input like insert and update.
3. Execute : Finally the prepared and bindParam are executed and data result set is generated.
If there is any error, The PDOException takes care of that.

PHP PDO fetch

The fetch parameter shows how the next row will be returned to the caller. For instance, the PDO::FETCH_ASSOC returns an array indexed by column name, PDO::FETCH_NUM returns an array indexed by column number, and the PDO::FETCH_BOTH returns an array indexed by both column name and indexed column number. The default fetch style is PDO::FETCH_BOTH.

To understand the practical example, below I am going to use countries table example.

Open phpMyadmin create a table called countries. And insert 10 country names with their country code. So, the database structure will be like this.
id
name
country_code
population

PHP PDO parameter binding

Sql queries are generated normally with user input parameters. But in some cases, the user(hacker may put malicious code). It has some serious security implications. To avoid this, a prepared statement is used which filters the data first and then displays to the user.

PDO uses bindParam() and bindValue() method to create parameterized queries.

PDO allows to bind data to question mark or named placeholders.

PHP PDO Use of bindParam

MySQLi supports the use of anonymous positional placeholder (?), as shown below:

INSERT INTO country (name, country_code, population) VALUES (?, ?, ?);

On the other hand, PDO supports both anonymous positional placeholder (?), as well as the named placeholders. A named placeholder begins with a colon (:) followed by an identifier, like this:

INSERT INTO country(name, country_code, population) VALUES (:name, :country_code, :population);

PHP Benefits

1. You don't have to deal with escaping values : it's done automatically (when using bound parameters, of course)
2. The statement is sent to the SQL server, prepared only once ; and, then, can be executed several times -- which is great for performances (the statement is parsed only once, even if executed lots of times)

BindValue and BindParam

Bind Value BindParam
The bindValue() is a built in PHP function which binds a value to named or question mark in SQL statement. The bindParam() function binds a parameter. The value is executed at the time of execute() function.
The bindValue() function is used to pass both value and variable. The bindParam () function is used to pass variable not value.

INSERT DATA

In this video , you will learn how the insert statement works with PDO.
First I will show you without PDO and with PDO so that you can understand the difference.

client-side-validatiion.jpg

Software Used

  1. Xampp with latest PHP 7
  2. Bootsrap

Pre-requisite

  1. Knowledge of Core PHP.
  2. Basic knowledge of CSS and Bootstrap.

Steps to be followed

  1. Create Database
  2. Create table
  3. Insert Form
  4. View List
  5. Edit page
  6. Update Page
  7. Delete Page