Showing data

See more about:

Where are we?

You know how to add data to a table. Now let’s see how you can show it.

This lesson’s goals

In this lesson, you learn that:

  • The SQL SELECT statement returns a record set. A record set has one or more rows of data.
  • You can loop across a record set, extracting each row. From each row, you extract the fields in the row, and use them to create some HTML.

Showing the DogToys product catalog

The goal

We want to make something like this:

Product catalog

Figure 1. Product catalog

The HTML uses the <table> tag. There’s a row for each product.

<Start table detour>

Let’s have a quick review of <table>s. Suppose we want to make a table like this:

Rendered table

Figure 2. Rendered table

Here’s the HTML code:

<table>
  <thead>
    <tr>
      <th>Food</th>
      <th>Rating</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Bacon</td>
      <td>Yum!</td>
    </tr>
    <tr>
      <td>Brussel sprouts</td>
      <td>Yuck!</td>
    </tr>
  </tbody>
</table>

Figure 3. The <table> tag

The whole thing is wrapped in <table>.

The <tr> tag makes a row. The <td> makes a cell in a row. The “d” in <td> stands for “data.”

<th> makes a cell as well, but with some special formating that’s good for column headers (hence the “h” in <th>).

The <thead> and <tbody> tags separate the table into two regions, header and body. The tags help with styling. For example, you can easily give different background colors to the header and body of the table.

To get the spacing in Figure 2, use the following CSS:

table {
  border-spacing: 10px;
}
td {
  padding: 10px;
}

Figure 4. CSS for table spacing

This puts 10 pixels between each cell (line 2) and 10 pixels between the edges of each cell and its content (line 5).

That’s all you need to know about tables for now. You can read the table chapter if you want to know more.

</ End table detour>

So our PHP will output HTML code with the table tag. Here’s the rendered product page:

Product catalog

Figure 1 (again). Product catalog

Here’s the HTML that will make it.

<tr> 
  <td> 
    <img class='product_image' src='./product-images/ball.jpg'> 
  </td> 
  <td class='product_name_description_container'> 
    <p class='product_name'>Squeaky ball</p> 
    <p class='product_description'>Bouncing, squeaking fun!</p> 
  </td> 
  <td class='product_price'>1.99</td> 
</tr> 
<tr> 
  <td> 
    <img class='product_image' src='./product-images/frisbee.jpg'> 
  </td> 
  <td class='product_name_description_container'> 
    <p class='product_name'>Frisbee</p> 
    <p class='product_description'>A whirling disk of pure goodness! From <a href="http://www.wham-o.com/">Wham-O</a>.</p> 
  </td> 
  <td class='product_price'>10.95</td> 
</tr> 
<tr> 
  <td> 
    <img class='product_image' src='./product-images/rope.jpg'> 
  </td> 
  <td class='product_name_description_container'> 
    <p class='product_name'>Giant chew rope</p> 
    <p class='product_description'>Nom nom nom, and nom again. Chew this, not human shoes. You know how touchy they are.</p> 
  </td> 
  <td class='product_price'>12.95</td> 
</tr>

Figure 5. HTML created by PHP

The pattern

Here’s the pattern for PHP that will take data from a MySQL table, and make HTML to show it:

  • Create a SELECT statement. SELECT is the SQL command to grab data.
  • Execute the SQL, and get back a record set. A record set is the result of a SELECT. Could be one record, or five, or 1,289.
  • For each record in the record set:
    • Get the fields from the records.
    • Show the fields.

The code

Here’s the code that will generate the HTML to show the product list. To make the code easier to follow, I’ve omitted some stuff having to do with sorting. I’ll add it back in the next lesson.

I’ve also omitted error checking, e.g., checking that the database connection was successful. That would clutter things, making it harder for you to focus on the core ideas of this lesson.

Here’s the code. We’ll go through it piece by piece.

<?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 product_id, name, description, image_file_name, price
    from products";
//Run the query.
$record_set = $db->query($query);
//Start the output table.
?>
<table class="product_table">
  <thead>
    <tr>
      <th>&nbsp;</th>
      <th>Name</th>
      <th>Price</th>
    </tr>
  </thead>
  <tbody>
<?php
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for a product.
  $product_id = $row['product_id'];
  $name = $row['name'];
  $description = $row['description'];
  $image_file_name = $row['image_file_name'];
  $price = $row['price'];
  //Output
  print "
    <tr>
      <td>
        <img class='product_image' src='$path_to_root/product-images/$image_file_name'>
      </td>
      <td class='product_name_description_container'>
        <p class='product_name'>$name</p>
        <p class='product_description'>$description</p>
      </td>
      <td class='product_price'>$price</td>
    </tr>";
} //End while.
?>
  </tbody>
</table>

Figure 6. PHP to show the product catalog

Lines 3 and 4 open the database. As usual.

Lines 6 to 8 create the SQL query that fetches the data. I broke it across lines to make it easier for me to read. The statement has the form:

SELECT fields FROM table;

When you send this to MySQL:

select name, description, image_file_name, price from products;

You are telling MySQL:

Open up the products table, and, for each row, fetch name, description, image_file_name, and price.

Here’s what you get:

From MySQL

Figure 7. A record set from MySQL

Renata
Renata

Hmm, that looks like part of a phpMyAdmin screen.

Kieran
Kieran

It is. I copied the SQL query from my code, clicked the SQL tab in phpMyAdmin, and pasted in the query. Clicked the Go button, and MySQL ran it.

That’s a good way to test your own queries.

Once the query has been created, it gets run in line 10:

$record_set = $db->query($query);

query() returns a record set, that gets put into the variable $record_set. The record set is this:

From MySQL

Figure 7 (again). A record set from MySQL

It’s the results of running the query.

We’re used to a variable containing something simple, like a number or some text:

$x = 42;
$y = 'The answer!';

But $record_set is different. It contains a complex object.

We don’t care too much what an object really is. Just think of it as a blob of data. There can be all kinds of data in an object.

You need special functions to get at the data inside an object. We’ll see some in a moment.

OK, so we’ve:

  • Created an SQL query.
  • Sent it to MySQL.
  • Gotten an object back. The object is a record set, with rows and columns.

Now we need to show the records in a <table>. There’s some code in Figure 6 to output the <table> tag and the header:

<table class="product_table">
  <thead>
    <tr>
      <th>&nbsp;</th>
      <th>Name</th>
      <th>Price</th>
    </tr>
  </thead>
  <tbody>

Part of Figure 6. PHP to show the product catalog

Line 16 is a little strange.

<th>&nbsp;</th>

The first column in the output table has the item’s picture. It doesn’t need a heading. So line 16 creates an empty heading cell.

Here’s the next part of the code, where the meat is:

<?php
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for a product.
  $name = $row['name'];
  $description = $row['description'];
  $image_file_name = $row['image_file_name'];
  $price = $row['price'];
  //Output
  print "
    <tr>
      <td>
        <img class='product_image' src='$path_to_root/product-images/$image_file_name'>
      </td>
      <td class='product_name_description_container'>
        <p class='product_name'>$name</p>
        <p class='product_description'>$description</p>
      </td>
      <td class='product_price'>$price</td>
    </tr>";
} //End while.
?>

Part of Figure 6. PHP to show the product catalog

while is a new PHP statement for us. It’s a loop. It executes the same PHP statements a bunch of times.

Its format is:

while( test ) {
  Do this
}

It says:

Keep doing “Do this” while the test is true.

Here’s an example:

<?php
$x = 1;
while ( $x <= 5 ) {
  print "$x<br>";
  $x += 1;
}
?>

Figure 8. while loop example

This says:

While $x is less than or equal to 5:
     Output $x and a line break tag.
     Add 1 to $x.

The PHP engine will keep doing lines 4 and 5 while the condition is true.

Line 5 is a common PHP shortcut. += means “add to.” So:

$x += 1;

means “Add 1 to what is already in $x.”

Here’s the HTML the code generates:

1<br>2<br>3<br>4<br>5<br>

Here’s how it renders:

Rendered output

Figure 9. Rendered output

Let’s have another look at the loop in our database program:

while( $row = $record_set->fetch_assoc() ) {
     Stuff to do
}

The PHP engine will keep looping around, doing “Stuff to do” while $row = $record_set->fetch_assoc() is true. When $row = $record_set->fetch_assoc() stops being true (when it is false), the PHP engine stops the loop. It will skip to the code after the loop.

Let’s look at:

$record_set->fetch_assoc()

Recall that $record_set is a record set object. It contains this data:

From MySQL

Figure 7 (again). A record set from MySQL

fetch_assoc() is a function that returns one row.

When a function belongs to an object, it’s really called a “method.” But let’s keep calling it a function for simplicity.

A record set object has other data besides some rows. It also has a record pointer:

Record pointer

Figure 10. Record pointer

The record pointer starts off pointing to the first record.

When you use the fetch_assoc() function, you are saying two things:

  • Give me the current record (the one the pointer points to).
  • Move the pointer down one record.

What does “Give me the current record” mean? Here’s the code again:

$row = $record_set->fetch_assoc()

fetch_assoc() copies $record_set’s current record into $row. fetch_assoc() doesn’t remove the data from $record_set. It’s still there. fetch_assoc() copies the row, and sends it to $row.

Then fetch_assoc() moves the pointer down. So after fetch_assoc() has run once, we would have:

<code>fetch_assoc()</code> has run once

Figure 11. fetch_assoc() has run once

fetch_assoc() has returned the record it was pointing to, and moved the pointer down one record.

Run fetch_assoc() a second time, and you get:

<code>fetch_assoc()</code> has run twice

Figure 12. fetch_assoc() has run twice

Run fetch_assoc() a third time, and you get:

<code>fetch_assoc()</code> has run three times

Figure 13. fetch_assoc() has run three times

The record pointer has moved beyond the end of the table.

Run fetch_assoc() a fourth time, and you get:

<code>fetch_assoc()</code> has run four times

Figure 14. fetch_assoc() has run four times

The record pointer is pointing to an empty space, so $row = $record_set->fetch_assoc() puts NULL into $row. NULL acts like FALSE in PHP’s if(), while(), and other statements.

Here’s the loop again:

while( $row = $record_set->fetch_assoc() ) {
     Stuff to do
}

This will do “Stuff to do” again and again. The first time, $row will have the first record. The second time, $row will have the second record. And so on.

The last time, $row will have the last record. When the while() tries to run again, fetch_assoc() will return NULL, which acts like FALSE. So the loop will stop. Execution will pick up after the brace (}) that ends the loop.

What we have now is a way to grab each record in the record set. Each one gets put into $row. But what do we do with $row?

Remember our goal. We want to create a <table> that contains rows like this:

<tr> 
  <td> 
    <img class='product_image' src='./product-images/ball.jpg'> 
  </td> 
  <td class='product_name_description_container'> 
    <p class='product_name'>Squeaky ball</p> 
    <p class='product_description'>Bouncing, squeaking fun!</p> 
  </td> 
  <td class='product_price'>1.99</td> 
</tr> 
<tr> 
  <td> 
    <img class='product_image' src='./product-images/frisbee.jpg'> 
  </td> 
  <td class='product_name_description_container'> 
    <p class='product_name'>Frisbee</p> 
    <p class='product_description'>A whirling disk of pure goodness! From <a href="http://www.wham-o.com/">Wham-O</a>.</p> 
  </td> 
  <td class='product_price'>10.95</td> 
</tr> 
<tr> 
  <td> 
    <img class='product_image' src='./product-images/rope.jpg'> 
  </td> 
  <td class='product_name_description_container'> 
    <p class='product_name'>Giant chew rope</p> 
    <p class='product_description'>Nom nom nom, and nom again. Chew this, not human shoes. You know how touchy they are.</p> 
  </td> 
  <td class='product_price'>12.95</td> 
</tr>

Figure 5 (again). HTML created by PHP

Lines 1 to 10 are for the first product. They were created from the first row in the record set:

From MySQL

Figure 7 (again). A record set from MySQL

The second row in the table was created from the second row in the record set.

Here’s the entire loop again:

while( $row = $record_set->fetch_assoc() ) {
  //Get fields for a product.
  $name = $row['name'];
  $description = $row['description'];
  $image_file_name = $row['image_file_name'];
  $price = $row['price'];
  //Output
  print "
    <tr>
      <td>
        <img class='product_image' src='$path_to_root/product-images/$image_file_name'>
      </td>
      <td class='product_name_description_container'>
        <p class='product_name'>$name</p>
        <p class='product_description'>$description</p>
      </td>
      <td class='product_price'>$price</td>
    </tr>";
} //End while.

Part of Figure 6 (again). PHP to show the product catalog

This from line 24:

$row = $record_set->fetch_assoc()

puts a row from the record set into $row.

Remember that $record_set doesn’t have a single value, like 7 or bark. It’s an object, with a bunch of data.

$row doesn’t contain a single value, either. But it isn’t a record set object. It’s an array. We don’t care right now about arrays. Just know that an array contains a bunch of values that you can access.

Remember the SQL query used to create the record set:

select name, description, image_file_name, price from products

So record (row) in the record set has the fields name, description, image_file_name, and price. When:

$row = $record_set->fetch_assoc()

puts the current record in $row, it puts four values into $row.

Here are the next few lines of code:


//Get fields for a product.
$name = $row[‘name’];
$description = $row[‘description’];
$image_file_name = $row[‘image_file_name’];
$price = $row[‘price’];

You can access array elements like this:

$array_name['element name']

So…

$name = $row['name'];

... gets the name element from $row and puts it into the variable $name. $name is not a complex object. It’s just a variable with a simple string in it.

We end up with four variables containing the name, description, image file name, and price for the current record.

Then we can output the variables:

//Output
print "
  <tr>
    <td>
      <img class='product_image' src='$path_to_root/product-images/$image_file_name'>
    </td>
    <td class='product_name_description_container'>
      <p class='product_name'>$name</p>
      <p class='product_description'>$description</p>
    </td>
    <td class='product_price'>$price</td>
  </tr>";

Part of Figure 6 (again). PHP to show the product catalog

This PHP statement outputs the fields, inserting them into some HTML, to create a table row.

Here’s all the code again:

<?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 product_id, name, description, image_file_name, price
    from products";
//Run the query.
$record_set = $db->query($query);
//Start the output table.
?>
<table class="product_table">
  <thead>
    <tr>
      <th>&nbsp;</th>
      <th>Name</th>
      <th>Price</th>
    </tr>
  </thead>
  <tbody>
<?php
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for a product.
  $product_id = $row['product_id'];
  $name = $row['name'];
  $description = $row['description'];
  $image_file_name = $row['image_file_name'];
  $price = $row['price'];
  //Output
  print "
    <tr>
      <td>
        <img class='product_image' src='$path_to_root/product-images/$image_file_name'>
      </td>
      <td class='product_name_description_container'>
        <p class='product_name'>$name</p>
        <p class='product_description'>$description</p>
      </td>
      <td class='product_price'>$price</td>
    </tr>";
} //End while.
?>
  </tbody>
</table>

Figure 6 (again). PHP to show the product catalog

You should be able to follow it now.

Lines 3 and 4 connect to the database.

Lines 6 to 10 run a SELECT query, fetching a record set from MySQL.

Lines 13 to 21 output the table header.

Lines 24 to 43 loop across the record set, showing a table row for each product.

Lines 45 and 46 close the table’s HTML.

W00f!

That’s a lot of new stuff we’ve covered. If you need a brain break, take one.

Let’s take a look at the second sample application, the DogRock CMS.

Showing the DogRock article list

We want to make something like this:

Article list

Figure 15. Article list

Here’s part of the HTML that shows the table:

<tr> 
  <td>A new howl on the prowl</td> 
  <td>February 5, 2010</td> 
  <td>Lumis</td> 
</tr> 
<tr> 
  <td>Somebody let them out!</td> 
  <td>February 3, 2010</td> 
  <td>Bounder</td> 
</tr> 

Figure 16. HTML for the article list

I’ve omitted some stuff for simplicity. It’ll come back later.

Here’s the PHP that creates the HTML that makes the page.

<?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 title, author, when_published
    from articles";
//Run the query.
$record_set = $db->query($query);
//Start the output table.
?>
<table cellpadding="10" cellspacing="10" border="0">
  <thead>
    <tr>
      <th>Title</th>
      <th>Date</th>
      <th>Author</th>
    </tr>
  </thead>
  <tbody>
<?php
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for an article.
  $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>$title</td>
      <td>$when_published</td>
      <td>$author</td>
    </tr>";
} //end while
?>
    </tbody>
  </table>

Figure 17. Article list code

Lines 3 and 4 connect to the database.

Lines 6 to 8 create an SQL query:

select title, author, when_published from articles

This says:

From the articles table, fetch the fields title, author, and when_published for every record.

Why “every record?” Because there’s no WHERE clause. Like this:

select title, author, when_published from articles where author='Bounder'

This would fetch data only for the rows that have Bounder in the author field.

But we don’t have a WHERE clause, so all rows are returned.

Lines 13 to 21 output the table header and such.

The fun starts here:

//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for an article.
  $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>$title</td>
      <td>$when_published</td>
      <td>$author</td>
    </tr>";
} //end while

Part of Figure 15. Article list code

Line 24 fetches a row from the record set. Lines 26 to 28 fetch fields from the row into variables.

Line 30 formats the date in $when_published. The format in the database is YYYY-MM-DD, like 1960-01-03 (my birthday – yes, I’m old). This is the standard international format, but most people aren’t used to it.

This code…

date('F j, Y', strtotime($when_published))

... uses the date() function to format the date in the common “Month Day, Year” format, such as “January 3, 1960.” date() expects the date you give it to be in a certain format – a Unix timestamp. strtotime() converts a YYYY-MM-DD date (from the database) into a Unix timestamp (that the date() function needs).

The output from the date() function is put back into $when_published:

$when_published = date('F j, Y', strtotime($when_published));

The last few lines show the record:

//Output
print "
  <tr>
    <td>$title</td>
    <td>$when_published</td>
    <td>$author</td>
  </tr>";

So that’s it! We now have a list of articles. W00f!

There’s something missing here, though. Here’s the output we want:

Article list

Figure 15 (again). Article list

We want the name of the article to be a clickable link. We’ll look at that later.

Exercise: Showing jokes

Write jokes.php, a page that will show the jokes in your database. You already have a version of the page; it was in the download. You need to add the database logic.

Include error checking code. Check that:

  • The database connection worked.
  • The SQL query was valid.

Don’t worry about sorting the records. We’ll talk about that later.

Don’t include the links to the individual jokes. That comes later, too.

Once you have it running, you can check my solution. But don’t look at it now!

Jokes also puts a list of jokes on the home page, like DogRock does. Do that too. Don’t worry about sorting, links, or limiting the number of records. That comes later.

You can check my solution once you have it running.

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

Summary

  • The SQL SELECT statement returns a record set. A record set has one or more rows of data.
  • You can loop across a record set, extracting each row. From each row, you extract the fields in the row, and use them to create some HTML.

What now?

Let’s see how you can let users sort the data.


How to...

Lessons

User login

Log in problems? Try here


Dogs