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:
- Create a database connection to the database server.
- Execute a
SELECT
statement by passing it to thequery()
method of aPDO
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 aSELECT
statement to query data from one or more tables.