Showing sorted data

See more about:

Where are we?

You know how to show users the data in a database. What if they want to sort the data? Let’s see how you do that.

This lesson’s goals

In this lesson, you will learn:

  • The ORDER BY clause of the SELECT statement tells MySQL to sort records in the record set.
  • Let users choose the sort order by adding a GET parameter to links. Add code that will append different fields to ORDER BY, depending on the parameter.
  • The LIMIT clause of the SELECT statement sets the maximum number of records MySQL will add to a record set.

The ORDER keyword

Recall that the SQL SELECT command returns a bunch of rows:

select name, description, image_file_name, price from products;

You can tell MySQL how to sort the returned rows. Add ORDER BY and then the field to sort by.

For example, this…

select name, description, image_file_name, price from products order by name;

... returns:

Sorted by name

Figure 1. Sorted by name

This…

select name, description, image_file_name, price from products order by image_file_name;

... returns:

Sorted by image_file_name

Figure 2. Sorted by image_file_name

This…

select name, description, image_file_name, price from products order by price;

... returns:

Sorted by price

Figure 3. Sorted by price

By default, the records are sorted in ascending order, from smaller to larger. But you can change this. For example, if you want to sort by price from high to low:

select name, description, image_file_name, price from products order by price desc;

I added desc for “descending” after the sort field name. This returns:

Sorted by price

Figure 4. Sorted by price, descending

Sorting is not difficult. But it’s trickier to…

Let the user choose the sort order

We want to let the user choose how to sort the data. Here’s the interface for the product list:

Sort interface for products

Figure 5. Sort interface for products

The user can click on the product name to sort by name, or click on an arrow to sort by price, from low to high, or high to low.

You can try it.

How does this work? The URLs give a clue. Here’s how the URL changes when the user clicks on the “Price” link.

User sorting by price

Figure 6. User sorting by price

The URL of the page is normally products.php. This gives a default sort order, by name. Then the user clicks the sort arrow. The HTML for the arrow is:

<a href="products.php?order=price_asc"> 
  <img src="./library/arrow_down.png"
    alt="Sort from low to high"> 
</a>

Figure 7. HTML for a sort arrow in products.php

Look at the href in line 1 of Figure 7:

products.php?order=price_asc

So the page points to itself, but with a parameter order.

When this renders (Figure 6), the user sees the data sorted ascending by price.

For this to work, we need:

  • A way for the page to get a sort order from the URL.
  • A way to apply the sort order.
  • Links that send the sort order to the page, as in Figure 7.

Let’s look at the code for DogToys.

Sorting the DogToys product list

Here’s products.php:

<?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
  order by ";
//Append the sort order.
$order = $_GET['order'];
if ( $order == 'price_asc' ) {
  $query .= 'price asc';
}
else if ( $order == 'price_desc' ) {
  $query .= 'price desc';
}
else {
  //Default sort order.
  $query .= 'name';
}

//Run the query.
$record_set = $db->query($query);
//Start the output table.
?>
<table cellpadding="5" cellspacing="0" border="0">
  <thead>
    <tr>
      <th> </th>
      <th><a href="products.php?order=name">Name</a></th>
      <th>Price<br>
        <a href="products.php?order=price_asc">
          <img src="<?php print $path_to_root; ?>/library/arrow_down.png"
               alt="Sort from low to high">
        </a>
        <a href="products.php?order=price_desc">
          <img src="<?php print $path_to_root; ?>/library/arrow_up.png"
               alt="Sort from high to low">
        </a>
      </th>
    </tr>
  </thead>
  <tbody>
<?php
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
...

Figure 8. products.php

I’ve omitted some code for simplicity.

We need to add the ORDER BY to the SQL. That’s what lines 9 to 21 do.

Line 9 adds “order by” to the SQL statement. But the statement is not complete. Order by what? That’s what the next few lines figure out.

Line 11 …

$order = $_GET['order'];

... fetches the value of the GET parameter order, if there is one. Recall that GET passes data to a Web page through the URL. The value of order is put in the PHP variable $order.

Here are the next few lines (lines 12 to 14):

if ( $order == 'price_asc' ) {
   $query .= 'price asc';
}

Recall that .= means “append” or “concatenate.” In normal words, “add to the end of.” So id the variable $order contained price_asc, then price asc would get appended to the SQL statement. It would end like this:

... order by price asc

Now the order by clause is complete. When the SQL statement is run, the records will be sorted by price in ascending order.

Lines 15 to 17 append price desc to the SQL statement, if $order contains price_desc:

if ( $order == 'price_desc' ) {
   $query .= 'price desc';
}

If $order contains anything else, or nothing at all, then line 20 sets the default sort order to name:

$query .= 'name';

The SQL query now has a complete ORDER BY clause, so MySQL will sort the data before returning it.

The last thing we need is the interface, that is, a way for the user to select the sort order. That’s what lines 31, 33, and 37 do:

<a href="products.php?order=name">

<a href="products.php?order=price_asc">

<a href="products.php?order=price_desc">

Clicking these links will cause products.php to reload itself, but with a different sort order.

Sorting the DogRock articles list

Here’s the code for articles.php:

<?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
    order by ";
//Append the sort order.
$order = $_GET['order'];
if ( $order == 'date' ) {
  $query .= 'when_published desc';
}
else if ( $order == 'author' ) {
  $query .= 'author';
}
else {
  //Default sort order.
  $query .= 'title';
}
//Run the query.
$record_set = $db->query($query);
//Start the output table.
?>
<table cellpadding="10" cellspacing="10" border="0">
  <thead>
    <tr>
      <th><a href="articles.php?order=title">Title</a></th>
      <th><a href="articles.php?order=date">Date</a></th>
      <th><a href="articles.php?order=author">Author</a></th>
    </tr>
  </thead>
  <tbody>
<?php
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
...

Figure 9. articles.php

It works the same way as products.php. Clicking a link (lines 28, 29, and 30) causes articles.php to reload itself, passing a sort order.

Line 10 gets the order from the URL.

Lines 11 to 20 complete the SQL statement, depending on which sort order the user selected.

The DogRock home page

The DogRock site also shows articles on its home page. It shows the three most recent:

Most recent articles

Figure 10. Most recent articles

They are sorted descending by date, so the most recent article is first.

Here’s the code for the page, with some things omitted.

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Fetch the three most recently published articles.
$query = "select article_id, title, when_published
    from articles
    order by when_published desc
    limit 3";
$record_set = $db->query($query);
//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'];
  //Format the date.
  $when_published = date('F j, Y', strtotime($when_published));
  print "<p>$title ($when_published)</p>";
} //End while.
?>

Figure 11. index.php for DogRock

Line 8 adds the sort order:

order by when_published desc

The desc means the the highest (most recent) articles are first.

Line 9 shows something new:

limit 3

MySQL will limit the record set it returns to the first three records.

The rest is as before.

Exercise: Showing sorted jokes

Change jokes.php so that users can sort by title, funniness, and date. Don’t worry about linking a joke’s title to its content; that comes later.

You can check my solution. But do it yourself first!

Now change the home page so that the user sees the three funniest jokes.

You can check my solution. But, as before, do it yourself first!

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

Summary

  • The ORDER BY clause of the SELECT statement tells MySQL to sort records in the record set.
  • Let users choose the sort order by adding a GET parameter to links. Add code that will append different fields to ORDER BY, depending on the parameter.
  • The LIMIT clause of the SELECT statement sets the maximum number of records MySQL will add to a record set.

What now?

Let’s see how you let users “drill down,” that is, go from summary data (e.g., just the title of an article) to details (e.g., all of the data about the article).


How to...

Lessons

User login

Log in problems? Try here


Dogs