PDO Querying Data

Summary: in this tutorial, you will learn how to query data from tables using PDOStatement object with different fetch modes.

In order to query data from database table, you have to perform the following steps:

  1. Create a connection to the database by initiating an instance of the PDO class.
  2. Pass a SQL statement to the query() method of the PDO object. This query() method returns a PDOStatement object that allows you to traverse the returned result set. If an error occurred during executing the SQL statement, the query() method return false.

For example, if you want to query all data from the departments table in the employees database ( empdb), you can use the following script:

How it works.

  • First, we included the dbconfig.php file, which is a database configuration file that contains database parameters including host, database name, username and password. These parameters are used to create a connection to the employees database.
  • Second, we created a connection to the employees database by initiating an instance of the PDO class.
  • Third, we called the query() method of the PDO object and passed the SELECT statement that gets all data from the departments table.
  • Fourth, in the body of the HTML document, we called the fetch() method of the PDOStatement object with the FETCH_ASSOC fetch mode and displayed the column’s value of each record.

The following is the output of the script:

PDO query departments data

When we use PDO::FETCH_ASSOC fetch mode, the PDOStatement returns an array indexed by column name.

PDO provides several fetch modes. We will discuss the most commonly used ones in this section.

If you don’t pass a fetch mode to the fetch() method, it will use a default fetch mode which is PDO::FETCH_BOTH¬†that instructs the fetch() method to returns an array indexed by both column name and integer index. For example, if you use PDO::FETCH_BOTH¬†fetch mode, you can access the department column by using not only:

but also

The PDO::FETCH_NUM allows the fetch() method to return an array indexed by integer index. Therefore the FETCH_BOTH mode is the combination of FETCH_ASSOC and FETCH_NUM modes.

You can set fetch mode before calling the fetch() method by calling the setFetchMode() method of the PDOStatement object.

See the following example:

The PDO::FETCH_COLUMN instructs PDO to return a specified column of every row. In this case, the fetch() method return a scalar value:

The PDO statement allows you to query multiple times however before querying a new result set you have to call the closeCursor() method. The following example shows the department with number 1 and employees who belong to department 1.

The following is the output of the script:

PDO query department and employees

In this tutorial, we have shown you how to query data from the database tables using the PDOStatement object with different fetch modes.