I have a database which holds two tables.
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>
<?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:
<?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:
<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>