PDO Querying Data

Summary: in this tutorial, you will learn to query data from a table using PHP PDO using the query() method of the PDO object and a prepared statement.

To select data from a table using PDO, you can use:

• The query() method of a PDO object.
• Or a prepared statement.

When a query doesn’t have any parameters, you can use the query() method. For example:

SELECT * FROM publishers;Code language: SQL (Structured Query Language) (sql)

However, if a query accepts one or more parameters, you should use a prepared statement for security reasons.

Using the query() method to select data from a table

To query data from a table using the query() method, you follow these steps:

1. Create a database connection to the database server.
2. Execute a SELECT statement by passing it to the query() method of a PDO object.

The query() method returns a PDOStatement object. If an error occurs, the query() method returns false.

The following illustrates how to query all rows from the publishers table in the bookdb database:

<?php

$pdo = require 'connect.php';

$sql = 'SELECT publisher_id, name 
		FROM publishers';

$statement = $pdo->query($sql);

// get all publishers
$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);

if ($publishers) {
	// show the publishers
	foreach ($publishers as $publisher) {
		echo $publisher['name'] . '<br>';
	}
}
Code language: HTML, XML (xml)

Output:

McGraw-Hill Education
Penguin/Random House
Hachette Book Group
Harper Collins
Simon and Schuster

When you use the PDO::FETCH_ASSOC mode, the PDOStatement returns an associative array of elements in which the key of each element is the column name of the result set.

How it works.

First, create a database connection to the bookdb database:

$pdo = require 'connect.php';Code language: PHP (php)

Second, define an SQL SELECT statement to select all rows from publishers table:

$sql = 'SELECT publisher_id, name FROM publishers';Code language: PHP (php)

Third, run the query by calling the query() method of the PDO object:

$statement = $pdo->query($sql);Code language: PHP (php)

Fourth, fetch all data from the result set:

$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);Code language: PHP (php)

The fetchAll() method with the PDO::FETCH_ASSOC option returns an associative array of data where:

• The keys are the names that appear on the select list
• and the values are the data rows in the result set.

Finally, iterate over the result set and show the array’s element:

<?php

// show the publishers
if ($publishers) {
	foreach ($publishers as $publisher) {
		echo $publisher['name'] . '<br>';
	}
}Code language: HTML, XML (xml)

Using a prepared statement to query data

The following example illustrates how to use a prepared statement to query data from a table:

<?php

$publisher_id = 1;

// connect to the database and select the publisher
$pdo = require 'connect.php';

$sql = 'SELECT publisher_id, name 
		FROM publishers
        WHERE publisher_id = :publisher_id';

$statement = $pdo->prepare($sql);
$statement->bindParam(':publisher_id', $publisher_id, PDO::PARAM_INT);
$statement->execute();
$publisher = $statement->fetch(PDO::FETCH_ASSOC);

if ($publisher) {
	echo $publisher['publisher_id'] . '.' . $publisher['name'];
} else {
	echo "The publisher with id $publisher_id was not found.";
}

Code language: HTML, XML (xml)

How it works.

First, define a publisher id. In practice, you may get it from the query string:

<?php

$publisher_id = 1;Code language: HTML, XML (xml)

Second, use the connect.php to connect to the bookdb database and return a new instance of the PDO object:

$pdo = require 'connect.php';Code language: PHP (php)

Third, construct an SQL SELECT statement with a named placeholder (:publisher_id)

$sql = 'SELECT publisher_id, name 
		FROM publishers
        WHERE publisher_id = :publisher_id';Code language: PHP (php)

Fourth, bind the value of the id to the prepared statement:

$statement->bindParam(':publisher_id', $publisher_id, PDO::PARAM_INT);Code language: PHP (php)

Fifth, execute the prepared statement:

$statement->execute();Code language: PHP (php)

Sixth, fetch a row from the result set into an associative array:

$publisher = $statement->fetch(PDO::FETCH_ASSOC);Code language: PHP (php)

Finally, show the publisher information or an error message:

if ($publisher) {
	echo $publisher['publisher_id'] . '.' . $publisher['name'];
} else {
	echo "The publisher with id $publisher_id was not found.";
}Code language: PHP (php)

Summary

• Use the query() method of an PDO object to execute a SELECT statement to query data from one or more tables.