How to show only certain rows from a table in my DB?

Question

I am creating a message board in PHP using Zend Framework and phpMyAdmin (MySQL). I'm a beginner with Zend Framework and haven't done much in PHP, so please be as simplistic as possible in your answers.

I've saved the structure of the message board (sections, under-sections and under-under-sections) in the database. I need to display the sections of the forum and their corresponding underSections. The question is - how do I display the under-sections of a particular section under it?

IndexController.php:

 public function indexAction()
{
    $sections = new Model_Sections();
    $this->view->sections = $sections->fetchAll();

    $undersections = new Model_Undersections();
    $this->view->undersections = $undersections->fetchAll();
}

In this code I fetch all the section and undersection data (id & name).

Database model Section.php:

class Model_Sections extends Zend_Db_Table_Abstract
{
    protected $_name = 'sections';
}

Database model Undersection.php:

class Model_Undersections extends Zend_Db_Table_Abstract
{
    protected $_name = 'undersections';
}

Fragment from the main view "index.phtml" concerning output of data:

<div class="section">
   <?php foreach($this->sections as $sections) : ?>

   <!-- Generates names of sections -->
   <h1><?php echo $this->escape($sections->section_name);?></h1>

      <!-- Generates names of undersections -->
      <?php foreach($this->undersections as $undersections) : ?>
         <div class="underSection">
            <h2>
              <a href=" ***link to some controller according to the undersection*** ">
                 <?php echo $this->escape($undersections->undersection_name);?>
              </a>
            </h2>
         </div>
      <?php endforeach; ?>
   <?php endforeach; ?>
 </div>

Currently it displays all undersections under every section.

Answer 1

The reason your code currently is displaying all undersections under each section is because you use a nested for-loop in which the inner loop is always the same. I.e. you are always iterating over the same collection of undersections. You need to define a way for the sections and undersections to have a parent-child relationship.

Here is how I would roughly structure it:

DB structure (table name: sections):

id INT NOT NULL AUTO_INCREMENT
parentId INT DEFAULT 0
section_name TINYTEXT

So the data for all sections would live in the same database table. When you insert a top level section, you would simply leave the parentId column = 0. When you insert an undersection, you would insert the id value of the parent section.

I would also change your model so that you don't have a Model_Section and an Model_Undersection. Instead, have a function within the Model_Section class called e.g. getChildren() which would return to you a collection of all sections that belong to that particular Model_Section instance.

Controller action:

public function indexAction()
{
    $sections = new Model_Sections();
    $this->view->sections = $sections->fetchAll();
}

View script:

<div class="section">
   <?php foreach($this->sections as $sections) : ?>

   <!-- Generates names of sections -->
   <h1><?php echo $this->escape($sections->section_name);?></h1>

      <!-- Generates names of undersections -->
      <?php foreach($sections->getChildren() as $undersections) : ?>
         <div class="underSection">
            <h2>
              <a href=" ***link to some controller according to the undersection*** ">
                 <?php echo $this->escape($undersections->section_name);?>
              </a>
            </h2>
         </div>
      <?php endforeach; ?>
   <?php endforeach; ?>
 </div>

Note the change in using $sections->getChildren() instead of $this->undersections

The biggest benefit you get from this is that your model now is completely recursive. Your undersections can have child sections of their own, and so on.