Dynamic navigation for a gallery page

[astypulk]

I am trying to create dynamic categories nav for a gallery page.

The structure of the nav would be:

<ul>
<li><a href="#">Category1</a>
<ul>
<li><a href="#">SubCategory1</a></li>
<li><a href="#">SubCategory2</a></li>
<li><a href="#">SubCategory3</a></li>
</ul>
</li>
<li><a href="#">Category2</a>
<ul>
<li><a href="#">SubCategory1</a></li>
<li><a href="#">SubCategory2</a></li>
<li><a href="#">SubCategory3</a></li>
</ul>
</li>
</ul>

The current tables I created in my database are:

 

categories 
idCat 
Category

 

subcategories 
idSub 
subCategory

 

cat_sub 
idCat 
idSub

 

And what I have so far in PHP and MySQL statements is:

<?php
$con = mysql_connect("localhost", "xray", "password") or die('Could not connect to server');
mysql_select_db("xray", $con) or die('Sorry, could not connect to the database');

$query = "SELECT categories.Category, categories.idCat, subcategories.subCategory, subcategories.idSub
FROM categories JOIN cat_sub ON categories.idCat = cat_sub.idCat
JOIN subcategories ON subcategories.idSub = cat_sub.idSub";

$result = mysql_query($query) or die('Error');

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

}
?>

I think I am probably using the wrong join, as its returning each Category several times. And I am not sure how to structure the while loop to achieve the above mentioned structure. Any advice would be much appreciated.

Thanks, Aleks

[Ch0cu3r]

Yes the join query will return the same category each time, but the sub category should be different?

 

To get the result you want you'll want loop through the results and store the categories in an array

// store categories in array
$categories = array();
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    $category_id = $row['idCat'];

    // create category
    if(!isset($categories[ $category_id ]))
    {
        $categories[ $category_id ] = array(
            'title' => $row['Category'],
            'sub_categories' => array() // store sub categories as an array
        );
    }

    // add sub categories
    $categories[ $category_id ]['sub_categories'][] = array(
        'id'    => $row['idSub'],
        'title' => $row['subCategory']
    );
}

To output the category list

// output category list
echo "<ul>\n";
foreach($categories as $cat_id => $category)
{
    echo "\t<li><a href=\"#\">" . $category['title'] . "</a>\n";
    if(is_array($category['sub_categories']))
    {
        echo "\t<ul>\n";

        foreach ($category['sub_categories'] as $sub_id => $sub_category)
        {
            echo "\t\t<li><a href=\"#\">" . $sub_category['title'] . "</a></li>\n";
        }

        echo "\n\t</ul>\n</li>\n";
    }
}
echo '</ul>';

[astypulk]

Fantastic!!! Can't believe that was that easy. Worked like a charm. Thank you so much Ch0cu3r!!!

 

For future users, the code above had a few small parse errors (marked them in red):

 

// create category
if(!isset($categories[ $category_id ]))
{
$categories[ $category_id ] = array(
'title' => $row['Category'],
'sub_categories' => array(), // store sub categories as an array
);
}
// add sub categories
else
{
$categories[ $category_id ]['sub_categories'][] = array(
'id' => $row['idSub'],
'title' => $row['subCategory'],
);
}
}

[astypulk]

Hmmm. Actually noticed small problem. The subcategories start from one item down. ex. skips subcategory1 and starts from subcategory2

[Ch0cu3r]

Hmmm. Actually noticed small problem. The subcategories start from one item down. ex. skips subcategory1 and starts from subcategory2

Sorry about that. Look at my code again. I did edit it to fix that (I removed the else)

[astypulk]

Sweet!!! That definably fixed it! Thank you so much again. 

Now, I just got to make sure I can replicate it in the future. 

 

Cheers!