php - 循环导航将子页面拉入正确的主题

Question

I have a database which holds two tables.

• table 1: - 'subjects'
• table 2: - 'pages'

I wish to display the navigation so that I get the right pages with the corresponding subjects.

I.e

*subject 1
   - page 1
   - page 2
*subject 2
   - page 1

My pages have the same Id which connects them to their subjects. I've called this 'page_link' or 'subject_link' for example

*subject 1 (has a subject_link of 1)
  -page 1 (has a page_link of 1)

So far my code lists subject 1 and all of the sub pages within that. Then it lists the next subject but it doesn't list it's sub pages.

Why is this?

<?php

// writing the query - selecting subject name
$subject_name_query = "SELECT id, subject_name, subject_link FROM subjects";
$subject_name_result = mysqli_query($connection, $subject_name_query);

// writing the query - selecting page
$page_name_query = "SELECT id, page_name, page_link FROM pages";
$page_name_result = mysqli_query($connection, $page_name_query);

?>

<ul>

    <?php

        while($subject_name = mysqli_fetch_assoc($subject_name_result)) {
            echo '<li><a href="?subject=' . $subject_name['id'] . '">' . $subject_name['subject_name'] . '</a>';

            while($page_name = mysqli_fetch_assoc($page_name_result)) {
                if($page_name['page_link'] == $subject_name['subject_link']) {
                    echo '<ul><li><a href="?page=' . $page_name['id'] . '">' . $page_name['page_name'] . '</a></li></ul>';
                }
            }
            echo '</li>';           
        }    
    ?>    
</ul>

Answer 1

<?php
    // writing the query - selecting subject name
    $subject_query = "SELECT id, subject_name, subject_link FROM subjects";
    $subject_result = mysqli_query($connection, $subject_query);

    // writing the query - selecting page
    $page_query = "SELECT id, page_name, page_link FROM pages";
    $page_result = mysqli_query($connection, $page_query);

    function fetch_as_array($result){
      $array = array();

      while($row = mysqli_fetch_assoc($result)){
        array_push($array, $row);
      }

      return $array;
    }

    $subject_array = fetch_as_array($subject_result);
    $page_array = fetch_as_array($page_result);

    foreach($subject_array as $subject){

      echo '<li><a href="?subject=' . $subject['id'] . '">' . $subject['subject_name'] . '</a>';

      foreach($page_array as $page){

        if($page['page_link'] == $subject['subject_link']){

          echo '<ul><li><a href="?page=' . $page['id'] . '">' . $page['page_name'] . '</a></li></ul>';
        }

      }

      echo '</li>';
    }
?>

That should do the trick.

But putting markup in PHP is horrible. I'de use a templating language like Jade or Mustache, depending on how you want to write it. For example for your situtation you could do this:

subjects.php

<?php

// THIS IS ALL THE SAME

// writing the query - selecting subject name
$subject_query = "SELECT id, subject_name, subject_link FROM subjects";
$subject_result = mysqli_query($connection, $subject_query);

// writing the query - selecting page
$page_query = "SELECT id, page_name, page_link FROM pages";
$page_result = mysqli_query($connection, $page_query);

function fetch_as_array($result){
  $array = array();

  while($row = mysqli_fetch_assoc($result)){
    array_push($array, $row);
  }

  return $array;
}

$subject_array = fetch_as_array($subject_result);
$page_array = fetch_as_array($page_result);

// Different stuff is below:

foreach($subject_array as $subject){

  $subject_array['pages'] = array();

  foreach($page_array as $page){

    if($page['page_link'] == $subject['subject_link']){

        array_push($subject_array['pages'], $page);
    }
  }
}

// Initiate a new mustache engine with the directory being the inside the views folder.
$m = new Mustache_Engine(array(
   'loader' => new Mustache_Loader_FilesystemLoader('/views')
));

// render using the 'subjects.mustache' file and the $subject_array to populate the template
echo $m->render('subjects', $subject_array); 

?>

and then with the already sorted data for the view you can then write a template to use this data:

subjects.mustache

<ul>
    {{#subjects}}
        <li>
            <a href="?subject={{id}}">{{subject_name}}</a>
            <ul>
                {{#pages}} 
                    <li>
                        <a href="?page={{id}}">{{page_name}}</a>
                    </li> 
                {{/pages}}
            </ul>
        </li>
    {{/subjects}}
</ul>

So it forces you to separate your PHP and focus on writing better and MUCH more maintainable code. {{mustache}} is just one of the templating languages, Jade is also very cool and offers a very nice syntax where it offers writing markup like this:

h1 Hello world
span= myVariable

instead of:

<h1>Hello World</h1>
<span><?php echo $myVariable; ?></span>