Fetching Database Result Set
query
The PDO query() method is use to execute SQL statements and results as a PDOStatement object. The query() method requires only one argument the desired query statement.
<?php
require_once "db.php";
$sql = "SELECT * FROM movies";
$result = $db->query($sql);
The PDOStatement class has several methods that can be used to retrieve the results that were return from the Database. The most common of these is fetch() and fetchAll().
fetch
The fetch() method is used to fetch the next row from a result set. It is often used in conjunction with a while loop.
<?php
require_once "db.php";
$sql = "SELECT * FROM movies";
$result = $db->query($sql);
?>
<ul>
<?php while ($movie = $result->fetch()) : ?>
<li><?php echo $movie['movie_title']; ?></li>
<?php endwhile; ?>
</ul>
A fetch_style can be applied to fetch() method to set how PDO will return the row. One such fetch_style is PDO::FETCH_ASSOC which returns an array index by the column name of the database. This can be useful if we wanted to use a foreach loop to output all columns without naming them directly.
<?php
require_once "db.php";
$sql = "SELECT * FROM movies";
$result = $db->query($sql);
?>
<table>
<?php while ($movie = $result->fetch(PDO::FETCH_ASSOC)) : ?>
<tr>
<?php foreach ($movie as $column) : ?>
<td><?php echo $column; ?></td>
<?php endforeach ?>
</tr>
<?php endwhile; ?>
</table>
fetchAll
The fetchAll() method returns an array containing all of the rows from a result set. Like the fetch() method the fetchAll() method also has use of the fetch_styles.
<?php
require_once "db.php";
$sql = "SELECT * FROM movies";
$result = $db->query($sql);
$movies = $result->fetchAll(PDO::FETCH_ASSOC);
?>
<table>
<?php foreach ($movies as $movie)) : ?>
<tr>
<?php foreach ($movie as $column) : ?>
<td><?php echo $column; ?></td>
<?php endforeach ?>
</tr>
<?php endforeach; ?>
</table>