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>