Drilling down

Where are we?

You know how to create a listing of data, like the DogRocks’ article list. But each listing only shows the title of the article, not the article itself.

This lesson’s goals

In this lesson, you will learn that:

  • Drilling down means going from summary to detail.
  • On a summary page, use PHP to create links that include a primary key value.
  • On a detail page, get the primary key value from the link, and use it to fetch detail data from the database.

Getting the details

“Drilling down” means to get more detailed information, to go from a summary to detail.

Here’s the article list from DogRock:

Article list

Figure 1. Article list, from articles.php

The reader gets a little information on what each article is about: the title. If the reader wants more, s/he can click on the title of the article. For example, clicking on the first one shows:

An article

Figure 2. An article

How does this work? Here’s some of the HTML that creates Figure 1, the article list:

<tr> 
  <td><a href='show-article.php?id=2'>A new howl on the prowl</a></td> 
  <td>February 5, 2010</td> 
  <td>Lumis</td> 
</tr> 
<tr> 
  <td><a href='show-article.php?id=1'>Somebody let them out!</a></td> 
  <td>February 3, 2010</td> 
  <td>Bounder</td> 
</tr> 

Figure 3. HTML generated by articles.php

Lines 1 to 5 show a table row for an article. You can see the link to the article on line 2:

<a href='show-article.php?id=2'>

show-article.php will show an article, if you send it the article’s id. If you want a different article, send it a different id.

Here’s a picture of what’s happening:

Link to article

Figure 4. Link to article

You can see the HTML that makes the link. The user clicks on the link. The browser jumps to show-article.php, passing id of the article to show.

What are the ids? They’re from the database:

articles table

Figure 5. articles table

The article ids are the primary key values.

So we need to do two things to get this working:

  • Write PHP for articles.php, to create links to the articles, links like <a href='show-article.php?id=2'>.
  • Write show-article.php, so it will take an id, and show the article with that id.

Let’s look at each piece.

The article list

Here’s code from articles.php. I’ve omitted error control and sorting.

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Create the query.
$query = "select article_id, title, author, when_published
    from articles";
//Run the query.
$record_set = $db->query($query);
//Start the output table.
...
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for an article.
  $article_id = $row['article_id'];
  $title = $row['title'];
  $when_published = $row['when_published'];
  $author = $row['author'];
  //Format the date.
  $when_published = date('F j, Y', strtotime($when_published));
  //Output
  print "
    <tr>
      <td><a href='show-article.php?id=$article_id'>$title</a></td>
      <td>$when_published</td>
      <td>$author</td>
    </tr>";
} //end while
?>

Figure 6. articles.php

Lines 3 and 4 connect to the MySQL database.

Lines 6 to 9 create and run the MySQL query. Here it is:

select article_id, title, author, when_published from articles

The query retrieves article_id, because we’ll need it to make the link to show-article.php. show-article.php needs to know which article to show.

Here’s the data the query might fetch:

Article data

Figure 7. Article data

The while() loop (lines 13 t0 28) works as before. It grabs the fields from each row, including each record’s article_id and title. They’re put in the variables $article_id and $title (lines 15 and 16).

The program outputs the link to each article with line 24:

<a href='show-article.php?id=$article_id'>$title</a>

If $article_id is 2 and $title is A new howl on the prowl (from the first record in Figure 7), then we get:

<td><a href='show-article.php?id=2'>A new howl on the prowl</a></td>

W00f! We have the article list.

Showing an article

How do we show the article? Here’s show-article.php, with error checking omitted.

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Fetch article data.
$article_id = $_GET['id'];
$query = "select title, body, when_published, author
    from articles
    where article_id = " . $article_id;
$record_set = $db->query($query);
$row = $record_set->fetch_assoc();
//Get fields.
$title = $row['title'];
$body = $row['body'];
$when_published = $row['when_published'];
$author = $row['author'];
//Format the date.
$when_published = date('F j, Y', strtotime($when_published));
//For the body, change new lines into <br> tags.
$body = str_replace("\n", "<br>", $body);
//Output.
print "
  <h2>$title</h2>
  <p>By $author</p>
  <p>$when_published</p>
  <p>$body</p>
";
?>

Figure 8. show-article.php

Line 6 gets the id of the article from the URL.

Lines 7 to 9 use the id to create an SQL statement to fetch the article with that id. It will fetch just one article. Remember that article_id is the primary key of the article table, so each row in the table will have a different value for article_id.

Line 10 runs the query, getting a record set back from MySQL. It puts the record set into the variable $record_set.

Line 11 takes the row from the record set:

$row = $record_set->fetch_assoc();

There is only one row, so no loop is needed.

Lines 13 to 16 extract the fields from the $row. Line 18 formats the date.

Renata
Renata

I don’t see the article id there. Why don’t you have something like this?

$article_id = $row['article_id'];

Kieran
Kieran

article_id was passed into the page. The page got it on line 6:

$article_id = $_GET['id'];

So there was no need to fetch it again from the database.

Lines 20 is something new. The body field of the articles table contains the main text of the article. When a writer types it in, s/he might do something like this:

Typing in an article

Figure 9. Typing in an article

The writer added a blank line between the two paragraphs. It gets stored this way my MySQL:

Article body shown by phpMyAdmin

Figure 10. Article body shown by phpMyAdmin

But, as you know, browsers don’t render whitespace in HTML. And an empty line is whitespace.

Take this:

<blockquote>
  <p>
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.
  </p>
</blockquote>

Figure 11. Empty line in body

It will render as:

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.

Figure 12. Empty line in body, rendered

The two paragraphs are smooshed together. There isn’t any whitespace between them, because browsers don’t render whitespace in HTML.

How can we make sure that the empty lines in the body are rendered by the browser?

Here’s one way, from line 20 of the code:

$body = str_replace("\n", "<br>", $body);

\n stands for the “new line” character. It’s the character that your keyboard makes when you press the Enter key. The str_replace() function goes through $body, and replaces all of the \ns with <br>s. As you, the <br> renders as a line break.

By the way, it’s important to use double quotes – "\n" – rather than single quotes – '\n'. PHP will only interpret the \n as the new line character when it’s in double quotes.

Here’s what $body looks like before the str_replace():

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.

It contains two \ns, one at the end of line 1, and other at the end of line 2. They’re might have been one at the end of line 3, but the writer didn’t type it.

Here’s what $body looks like after the substitution:

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.
<br> 
<br>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.

The two <br>s make the browser show the empty line. W00f!

Here’s the rest of the code from show-article.php:

//Output.
print "
  <h2>$title</h2>
  <p>By $author</p>
  <p>$when_published</p>
  <p>$body</p>
";

Figure 13. Part of show-article.php

It outputs the article data.

Exercise: Drilling down to jokes

Change jokes.php so that each joke title is a link to the page show-joke.php. This page shows all of the details of a joke. You can see the page in action on my sample site.

Do the same for the home page.

You can see my code for:

But don’t look at them now! Do it yourself first.

(Log in to enter your solution to this exercise.)

Summary

You learned:

  • Drilling down means going from summary to detail.
  • On a summary page, use PHP to create links that include a primary key value.
  • On a detail page, get the primary key value from the link, and use it to fetch detail data from the database.

How to...

Lessons

User login

Log in problems? Try here


Dogs