How to Fetch MySQL Results With PDO in PHP
- Setup a Database
-
Fetch Results With
pdostatement.fetchall()in PHP - Fetch Results by Iterating Over the PDO Statement in PHP
-
Fetch Results With PDO
fetch()Method in PHP -
Preprocess Results of PDO
fetch()Method
PHP PDO allows you to work with multiple databases via a uniform interface. It simplifies routine database operations such as result fetching.
This tutorial will explain how to fetch multiple results returned from a PDO statement. You will use PDOStatement.fetchAll, Array Iteration, and the fetch() method in a while loop.
Setup a Database
For this tutorial, you’ll need a MySQL database to follow along. Download and install the XAMPP server. Launch the XAMPP control panel and log in to the MySQL shell.
# This login command assumes that the
# password is empty and the user is "root"
mysql -u root -p
Use the following SQL query to create a database called fruit_db.
CREATE database fruit_db;
Output:
Query OK, 1 row affected (0.001 sec)
To create a sample data that you can work with, execute the following SQL on the fruit_db database:
CREATE TABLE fruit
(id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
color VARCHAR(20) NOT NULL,
PRIMARY KEY (id))
ENGINE = InnoDB;
Output:
Query OK, 0 rows affected (0.028 sec)
Confirm the tables exists with the following:
DESC fruit;
Output:
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| color | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
Once the table is set up, use the following SQL to insert a sample data:
INSERT INTO fruit (id, name, color) VALUES (NULL, 'Banana', 'Yellow'), (NULL, 'Pineapple', 'Green')
Confirm the data existence with the following SQL:
SELECT * FROM fruit;
Output:
+----+-----------+--------+
| id | name | color |
+----+-----------+--------+
| 1 | Banana | Yellow |
| 2 | Pineapple | Green |
+----+-----------+--------+
Now, you can fetch the result from PHP.
Fetch Results With pdostatement.fetchall() in PHP
Before you can fetch results with PDOStament.fetchAll(), you will need to connect to the database that you created earlier. Create a file called config.php, and place the following code; if your database username and password are different, replace them.
<?php
# If necessary, replace the values for the
# user and password variables
$host = 'localhost';
$database = 'fruit_db';
$user = 'root';
$password = '';
?>
When you want to fetch the database result in a file, you need to import the config.php.
Create another file called fetchpdo.php. In the file, you’ll do the following:
-
Connect to the database.
-
Create a new PDO connection.
-
Create a prepared SQL statement with the
prepare()method. -
Execute the statement.
-
Fetch the result with the
fetchAll()method.
Now, type the following code in fetchpdo.php.
<?php
// Require the config file. It contains
// the database connection
require ('config.php');
// Create a connection string
$database_connection = "mysql:host=$host;dbname=$database;charset=UTF8";
// Create a new PDO instance
$pdo = new PDO($database_connection, $user, $password);
// Prepare a SQL statement
$statement = $pdo->prepare('SELECT name, color FROM fruit');
// Execute the statement
$statement->execute();
// Fetch the results
print("Fetch the result set:\n");
$result = $statement->fetchAll(\PDO::FETCH_ASSOC);
print "<pre>";
print_r($result);
print "</pre>";
?>
Output:
Array
(
[0] => Array
(
[name] => Banana
[color] => Yellow
)
[1] => Array
(
[name] => Pineapple
[color] => Green
)
)
Fetch Results by Iterating Over the PDO Statement in PHP
Once you execute the SQL prepared statement, you can iterate over the result with a while loop. You’ll find details in the next code block.
<?php
// Require the config file. It contains
// the database connection
require ('config.php');
// Create a connection string
$database_connection = "mysql:host=$host;dbname=$database;charset=UTF8";
// Create a new PDO instance
$pdo = new PDO($database_connection, $user, $password);
// Prepare a SQL statement
$statement = $pdo->prepare('SELECT name, color FROM fruit');
// Execute the statement
$statement->execute(array());
// Iterate over the array
foreach($statement as $row) {
echo $row['name'] . "<br />";
}
?>
Output:
Banana
Pineapple
Fetch Results With PDO fetch() Method in PHP
The fetch() method will fetch the next row from a result. It allows you to use it in a while loop.
The details are in the next code block.
<?php
// Require the config file. It contains
// the database connection
require ('config.php');
// Create a connection string
$database_connection = "mysql:host=$host;dbname=$database;charset=UTF8";
// Create a new PDO instance
$pdo = new PDO($database_connection, $user, $password);
// Prepare a SQL statement
$statement = $pdo->prepare('SELECT name, color FROM fruit');
// Execute the statement
$statement->execute(array());
// Use while loop over the array
while ($row = $statement->fetch()) {
echo $row['name'] . "<br />";
}
?>
Preprocess Results of PDO fetch() Method
If you’d rather preprocess the database data, you can use the while loop. Then store the processed results in an array. The following code shows you how to do this.
<?php
// Require the config file. It contains
// the database connection
require ('config.php');
// Create a connection string
$database_connection = "mysql:host=$host;dbname=$database;charset=UTF8";
// Create a new PDO instance
$pdo = new PDO($database_connection, $user, $password);
// Create an empty array to store the results
$result = [];
// Prepare a SQL statement
$statement = $pdo->prepare('SELECT name, color FROM fruit');
// Execute the statement
$statement->execute(array());
// Iterate over the result and assign
// new names to the table rows
while ($row = $statement->fetch()) {
$result[] = [
'Fruit Name' => $row['name'],
'Fruit Color' => $row['color'],
];
}
print "<pre>";
print_r($result);
print "</pre>";
?>
Output:
Array
(
[0] => Array
(
[Fruit Name] => Banana
[Fruit Color] => Yellow
)
[1] => Array
(
[Fruit Name] => Pineapple
[Fruit Color] => Green
)
)
Habdul Hazeez is a technical writer with amazing research skills. He can connect the dots, and make sense of data that are scattered across different media.
LinkedIn