facebook google plus twitter
Webucator's Free PHP Tutorial

Lesson: Working with Databases

Welcome to our free PHP tutorial. This tutorial is based on Webucator's Introduction to PHP Training course.

There are a few different ways of working with databases in PHP, but the most common two are through extensions that ship as part of PHP: the MySQL Improved (mysqli) extension and the PHP Data Objects (PDO) extension. They work in much the same way. In this course, we will teach the PDO extension as it can be used with any database; whereas mysqli just works with MySQL. We will start the lesson with an overview of objects and classes as it's important to understand a bit about object-oriented programming to work with PDO.

Lesson Goals

  • To work with object-oriented PHP.
  • To connect to a database.
  • To use phpMyAdmin.
  • To query a database and display records.
  • To paginate through records.
  • To sort records.
  • To filter records.

Objects

An object is something that has attributes (properties) and/or behaviors (methods), meaning it is certain ways and does certain things. In the real world, everything could be considered an object. Some objects are tangible, like rocks, trees, tennis racquets, and tennis players. And some objects are intangible, like words, colors, tennis swings, and tennis matches.

Attributes / Properties

If you can say "x is y" or "x has y," then y is an attribute of x. Some examples:

  1. The rock that he is holding is heavy. Heavy is an attribute of the specific rock he is holding. More generally, rocks have weight.
  2. The apple tree in our back yard has four branches. The four branches are attributes of that specific tree. More generally, trees have branches.
  3. Venus Williams' swing is strong. Strong is an attribute of Venus Williams' swing. More generally, tennis swings have a strength.
  4. The final match had three sets. The three sets are attributes of the specific match. More generally, matches have sets.
  5. Serena Williams has a first-serve percentage of 57.2%. A 57.2% first-serve percentage is an attribute of Serena Williams. More generally, tennis players have a first-serve percentage.

Attributes are generally nouns (e.g., branches) or adjectives (e.g., heavy). Attributes of objects are called properties. In PHP, we could write the statements above like this:

$rockHeHolds->weight = 'heavy';
$backyardAppleTree->branches = [branch1, branch2, branch3, branch4];
$venus->swing = 'strong';
$finalMatch->sets = [set1, set2, set3];
$serena->serve1 = .572;

Behaviors / Methods

If you can say "x does" then does is a behavior of x. Some examples of behaviors:

  1. The rock falls fast. More generally, rocks can fall.
  2. The apple tree in our back yard first bore fruit on August 23, 1961. More generally, trees can bear fruit.
  3. Venus Williams' swing hit the ball. More generally, tennis swings can hit things.
  4. The rocket landed at 8:17PM, July 20, 1969. More generally, rockets can land.
  5. Serena Williams served. More generally, tennis players can serve.

Behaviors are verbs and behaviors of objects are called methods, which are simply functions defined within a class definition. In PHP, we could write the statements above like this:

$rockHeHolds->fall('fast');
$backyardAppleTree->bearFruit(mktime(0, 0, 0, 8, 23, 1961));
$venus->swing->hit(ball);
$rocket->land(mktime(0, 20, 17, 7, 20, 1969));
$serena->serve();

Classes vs. Objects

A class is a template for an object. An object is an instance of a class. When we say Serena Williams is a tennis player, we are saying that Serena Williams is an object of the Tennis Player class. There are other tennis players who have the same attributes and behaviors as Serena Williams, but not in the same way. For example, Serena has a winning percentage. Her sister Venus also has a winning percentage. So do Roger Federer and Rafael Nadal. But their winning percentages are all different. They also all have backhands, but they don't all have the same backhand. Roger Federer has a one-handed backhand, while the others all have two-handed backhands. If you needed to express that in PHP, you could do it this way:

$serena->twoHandedBackhand = true;
$venus->twoHandedBackhand = true;
$roger->twoHandedBackhand = false;
$rafa->twoHandedBackhand = true;

New objects are created from classes using the new keyword, like this:

$serena = new TennisPlayer();

In the code above, $serena would be a new object of the TennisPlayer class. TennisPlayer() is a constructor for initializing new objects. Constructors are similar to methods in that they can take arguments. We will see this with the PDO class.

Connecting to a Database with PDO

There are a few different ways of working with databases in PHP, but the most common two are through extensions that ship as part of PHP:

  1. The MySQL Improved (mysqli) extension
  2. The PHP Data Objects (PDO) extension

They work in much the same way. In this course, we will teach the PDO extension as it can be used with any database; whereas mysqli just works with MySQL. We will start the lesson with an overview of objects and classes as it's important to understand a bit about object-oriented programming to work with PDO.

The first step of working with a database is to make the connection. With PDO, the connection is made by initializing a PDO object:

$db = new PDO($dsn, $username, $password);
  1. $dsn - the DSN or database source name. Our DSN will be 'mysql:host=localhost;dbname=poetree'.
  2. $username - the username. We will be using 'root'.
  3. $password - the password. We will be using 'pwdpwd'. In the real world, you should use a much more secure password.

Given the values we will be using, our code for connecting to the database will be:

$dsn = 'mysql:host=localhost;dbname=poetree';
$username = 'root';
$password = 'pwdpwd';
$db = new PDO($dsn, $username, $password);

"localhost" is the name of our local server and "poetree" is the name of the database we'll be using in class. Before we learn more about PDO, let's take a look at our database.

Introducing the Poetree Database

Before we begin querying our database, let's take a look at how it is structured:Poetree ER Diagram

As you can see, the Poetree database consists of four tables:

  1. poems
  2. categories
  3. users
  4. tokens

There are three relationships between the tables:

  1. Each poem is associated with a category. A category can have 0 or more poems associated with it.
  2. Each poem is associated with a user. A user can have 0 or more poems associated with it.
  3. Each token is associated with a user. A user can have 0 or more tokens associated with it.

phpMyAdmin

phpMyAdmin is a web-based tool, which comes bundled with MAMP for managing MySQL databases. It is useful for testing and debugging queries. Follow these steps to open phpMyAdmin:

  1. In MAMP, click on Open WebStart Page: MAMP - Open WebStart Page
  2. That will launch the MAMP WebStart page. Click on the PHPMYADMIN link in the TOOLS menu: MAMP - Home Page - phpMyAdmin link
  3. The left navigation in phpMyAdmin lists the MySQL databases installed. Click the poetree database:phpmyadmin poetree
  4. To run SQL code, click on the SQL tab, enter your SQL statement(s), and click the Go button. For example, the screenshot below shows how to select all records from the poems table using the following query:
    SELECT poem_id, title, date_approved
    FROM poems;
    phpmyadmin poems query
  5. This should return results similar to the following:phpmyadmin poems query Results

While it isn't necessary to use phpMyAdmin to do PHP development, you may find it useful for testing SQL queries and managing data. As you can see from the Edit, Copy, and Delete links, in addition to being able to run SQL statements, phpMyAdmin provides a GUI (Graphic User Interface) for managing the data.

Querying Records with PHP

We have already seen how to connect to the database. Now let's see how we can get data from a table. We'll start with the query we ran in phpMyAdmin to get all the records from the poems table:

SELECT poem_id, title, date_approved
FROM poems;

There are two approaches to running queries with PDO:

  1. Use the query() method to execute the query directly. An example is below:
    $query = 'SELECT poem_id, title, date_approved
              FROM poems';
    $stmt = $db->query($query);
    This will return a PDOStatement object.
  2. Use the prepare() method to prepare the statement for executing. This returns a new PDOStatement object, which you can then execute with the new object's execute() method. An example is below:
    $query = 'SELECT poem_id, title, date_approved
              FROM poems';
    $stmt = $db->prepare($query);
    $stmt->execute();

Both the query() and the prepare() methods return a PDOStatement object.

While using query() is simpler, using prepare() has at least two major advantages:

  1. If you decide to re-run the query, it will run faster the second time.
  2. You do not have to worry about character escaping or SQL Injection attacks, in which hackers try to attack your database content by passing partial or complete SQL statements through your GET and POST variables.

The second advantage is key and is the reason we recommend using prepare(). To illustrate how this works, consider a search form on a website in which a user enters a name to search poem authors. The search input field might be called "title". And your PHP code to create the query might look like this:

$title = $_GET['title'];
$query = "SELECT title, poem
          FROM poems
          WHERE title = '$title'";
$stmt = $db->query($query);

If the searcher enters Carrots and Camels or The Geriatric General, this will be fine. The resulting query would look something like this:

SELECT title, poem
FROM poems
WHERE title = 'Carrots and Camels'

But if the searcher enters Harry's Torment and you don't properly escape the string, the resulting query will look like this:

SELECT title, poem
FROM poems
WHERE title = 'Harry's Torment'

And that apostrophe in Harry's Torment will cause the query to fail when we try to execute it.

With prepare(), you do not have to worry about escaping the string. You simply replace the unknown value in the query with a question mark, like this:

$title = $_GET['title'];
$query = 'SELECT title, poem
          FROM poems
          WHERE title = ?';
$stmt = $db->prepare($query);

And then when you execute the query, you pass in an array of values to replace any question marks in the query. In our example, we only have one:

$stmt->execute([$title]);

Notice that we do not need to (in fact, we cannot) put the question mark in single quotes. Single quotes are added behind the scenes if necessary based on the data type of the field.

Named Parameters

The query above uses a question mark (?) as a placeholder for the poem title. Each question mark is an ordered parameter to be replaced with a value when the query is executed. When we call the execute() method, we pass it an array containing one value for each question mark in the order those question marks appear. The query below has two ordered parameters:

$query = 'SELECT first_name, last_name
          FROM users
          WHERE username = ? AND email = ?';
$username = 'HugHerHeart';
$email = 'herheart@phppoetry.com';
$stmt = $db->prepare($query);
$stmt->execute([$username, $email]);

We can also write the query with named parameters by prefixing each with a colon, like this:

$query = 'SELECT first_name, last_name
          FROM users
          WHERE username = :un AND email = :em';
$username = 'HugHerHeart';
$email = 'herheart@phppoetry.com';
$stmt = $db->prepare($query);

When we execute this statement, we pass in an associative array with key-value pairs:

$stmt->execute(['un'=>$username, 'em'=>$email]);

There is no functional difference between using ordered and named parameters. Use whichever you are more comfortable with.

Binding Parameters

An alternative to passing an array of parameters to the statement's execute() method is binding the parameters to the statement. This can result in cleaner code, especially when there are many parameters. It works like this:

$query = 'SELECT first_name, last_name
          FROM users
          WHERE username = :un AND email = :em';
$username = 'HugHerHeart';
$email = 'herheart@phppoetry.com';
$stmt = $db->prepare($query);
$stmt->bindParam(':un', $username);
$stmt->bindParam(':em', $email);

$stmt->execute();

Notice that in this case nothing is passed to the execute() method. For more on bindParam() see https://www.php.net/manual/pdostatement.bindparam.php.

Fetching the Records

Once you have executed the statement, either using $db->query() or $stmt->execute(), you can fetch the next row (starting with the first) using the $stmt->fetch() method, like this:

$title = $_GET['title'];
$query = 'SELECT title, poem
          FROM poems
          WHERE title = ?';
$stmt = $db->prepare($query);
$stmt->execute([$title]);

$row = $stmt->fetch();

$row will then contain an array holding values for the keys 'title' and 'poem'. We can output them as part of the HTML like this:

<h1><?= $row['title'] ?></h1>
<div><?= nl2br($row['poem']) ?></div>

nl2br()

Note that we pass $row['poem'] to the built-in nl2br() function, which replaces all newline characters with <br> tags.

See https://www.php.net/nl2br for documentation.

If $stmt->fetch() doesn't return any rows (e.g., because the query didn't return any results) then it will return false to $row. If you then attempt to treat $row as an array (e.g., $row['title']), you will get an error. To prevent this, you should do a check. For example:

<?php if ($row) { ?>
  <h1><?= $row['title'] ?></h1>
  <div><?= nl2br($row['poem']) ?></div>
  <?php } else { ?>
  <h1>No Results</h1>
  <p>Sorry, we couldn't find a poem by that name.</p>
<?php } ?>

The following demos show how to put all this together. First, an HTML search form:

Code Sample:

Database/Demos/poem-search.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<link rel="stylesheet" href="../../static/styles/normalize.css">
<link rel="stylesheet" href="../../static/styles/styles.css">
<title>Search Poem</title>
</head>
<body>
<main>
<form method="get" action="pdo-prepare.php">
  <label for="title">Poem Title:</label>
  <input type="search" name="title" id="title">
  <button class="wide">Search</button>
</form>
</main>
</body>
</html>

Code Sample:

Database/Demos/pdo-prepare.php
<?php
  $dsn = 'mysql:host=localhost;dbname=poetree';
  $username = 'root';
  $password = 'pwdpwd';
  $db = new PDO($dsn, $username, $password);
  $title = $_GET['title'];
  $query = "SELECT title, poem
    FROM poems
    WHERE title = ?";
  $stmt = $db->prepare($query);
  $stmt->execute([$title]);
  $row = $stmt->fetch();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<link rel="stylesheet" href="../../static/styles/normalize.css">
<link rel="stylesheet" href="../../static/styles/styles.css">
<title><?= $row['title'] ?></title>
</head>
<body>
<main>
<?php if ($row) { ?>
  <h1><?= $row['title'] ?></h1>
  <div><?= nl2br($row['poem']) ?></div>
<?php } else { ?>
  <h1>No Results</h1>
  <p>Sorry, we couldn't find a poem by that name.</p>
<?php } ?>
</main>
</body>
</html>

Open http://localhost:8888/Webucator/php/Database/Demos/poem-search.html in your browser and search for Harry's Torment. You should get the following result:Poem Search Results: Success

Now go back to the form and search on some random string (e.g.,foobar). You should get the following result:Poem Search Results: No Results

Creating a Single Poem Page

Duration: 20 to 30 minutes.

In this exercise, you will create the poem.php page of the PHP Poetry website.

  1. Open Database/Exercises/phppoetry.com/poem.php in your editor. Currently, the page always outputs the same poem.
  2. Edit the page so that it expects a poem-id parameter to be passed on the query string and uses it to get the poem data from the database. You will need to get the following fields from the database:
    1. poems.title
    2. poems.poem
    3. poems.date_submitted
    4. poems.date_approved
    5. users.username
  3. Using the data returned from the database, edit the page so that the content highlighted below is dynamic (i.e., based on the poem-id):phppoetry.com poem.php
  4. Visit http://localhost:8888/Webucator/php/Database/Exercises/phppoetry.com/poem.php?poem-id=1 to test your solution. Then try passing in other values for poem-id.

Look in Database/Exercises/phppoetry.com/sql.txt if you need help with the SQL query.

Making use of php.net

To complete this exercise, you will likely need to review two functions on https://www.php.net:

  1. date() - This function is used to format a date or a time.
  2. strtotime() - This function converts a string (like the ones returned from the database for the date_published and date_approved fields) to an integer representing the number of seconds since the epoch.

Solution:

Database/Solutions/phppoetry.com/poem.php
<?php
  $dsn = 'mysql:host=localhost;dbname=poetree';
  $username = 'root';
  $password = 'pwdpwd';
  $db = new PDO($dsn, $username, $password);
  $poemId = $_GET['poem-id'];
  $query = "SELECT u.username,
    p.title, p.poem, p.date_submitted, p.date_approved
    FROM users u
      JOIN poems p ON u.user_id = p.user_id
    WHERE p.poem_id = ?";
  $stmt = $db->prepare($query);
  $stmt->execute([$poemId]);
  $row = $stmt->fetch();
  
  if ($row) {
    $title = $row['title'];
    $authorUserName = $row['username'];
    $dateSubmitted = $row['date_submitted'];
    $dateApproved = $row['date_approved'];
    $poem = $row['poem'];
  } else {
    $title = 'Poem Not Found';
  }
  
  $pageTitle = $title;
  require 'includes/header.php';
?>
<main id="poem">
  <h1><?= $title ?></h1>
  <?php if ($row) { ?>
    <div id="submission-status">
      Submitted on <?= date('m/d/Y', strtotime($dateSubmitted)) ?>
      at <?= date('g:iA', strtotime($dateSubmitted)) ?>
      by <?= $authorUserName ?>
      <a href='#'>Edit</a>
      <a href='#'>Delete</a>
    </div>
    <div id="approval-status">
      Approved: <?= date('m/d/Y', strtotime($dateApproved)) ?>
    </div>
    <article class="poem">
      <?= nl2br($poem) ?>
    </article>
  <?php } else { ?>
    <p>Sorry, we couldn't find the poem you're looking for.</p>
  <?php } ?>
---- C O D E   O M I T T E D ----

Code Explanation

After fetching $row, we check to see if it returned a truthy value (an array). If it did, we set our variables accordingly. If it did not, we only set the $title variable, which we set to 'Poem Not Found'.

After the if condition, we set $pageTitle, which is used in header.php as part of the HTML title.

In the body, we again check $row (line 31), and based on its value, either output the poem data or a friendly message saying we couldn't find that poem.

Queries Returning Multiple Rows

When we queried the poems table using a poem_id, we knew that we would get at most one row, because each poem_id are unique. Now we will look at how we can get and output multiple poems. The following query, for example, will return all poem titles and their categories:

SELECT p.title, c.category
FROM poems p
JOIN categories c ON c.category_id = p.category_id

Assuming the query returns results, the PDOStatement object's fetch() method returns a row as an array with keys 'title' and 'category'. Each subsequent time fetch() is called, it gets the next row until there are no rows left, at which point it returns false.

Because fetch() returns false when there are no more rows to fetch, we can use a while loop to iterate through the results, checking the value returned from fetch() at the same time as we assign that value to $row, like this:

while ($row = $stmt->fetch()) {
  echo $row['title'] . ': ' . $row['category'] . '<br>';
}

The following demo puts this together:

Code Sample:

Database/Demos/poem-categories.php
<?php
  $dsn = 'mysql:host=localhost;dbname=poetree';
  $username = 'root';
  $password = 'pwdpwd';
  $db = new PDO($dsn, $username, $password);
  $query = "SELECT p.title, c.category
    FROM poems p
    JOIN categories c ON c.category_id = p.category_id";
  $stmt = $db->prepare($query);
  $stmt->execute();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<link rel="stylesheet" href="../../static/styles/normalize.css">
<link rel="stylesheet" href="../../static/styles/styles.css">
<title>Poem Categories</title>
</head>
<body>
<main>
  <h1>Poem Categories</h1>
  <ol>
  <?php
    while ($row = $stmt->fetch()) {
      echo '<li>' . $row['title'] . ' - '
            . $row['category'] . '</li>';
    }
  ?>
  </ol>
</main>
</body>
</html>

Run this code and you should see the following:Poem Categories

You've Got Skills!

You have now learned the basic PHP skills for building database-driven applications. In the following series of exercises, you will create a sortable table of poems that you can filter and paginate through.

Creating the Poems Listings

Duration: 25 to 40 minutes.

In this exercise, you will create the poem listing on the home page and the poems.php page of the PHP Poetry website.

  1. Open Database/Exercises/phppoetry.com/poems.php in your editor. Currently, the page outputs a table with two rows of static data.
  2. Edit the page so that it outputs all poems that have been approved, beginning with those most recently approved . You will need to get the following fields from the database:
    1. poems.poem_id - used to pass a value to poem.php when the poem title is clicked.
    2. poems.title - the poem title.
    3. poems.date_approved - the published date.
    4. categories.category - the category.
    5. users.username - the author's username.
  3. Be sure to make the links work, so that poem.php shows the correct poem.
  4. Visit http://localhost:8888/Webucator/php/Database/Exercises/phppoetry.com/poems.php when you are done. It should look something like this:phppoetry.com poems.php
  5. Be sure to test the poem title links.
  6. Now open Database/Exercises/phppoetry.com/index.php in your editor. Modify this table so that it gets and shows the latest three poems published. To limit the records returned in a MySQL query, add the following clause after the ORDER BY clause:
    LIMIT 0, 3
    • 0 is the offset, meaning the number of records to skip. In this case, we are starting with the first record.
    • 3 is the number of rows to return.

Look in Database/Exercises/phppoetry.com/sql.txt if you need help with the SQL query.

Challenge

The number of total poems shown is static. You can get the actual number by writing a new query using the same WHERE conditions, but selecting a COUNT. See if you can make the page show the actual count.

Solution:

Database/Solutions/phppoetry.com/poems.php
<?php
  $pageTitle = 'Poems';
  require 'includes/header.php';
  
  $dsn = 'mysql:host=localhost;dbname=poetree';
  $username = 'root';
  $password = 'pwdpwd';
  $db = new PDO($dsn, $username, $password);
  $query = "SELECT p.poem_id, p.title, p.date_approved, 
  c.category, u.username
          FROM poems p
          JOIN categories c ON c.category_id = p.category_id
          JOIN users u ON u.user_id = p.user_id
          WHERE p.date_approved IS NOT NULL
          ORDER BY p.date_approved DESC";
  $stmt = $db->prepare($query);
  $stmt->execute();
?>
<main id="poems">
  <h1><?= $pageTitle ?></h1>
  <table>
    <caption>Total Poems: 8</caption>
    <thead>
      <tr>
        <th>Poem</th>
        <th>Category</th>
        <th>Author</th>
        <th>Published</th>
      </tr>
    </thead>
    <tbody>
      <?php
        while ($row = $stmt->fetch()) { 
          $approved = strtotime($row['date_approved']);
          $published = date('m/d/Y', $approved);
      ?>
        <tr class="normal">
          <td>
            <a href="poem.php?poem-id=<?= $row['poem_id'] ?>">
              <?= $row['title'] ?>
            </a>
          </td>
          <td><?= $row['category'] ?></td>
          <td><?= $row['username'] ?></td>
          <td><?= $published ?></td>
        </tr>
      <?php } ?>
    </tbody>
---- C O D E   O M I T T E D ----

Code Explanation

Notice that we chose to put the start of the while loop in one PHP block and the end in a separate PHP block. If we had put them in the same PHP block, we would have had to create the table row using a lot of concatenation. While that's perfectly valid, it can get a little messy.

Solution:

Database/Solutions/phppoetry.com/index.php
<?php
  require 'includes/header.php';

  $dsn = 'mysql:host=localhost;dbname=poetree';
  $username = 'root';
  $password = 'pwdpwd';
  $db = new PDO($dsn, $username, $password);
  $query = "SELECT p.poem_id, p.title, p.date_approved, 
  c.category, u.username
          FROM poems p
          JOIN categories c ON c.category_id = p.category_id
          JOIN users u ON u.user_id = p.user_id
          WHERE p.date_approved IS NOT NULL
          ORDER BY p.date_approved DESC
          LIMIT 0, 3";
  $stmt = $db->prepare($query);
  $stmt->execute();
?>
<main>
  <h1>Latest Poems</h1>
  <table>
    <thead>
      <tr>
        <th>Poem</th>
        <th>Category</th>
        <th>Author</th>
        <th>Published</th>
      </tr>
    </thead>
    <tbody>
      <?php
        while ($row = $stmt->fetch()) { 
          $approved = strtotime($row['date_approved']);
          $published = date('m/d/Y', $approved);
      ?>
        <tr>
          <td>
            <a href="poem.php?poem-id=<?= $row['poem_id'] ?>">
              <?= $row['title'] ?>
            </a>
          </td>
          <td><?= $row['category'] ?></td>
          <td><?= $row['username'] ?></td>
          <td><?= $published ?></td>
        </tr>
      <?php } ?>
    </tbody>
---- C O D E   O M I T T E D ----

Challenge Solution:

Database/Solutions/phppoetry.com/poems-with-count.php
<?php
  $pageTitle = 'Poems';
  require 'includes/header.php';

  $dsn = 'mysql:host=localhost;dbname=poetree';
  $username = 'root';
  $password = 'pwdpwd';
  $db = new PDO($dsn, $username, $password);
  $query = "SELECT p.poem_id, p.title, p.date_approved, 
  c.category, u.username
          FROM poems p
          JOIN categories c ON c.category_id = p.category_id
          JOIN users u ON u.user_id = p.user_id
          WHERE p.date_approved IS NOT NULL
          ORDER BY p.date_approved DESC";
  $stmt = $db->prepare($query);
  $stmt->execute();

  $qPoemCount = "SELECT COUNT(p.poem_id) AS num
  FROM poems p
    JOIN categories c ON c.category_id = p.category_id
    JOIN users u ON u.user_id = p.user_id
  WHERE p.date_approved IS NOT NULL";

  $stmtPoemCount = $db->prepare($qPoemCount);
  $stmtPoemCount->execute();
  $poemCount = $stmtPoemCount->fetch()['num'];
?>
<main id="poems">
  <h1><?= $pageTitle ?></h1>
  <table>
    <caption>Total Poems: <?= $poemCount ?></caption>
---- C O D E   O M I T T E D ----

Code Explanation

Note the following line of code:

$poemCount = $stmtPoemCount->fetch()['num'];

We could break that into two lines for clarity:

$poemCountRow = $stmtPoemCount->fetch();
$poemCount = $poemCountRow['num'];

But when a method returns an array that is only going to be used one time, it is common to chain the lookup onto the method.

Adding Pagination

Duration: 40 to 60 minutes.

In this exercise, you will limit the number of poems shown at one time and make it possible to paginate through the results with Previous and Next links.

  1. Open Database/Exercises/phppoetry.com/poems.php in your editor if it isn't still open. If you didn't do the challenge in the last exercise to get the correct poem count, you should replace your code with the code in Database/Solutions/phppoetry.com/poems-with-count.php.
  2. Notice that there are "Previous" and "Next" placeholders in table data cells within tfoot.
  3. Edit the page so that only two poems show up in the table. Normally, you would show a larger number (e.g., 10), but we will use two to demonstrate.
  4. Unless the last poem is already showing up, make the word "Next" link to the same page, but show the next two poems by passing a new offset value on the query string. You will need to create a new variable to hold the next offset value, which should equal the current offset value plus the number of rows being shown.
    1. If the last poem is already showing up, the word "Next" should not be linked and the table data cell should get the "disabled" class, like this:
      <td class="disabled">Next</td>
  5. Unless the first poem is already showing up, make the word "Previous" link to the same page, but show the previous two poems by passing a new offset value on the query string. You will need to create a new variable to hold the previous offset value, which should equal the current offset value minus the number of rows being shown.
    1. If the first poem is already showing up, the word "Previous" should not be linked and the table data cell should get the "disabled" class, like this:
      <td class="disabled">Previous</td>
  6. Visit http://localhost:8888/Webucator/php/Database/Exercises/phppoetry.com/poems.php when you are done. On first loading, the page should look like this:phppoetry.com poems.php with paginationNotice "Previous" is not linked and "Next" is.
  7. Clicking on the Next link should take you to poems.php?offset=2, which will show two new poems. On that page, both the Previous and Next links should be active.
  8. Clicking on the Previous link should take you to poems.php?offset=0. As we are again at the start of the poems, only the Next link should be active.
  9. Clicking Next several times should take you to the end, at which point only the Previous link should be active.

Look in Database/Exercises/phppoetry.com/sql.txt if you need help with the SQL query.

Solution:

Database/Solutions/phppoetry.com/poems-pagination.php
<?php
  $pageTitle = 'Poems';
  require 'includes/header.php';

  $offset = $_GET['offset'] ?? 0;
  $offset = (int) $offset; // So we can use === later in the code
  $rowsToShow = 2;
  $dsn = 'mysql:host=localhost;dbname=poetree';
  $username = 'root';
  $password = 'pwdpwd';
  $db = new PDO($dsn, $username, $password);
  $query = "SELECT p.poem_id, p.title, p.date_approved, 
  c.category, u.username
          FROM poems p
          JOIN categories c ON c.category_id = p.category_id
          JOIN users u ON u.user_id = p.user_id
          WHERE p.date_approved IS NOT NULL
          ORDER BY p.date_approved DESC
          LIMIT $offset, $rowsToShow";
  $stmt = $db->prepare($query);
  $stmt->execute();

  $qPoemCount = "SELECT COUNT(p.poem_id) AS num
  FROM poems p
    JOIN categories c ON c.category_id = p.category_id
    JOIN users u ON u.user_id = p.user_id
  WHERE p.date_approved IS NOT NULL";

  $stmtPoemCount = $db->prepare($qPoemCount);
  $stmtPoemCount->execute();
  $poemCount = $stmtPoemCount->fetch()['num'];

  $prevOffset = max($offset - $rowsToShow, 0);
  $nextOffset = $offset + $rowsToShow;

  $href = "poems-pagination.php?"; // Will be poems.php? for you.
  $prev = $href . "offset=$prevOffset";
  $next = $href . "offset=$nextOffset";
?>
<main id="poems">
  <h1><?= $pageTitle ?></h1>
  <table>
---- C O D E   O M I T T E D ----
    <tfoot class="pagination">
      <tr>
        <?php 
          if ($offset === 0) {
            echo "<td class='disabled'>Previous</td>";
          } else {
            echo "<td><a href='$prev'>Previous</a></td>";
          }
        ?>
        <td colspan="2"></td>
        <?php 
          if ($nextOffset >= $poemCount) {
            echo "<td class='disabled'>Next</td>";
          } else {
            echo "<td><a href='$next'>Next</a></td>";
          }
        ?>
      </tr>
    </tfoot>
  </table>
---- C O D E   O M I T T E D ----

Code Explanation

Things to notice:

  1. We use the null coalescing operator to set $offset to the value of the offset parameter on the query string, or to 0 if that offset parameter is not set.
  2. We cast $offset to an integer as values coming in on the query string are always strings.
  3. We set $rowsToShow to 2. Try changing that to see how it works with different numbers of rows.
  4. We add a LIMIT clause to our SELECT statement using $offset and $rowsToShow.
  5. We set values for $prevOffset and $nextOffset and use them to create the $prev and $next variables, which hold the URLs for the Previous and Next links.
  6. If the current value of $offset is 0 then we disable the Previous link.
  7. If the value of $nextOffset is greater than the number of poems ($poemCount) then we disable the Next link.

Sorting

Duration: 45 to 75 minutes.

In this exercise, you will make the table sortable by turning the four table headers (Poem, Category, Author, and Published) into links. When the user clicks one of these headings, the results should sort on that header. The next time the user clicks a header, the results should sort in reverse order.

Complexity Warning

This exercise is much more complex than anything we have done thus far, but it doesn't require any knowledge of PHP code beyond what you have learned. Take your time and move through each step slowly. If you get stuck, you are welcome to peek at the solution for help.

  1. Open Database/Exercises/phppoetry.com/poems.php in your editor if it isn't still open.
  2. You are going to have to use variables in the ORDER BY clause of the SQL query for both the field you want to sort on and the direction of the sort (i.e., 'asc' or 'desc'). Name those variables $order and $dir. Values for these can be passed in on the query string (call those URL parameters order and dir), but they might not be. You should start by writing code that:
    1. Sets $order to the value of the order URL parameter if it is set, and otherwise sets it to 'date_approved'.
    2. Sets $dir to the value of the dir URL parameter if it is set, and otherwise sets it to 'desc'.
  3. Next, change the ORDER BY clause to use the new $order and $dir variables.
  4. When users click the Previous and Next links, you will need to keep track of the sorting, so, in addition to the offset parameter, which is already being passed, pass values for the order and dir parameters on the query string for $prev and $next.
  5. Next you will need to construct the links for the headers. First consider the logic. If the poems are already sorted by date_approved asc and the user clicks the Published heading, you want to switch the sort to date_approved desc. But if the user clicks any other header, you will sort in ascending order by that header. So, the first step is to set variables for the direction for each of the four headers with a default of 'asc'. Good variable names would be $dirTitle, $dirCategory, etc.
  6. If the last sort was done in descending order, then the next sort will be in ascending order, no matter which header is clicked. But if the last sort was done in ascending order, then the next sort will be done in descending order if the same header is clicked again. So, you need to check if the last sort was done in ascending order. If it was, you need to find out which header was clicked and change the value of the direction variable for that header (e.g., $dirTitle) to 'desc'.
  7. Next you need to construct the href values for the headers and assign them to variables. Good variable names would be $linkTitle, $linkCategory, etc. Remember that you already have declared a $href variable to hold "poems.php?" You just need to append the query string on to that to pass the order field and direction for each header.
  8. Finally, you need to turn the headers into links using the href values you constructed in the last step.
  9. Visit http://localhost:8888/Webucator/php/Database/Exercises/phppoetry.com/poems.php when you are done to test your solution.

Solution:

Database/Solutions/phppoetry.com/poems-sorting.php
---- C O D E   O M I T T E D ----
  // Set defaults for $order and $dir
  $order = $_GET['order'] ?? 'date_approved';
  $dir = $_GET['dir'] ?? 'desc';
  $query = "SELECT p.poem_id, p.title, p.date_approved, 
  c.category, u.username
          FROM poems p
          JOIN categories c ON c.category_id = p.category_id
          JOIN users u ON u.user_id = p.user_id
          WHERE p.date_approved IS NOT NULL
          ORDER BY  $order $dir
          LIMIT $offset, $rowsToShow";
  $stmt = $db->prepare($query);
  $stmt->execute();
---- C O D E   O M I T T E D ----
  $href = "poems-sorting.php?"; // Will be poems.php? for you.
  $prev = $href . "offset=$prevOffset&order=$order&dir=$dir";
  $next = $href . "offset=$nextOffset&order=$order&dir=$dir";

  /* CONSTRUCT THE LINKS FOR THE HEADERS */

  // Default all directions to ascending
  $dirTitle = 'asc';
  $dirCategory = 'asc';
  $dirUsername = 'asc';
  $dirPublished = 'asc';

  // If the current direction is 'asc', switch the direction
  //  for the header that is currently being sorted on
  if ($dir === 'asc') {
    switch ($order) {
      case 'title':
        $dirTitle = 'desc';
        break;
      case 'category':
        $dirCategory = 'desc';
        break;
      case 'username':
        $dirUsername = 'desc';
        break;
      case 'date_approved':
        $dirPublished = 'desc';
        break;
    }
  }

  $titleLink = $href . "order=title&dir=$dirTitle";
  $categoryLink = $href . "order=category&dir=$dirCategory";
  $usernameLink = $href . "order=username&dir=$dirUsername";
  $publishedLink = $href . "order=date_approved&dir=$dirPublished";
?>
<main id="poems">
  <h1><?= $pageTitle ?></h1>
  <table>
    <caption>Total Poems: <?= $poemCount ?></caption>
    <thead>
      <tr>
        <th>
          <a href="<?= $titleLink ?>">Poem</a>
        </th>
        <th>
          <a href="<?= $categoryLink ?>">Category</a>
        </th>
        <th>
          <a href="<?= $usernameLink ?>">Author</a>
        </th>
        <th>
          <a href="<?= $publishedLink ?>">Published</a>
        </th>
      </tr>
    </thead>
---- C O D E   O M I T T E D ----

Anticipating Foul Play

Web developers always need to be aware that users won't always do what they expect them to do, and sometimes they will intentionally try to break things. One method of doing that is to pass in unexpected URL or POST parameters. We can prevent this by creating an array of acceptable values and checking to make sure the passed-in value is one of those values. Examine the code below:

Code Sample:

Database/Solutions/phppoetry.com/poems-sorting-2.php
---- C O D E   O M I T T E D ----
  $order = $_GET['order'] ?? 'date_approved';
  $orderAllowed = ['date_approved',
                  'title',
                  'category',
                  'username'];
  if (!in_array($order, $orderAllowed)) {
    $order = 'date_approved';
  }
  
  $dir = $_GET['dir'] ?? 'desc';
  $dirAllowed = ['asc', 'desc'];
  if (!in_array($dir, $dirAllowed)) {
    $dir = 'asc';
  }
---- C O D E   O M I T T E D ----

Code Explanation

By adding the code shown above checking that the passed-in values on the order and dir URL parameters are in our list of acceptable values, we ensure that the values of $order and $dir are valid. Take a moment to add this code into your poems.php file.

Filtering

Duration: 45 to 75 minutes.

In this exercise, you will add filtering to the results.

  1. Open Database/Exercises/phppoetry.com/poems.php in your editor if it isn't still open.
  2. We will add the ability to filter on two fields: category and username. The first step is to populate the form correctly. The form will submit to the same page (poems.php) using the GET method, so the form name-value pairs will be passed on the query string, just as occurs with the Previous and Next links.
    1. We must remember the order and dir values, which may have changed from the defaults based on any sorting the user has done. Add two hidden fields to the form that hold those values.
    2. Currently, the options for the cat (category) and user (username) select fields are hard coded. Those should be populated based on data from the database so that if the categories change or if a new user publishes a poem, the form reflects that. Write code to retrieve from the database:
      1. The categories. category ids, and number of published poems in that category.
      2. The usernames and user ids of people who have published poems, and the number of poems each user has published.
    3. Use those results to dynamically populate the cat and user options.
    4. If the form had previously been submitted, then you want the category and/or username that was submitted the previous time to be pre-selected. Add the selected attribute to the category and/or username options that were submitted via the form.
  3. Before moving on to the next step, test this to make sure it's working by visiting http://localhost:8888/Webucator/php/Database/Exercises/phppoetry.com/poems.php.
    1. The categories and username fields should be populated. View the source of the page. The form code should look something like this:
      <form method="get" action="poems-filtering.php">
        <input type="hidden" name="order" value="date_approved">
        <input type="hidden" name="dir" value="desc">
        <label for="cat">Category:</label>
        <select name="cat" id="cat">
          <option value="0">All</option>
          <option value='2'>Funny (4)</option>
          <option value='1'>Romantic (2)</option>
          <option value='4'>Serious (1)</option>
        </select>
        <label for="user">Author:</label>
        <select name="user" id="user">
          <option value="0">All</option>
          <option value='3'>Dawnable (1)</option>
          <option value='2'>HugHerHeart (2)</option>
          <option value='1'>LimerickMan (5)</option>
        </select>
        <button name="filter" class="wide">Filter</button>
      </form>
      Don't worry if the format of your code is a little messy. Dynamically generated code is generally messy.
    2. Select a category and submit. The records won't change yet, but the category you selected should still be selected. If it is not, go back to your code and fix it.
    3. Select a username and submit. The records won't change yet, but the username you selected should still be selected. If it is not, go back to your code and fix it.
  4. Next, we need to modify the WHERE clause of the poems query to take into account the filtering.
    1. Break $query into two parts like this:
      $query = "SELECT p.poem_id, p.title, p.date_approved, 
      c.category, u.username
        FROM poems p
        JOIN categories c ON c.category_id = p.category_id
        JOIN users u ON u.user_id = p.user_id
        WHERE p.date_approved IS NOT NULL";
      
      /*
        You will add code here to append to the WHERE clause
        if a category and/or username has been selected.
      */
      
      // Concatenate on the rest of the query
      $query .= " ORDER BY  $order $dir
        LIMIT $offset, $rowsToShow";
    2. If the user selected a category and/or username, we need to get the selected category ID and/or selected user id. Declare two variables: $selCatId and $selUserId, and set them to the values of the cat and user parameters passed on the query string, if they are passed, or to 0 if they are not passed. Hint: this is a good opportunity to use the null coalescing operator.
    3. We now need to create the additional conditions to append to the WHERE clause. We will do this by creating two arrays: $whereConditions and $params. The items in $whereConditions will be strings structured like this: "field_name = ?". The items in $params will be the values that will replace the question mark placeholders in the where conditions.
      1. Create a new variable called $whereConditions and assign it an empty array.
      2. Create a new variable called $params and assign it an empty array.
      3. If a category was selected (i.e., $selCatId is not 0), append "c.category_id = ?" to $whereConditions and append $selCatId to $params.
      4. If a username was selected (i.e., $selUserId is not 0), append "u.user_id = ?" to $whereConditions and append $selUserId to $params.
      5. Now, if and only if $whereConditions contains items, we need to append the where conditions on to the end of the WHERE clause. To do that, we need to join them on the string " AND " and then concatenate the result to $query right before the ORDER BY clause is concatenated on. To do this, use the implode() function. If you need help with this part, open Database/Demos/where-conditions.php in your editor and study it. Then open the same file in your browser to see the output.
      6. When executing the statement with this query, pass in $params.
      7. Finally, you will need to add the additional where conditions to $qPoemCount as well so that the poem count is correct. Do this in the same way. Note that you do not need to recreate the $whereConditions and $params arrays. Reuse the ones you have already created.
  5. Visit http://localhost:8888/Webucator/php/Database/Exercises/phppoetry.com/poems.php when you are done to test your solution.

Look in Database/Exercises/phppoetry.com/sql.txt if you need help with the SQL query.

Solution:

Database/Solutions/phppoetry.com/poems-filtering.php
---- C O D E   O M I T T E D ----
  $query = "SELECT p.poem_id, p.title, p.date_approved, 
  c.category, u.username
          FROM poems p
          JOIN categories c ON c.category_id = p.category_id
          JOIN users u ON u.user_id = p.user_id
          WHERE p.date_approved IS NOT NULL";


  $selCatId = $_GET['cat'] ?? 0; // category_id
  $selUserId = $_GET['user'] ?? 0; // user_id
  $whereConditions = [];
  $params = [];

  if ($selCatId) {
    $whereConditions[] = "c.category_id = ?";
    $params[] = $selCatId;
  }
  
  if ($selUserId) {
    $whereConditions[] = "u.user_id = ?";
    $params[] = $selUserId;
  }

  if ($whereConditions) {
    $where = implode($whereConditions, ' AND ');
    $query .= ' AND ' . $where;
  }

  $query .= " ORDER BY  $order $dir
          LIMIT $offset, $rowsToShow";

  $stmt = $db->prepare($query);
  $stmt->execute($params);

  $qPoemCount = "SELECT COUNT(p.poem_id) AS num
  FROM poems p
    JOIN categories c ON c.category_id = p.category_id
    JOIN users u ON u.user_id = p.user_id
  WHERE p.date_approved IS NOT NULL";

  if ($whereConditions) {
    $where = implode($whereConditions, ' AND ');
    $qPoemCount .= ' AND ' . $where;
  }

  $stmtPoemCount = $db->prepare($qPoemCount);
  $stmtPoemCount->execute($params);
  $poemCount = $stmtPoemCount->fetch()['num'];

  $prevOffset = max($offset - $rowsToShow, 0);
  $nextOffset = $offset + $rowsToShow;

  $qCategories = "SELECT c.category_id, c.category,
    COUNT(p.poem_id) AS num_poems
  FROM categories c
    JOIN poems p ON c.category_id = p.category_id
  WHERE p.date_approved IS NOT NULL
  GROUP BY c.category_id
  ORDER BY c.category";

  $stmtCats = $db->prepare($qCategories);
  $stmtCats->execute();

  $qUsers = "SELECT u.user_id, u.username, 
    COUNT(p.poem_id) AS num_poems
  FROM users u
    JOIN poems p ON u.user_id = p.user_id
  WHERE p.date_approved IS NOT NULL
  GROUP BY u.user_id
  ORDER BY u.username";

  $stmtUsers = $db->prepare($qUsers);
  $stmtUsers->execute();

               $href = "poems-filtering.php?cat=$selCatId&user=$selUserId&";
  // "poems.php?cat=$selCatId&user=$selUserId&" for you.

---- C O D E   O M I T T E D ----
  <h2>Filtering</h2>
  <!--The form action will be poems.php for you.-->
  <form method="get" action="poems-filtering.php">
    <input type="hidden" name="order" value="<?= $order ?>">
    <input type="hidden" name="dir" value="<?= $dir ?>">
    <label for="cat">Category:</label>
    <select name="cat" id="cat">
      <option value="0">All</option>
      <?php
        while ($row = $stmtCats->fetch()) {
          $category = $row['category'];
          $numPoems = $row['num_poems'];
          $categoryId = $row['category_id'];
          $selected = $categoryId === $selCatId ? 'selected' : '';
          echo "<option value='$categoryId' $selected>
            $category ($numPoems)
          </option>";
        }
      ?>
    </select>
    <label for="user">Author:</label>
    <select name="user" id="user">
      <option value="0">All</option>
      <?php
        while ($row = $stmtUsers->fetch()) {
          $username = $row['username'];
          $userId = $row['user_id'];
          $numPoems = $row['num_poems'];
          $selected = $userId === $selUserId ? 'selected' : '';
          echo "<option value='$userId' $selected>
            $username ($numPoems)
          </option>";
        }
      ?>
    </select>
    <button name="filter" class="wide">Filter</button>
  </form>
</main>
<?php
  require 'includes/footer.php';
?>

Code Explanation

When reviewing this code, you may find it helpful to go back through the exercise instructions and match up each instruction with the relevant solution code.

Adding Filtering Links to the Single Poem Page

Duration: 20 to 30 minutes.

Currently, on poem.php, there are a couple of links under the poem that don't go anywhere and have static text that isn't always relevant:

<li>
  <i class="fas fa-circle"></i>
  <a href="#">More Funny Poems</a>
</li>
<li>
  <i class="fas fa-circle"></i>
  <a href="#">More Poems by LimerickMan</a>
</li>

In this exercise, you will make these links work and be related to the poem on the page.

  1. Open Database/Exercises/phppoetry.com/poem.php in your editor.
  2. Modify the query so that:
    1. It gets the user_id from the users table.
    2. It joins on the categories table as well and gets the category_id and the category fields from that table.
  3. Using the user_id, category_id, and category returned from the query, edit the page so that the links below the poem have text relevant to the poem and work correctly.
  4. Visit http://localhost:8888/Webucator/php/Database/Exercises/phppoetry.com/poems.php to test your solution. Click on poems in different categories or by different authors. Check the links below the poem to make sure they update correctly.

Look in Database/Exercises/phppoetry.com/sql.txt if you need help with the SQL query.

Solution:

Database/Solutions/phppoetry.com/poem-2.php
---- C O D E   O M I T T E D ----
  $query = "SELECT u.username, u.user_id,
    p.title, p.poem, p.date_submitted, p.date_approved,
    c.category_id, c.category
    FROM users u
      JOIN poems p ON u.user_id = p.user_id
      JOIN categories c ON c.category_id = p.category_id
    WHERE p.poem_id = ?";
  $stmt = $db->prepare($query);
  $stmt->execute([$poemId]);
  $row = $stmt->fetch();
  
  if ($row) {
    $title = $row['title'];
    $authorUserId = $row['user_id'];
    $authorUserName = $row['username'];
    $dateSubmitted = $row['date_submitted'];
    $dateApproved = $row['date_approved'];
    $poem = $row['poem'];
    $categoryId = $row['category_id'];
    $category = $row['category'];
  } else {
    $title = 'Poem Not Found';
  }
---- C O D E   O M I T T E D ----
      <?php if ($row) { ?>
        <li>
          <i class="fas fa-circle"></i>
          <a href="poems.php?cat=<?= $categoryId ?>">
            More <?= $category ?> Poems
          </a>
        </li>
        <li>
          <i class="fas fa-circle"></i>
          <a href="poems.php?user=<?= $authorUserId ?>">
            More Poems by <?= $authorUserName ?>
          </a>
        </li>
      <?php } ?>
---- C O D E   O M I T T E D ----