Create nested lists from a recordset (PHP)

Let's begin with a simple scenario. You have a set of database results that lists events by date. You want to display each event related with a date, but you don't want the date repeated each time if it's the same as the previous one. The results will be displayed in a table, and look something like this:

January 21    Children's playgroup
                        Classical concert
                        User group meeting

January 22    Disco
                        Geekathon

Make sure that your database results are sorted in date order. If you're using a Dreamweaver recordset called "events" and a repeat region, the code generated by Dreamweaver will look like this:

<table>
  <tr>
    <th scope="col">Date</th>
    <th scope="col">Event</th>
  </tr>
  <?php do { ?>
  <tr>
    <td><?php echo $row_events['evt_date'];
?></td>
    <td><?php echo $row_events['event']; ?></td>
  </tr>
  <?php } while ($row_events = mysql_fetch_assoc($events));
?>
</table>

Dreamweaver repeat regions use a do... while loop. To keep track of the value you don't want repeated. Initialize a variable outside the loop, and set its value to an empty string. Inside the loop, compare the current value with the one in the variable. Since the variable is an empty string the first time round, it won't match the current value, so you can show it. Then assign the current value to the variable. The next time the loop runs, the variable contains the value from the previous row. If it's the same, the comparison fails, so you skip displaying the result. Here's what the code looks like:

<table>
  <tr>
    <th scope="col">Date</th>
    <th scope="col">Event</th>
  </tr>
  <?php
  // initialize variable to hold previous value
  $previous = '';
  ?>
  <?php do { ?>
  <tr>
    <td><?php
        // display field if it doesn't match previous value
        if ($row_events['evt_date'] != $previous) {
          echo $row_events['evt_date']; 
        } else {
          // otherwise, insert a non-breaking space
          echo '&nbsp;';
        }
        // set $previous to current value for next time
        $previous = $row_events['evt_date'];
        ?>
    </td>
    <td><?php echo $row_events['event']; ?></td>
  </tr>
  <?php } while ($row_events = mysql_fetch_assoc($events));
?>
</table>

Creating nested lists

Now that you have seen the basic principle, it's time to tackle a common scenario: creating a series of nested lists from a set of database results. It sounds simple enough, but the tricky problem lies in getting the opening and closing list tags in the right place. Unlike table rows, lists have a more complex structure.

The following example shows how you could build a dynamic menu from a database result that selects each category with its related subcategories like this:

Category 1  Subcategory 1
Category 1  Subcategory 2
Category 1  Subcategory 3
Category 2  Subcategory 1
Category 2  Subcategory 2

You want each category to be an item in an unordered list, with each related subcategory in a nested list. To do so, the HTML code needs to look like this:

<ul>
  <li>Category 1
    <ul>
      <li>Subcategory 1</li>
      <li>Subcategory 2</li>
      <li>Subcategory 3</li>
    </ul>
  </li>
  <li>Category 2
    <ul>
      <li>Subcategory 1</li>
      <li>Subcategory 2</li>
    </ul>
  </li>
</ul>

This is how you do it:

<ul>
<?php
// initialize variables
$previous = '';
$first = true;
do {
  // if not the same value as $previous
  if ($row_categories['category'] != $previous) {
    // if not the first time, close the nested list
    if (!$first) {
      echo '</ul></li>';
    }
    // display the category
    echo '<li>' . $row_categories['category'];
    // open the nested list
    echo '<ul>';
    // store the current value for comparison next time
    $previous = $row_categories['category'];
  }
  // display the subcategories
  echo '<li>' . $row_categories['subcategory'] .
'</li>';
  // it's no longer the first time
  $first = false;
  } while ($row_categories = mysql_fetch_assoc($categories)); ?>
    </ul>
  </li>
</ul>

Most people's first instinct is to think that nested lists need nested loops. In fact, it's the same principle as before. You use just one loop, but suppress the repeated value if it's the same as the previous one. However, you need to close the nested list and the previous list item each time you come to a new value in the main list. The only time you don't need this is the first time round, so a second variable is needed to check whether this is the first time through the loop. Outside the loop, its value is set to true, but it's reset to false at the end of the loop, so it's true only the first time.

Right at the end, you need to close all list items.

To make the code easier to read, I have omitted the code to turn each list item into a link. The loop and the conditional statements remain the same.