Help with 2-Tier Navigation

[Besherek]

Hi folks,

I need a little help working out a 2 tier navigation that is database driven as I'll need to use CMS to insert new categories and topics at a later date.

The navigation is in unordered list format looking similar to below. Two category listed navigation with two sub topics for each as a basic example. Some categories will have more or less topics.

<ul>
    <li>
        Category 1
    </li>
    <ul>
        <li>
            Topic 1
        </li>
        <li>
            Topic 2
        </li>
    </ul>
    <li>
        Category 2
    </li>
    <ul>
        <li>
            Topic 1
        </li>
        <li>
            Topic 2
        </li>
    </ul>
</ul>

I've built two tables in preparationtblCategory
cat_id as primary key
cat_name as nvarchar

tblTopics
topic_id as primary key
topic_name as nvarchar
cat_id as foreign key

I've read it's also possible and probably a better idea to have 1 table, then using parent, child to array the data. I'm keen to look at that as well.

So the question is, how do I loop the database records through these nested unordered lists?

Thanks in advance

[kkeith29]

run two queries in a while loop.

<?php

$html = "<ul>\n";
$query = mysql_query("SELECT `cat_id`,`cat_name` FROM `tblCategory`");
while( list( $id,$name ) = mysql_fetch_row( $query ) ) {
	$html .= "\t<li>{$name}";
	$query2 = mysql_query("SELECT `topic_id`,`topic_name` FROM `tblTopics` WHERE `cat_id` = {$id}");
	if ( mysql_num_rows( $query ) > 0 ) {
		$html .= "\t\t<ul>\n";
		while( list( $tid,$tname ) = mysql_fetch_row( $query2 ) ) {
			$html .= "\t\t\t<li>{$tname}</li>\n";
		}
		$html .= "\t\t</ul>\n";
	}
	$html .= "</li>\n";
}
$html .= "</ul>";

echo $html;

?>

I know there is a recursive way to do it (didn't feel like thinking of it), but this will work if you only want 2-tier.

[cwarn23]

As you've mentioned, it is best to have 1 table as it will save recourses and space. So the table structure will be as follows:

tblCategory
cat_id as primary key
cat_name as nvarchar
topic_id as nvarchar
topic_name as nvarchar

Then to retrieve the data and display it as the html format shown in post #1, simply use the following:

<?
//mysql connections

$result=mysql_query('SELECT * FROM `tblCategory` ORDER BY cat_id');
$catid=false;
while ($row=mysql_fetch_array($result)) {
    if ($catid!==$row['catname']) {
        if ($catid==false) {
            echo '<ul><li>'.$row['cat_name'].'</li><ul>';
            } else {
            echo '</ul><li>'.$row['cat_name'].'</li><ul>';
            }
        echo '<li>'.$row['topic_name'].'</li>';
        }
    $catid=$row['cat_name'];
    }

Hope that helps.

[kkeith29]

As you've mentioned, it is best to have 1 table as it will save recourses and space. So the table structure will be as follows:

tblCategory
cat_id as primary key
cat_name as nvarchar
topic_id as nvarchar
topic_name as nvarchar

Then to retrieve the data and display it as the html format shown in post #1, simply use the following:

<?
//mysql connections

$result=mysql_query('SELECT * FROM `tblCategory` ORDER BY cat_id');
$catid=false;
while ($row=mysql_fetch_array($result)) {
    if ($catid!==$row['catname']) {
        if ($catid==false) {
            echo '<ul><li>'.$row['cat_name'].'</li><ul>';
            } else {
            echo '</ul><li>'.$row['cat_name'].'</li><ul>';
            }
        echo '<li>'.$row['topic_name'].'</li>';
        }
    $catid=$row['cat_name'];
    }

Hope that helps.

Doesn't make sense to me. When there is two topics with the same category, you will run into problems. Maybe I am not thinking correctly as its 4:47am but I can't think of how that would work.

[cwarn23]

Doesn't make sense to me. When there is two topics with the same category, you will run into problems. Maybe I am not thinking correctly as its 4:47am but I can't think of how that would work.

Well I have designed it so that if there are two topics or more topics in the one category, the category field will always contain the same category name as like an identifier and that is what the following if statement is all about:

if ($catid!==$row['catname']) {

[Besherek]

Thanks for you help guys. I do appreciate both your input on my problem.

I tried both, however Keith's works best with my layout and Java I have going with it. I already had the tables ready which I use for other parts of the site, so it will save some time.

Cheers!