Categories & Subcategories

crazyryan · 08-24-2008, 11:34 AM

Hey

I'm having some problems with categories and subcategories.

Basically I want to assign pages to either a category or subcategory.

For example:

Main Category
- Sub Category 1
-- Page 1
-- Page 2
- Sub Category 2
-- Page 1
-- Page 2

Another Category
- Sub Category 1
-- Page 1
-- Page 2
- Sub Category 2
-- Page 1
-- Page 2

I've got a categories table with columns categoryid, categoryname, parentid, where parentid is the main category.

In my pages table I have categoryid (the category the page belongs to).

The problem I am having is the mysql query and actually outputting the menu like I have added above.

Could anyone help me do this?

CoryMathews · 08-24-2008, 04:39 PM

Its sounds as if you have the database set up correctly so you should be in good shape in that area. To display this I would query the db, and store all the rows in an array. Then take the array and search for the first main directory, then its sub categories and then the sub categories subs, and then move back to the next category and continue on like that. The reason for thearray is so that you are not having 8 or 12 queries to your db which would take a lot more time to run.

crazyryan · 08-24-2008, 04:40 PM

Thanks for the reply Cory, someone did suggest using a tree or something but I have no idea what query to use and how I would go about doing the array and such. :(

delayedinsanity · 08-24-2008, 05:05 PM

SQL Join

Example:

Code:

SELECT parent.name AS parent, category.name AS category, page.name AS page
FROM pages_table AS page,
LEFT JOIN categories_table AS category
ON page.categoryid = category.categoryid
LEFT JOIN parent_table AS parent
ON category.parentid = parent.id

You'll have to modify the above query to reflect the proper table and column names before it'll work. It's a pretty generic join too, you may want to look into the link above it and see if there's something that suits your taste or needs a little more closely - the above should give you a result set with 3 columns, and a row for every page that's in your database. Each row will include a column detailing which category that page belongs to, and in turn what parent category that belongs to. How you display the output is up to you, but all the data should be there to do it however you like.
-m

crazyryan · 08-24-2008, 06:11 PM

Still cant figure it out so I'll give $20 tomorrow to the first person who posts the code.

maZtah · 08-25-2008, 01:26 PM

That's nice!

;)

First select all the categories:

PHP Code:


			
$szQuery = "SELECT id, parentId, name FROM categories ORDER BY parentId ASC, name ASC";
$pSidebar = mysql_query($szQuery) or die(mysql_error());

Then put the results into an array:

PHP Code:


			
if (mysql_numrows($pSidebar))
{
    while ($aRow = mysql_fetch_array($pSidebar))
    {
        if ($aRow['parentId'] == 0) {
            $aParent[$aRow['id']] = $aRow;
        }
        else
        {
            $aParent[$aRow['parentId']]['child'][] = $aRow;
        }
    }
}

Then output!

PHP Code:


			
<ul id="categories">
<?php
foreach ($aParent as $iParent => $aData)
    { ?>
            <li>
                <?php echo $aData['name']; ?>
<?php
        $aChild = $aData['child'];
        if (is_array($aChild))
        { ?>
                <ul>
<?php
            foreach ($aChild as $iChild => $aSubData)
            { ?>
                    <li>
                        <?php echo $aSubData['name']; ?>
                    </li>
<?php
            } ?>
                </ul>
<?php
        } ?>
</ul>

Good luck