up vote 0 down vote favorite

I have a menu that uses PHP generated HTML to create jQuery dropdown menu. The information used to build the menu is coming from a database table of categories:

cats (db table)

cat_id | cat_name | cat_parent | cat_short
__________________________________________
1      | Home     | 0          | home
__________________________________________
2      | Games    | 1          | games
__________________________________________
3      | Checkers | 2          | checkers

The code that I have built so far only allows for 2 levels in the menu, and won't add another dropdown for a 3rd level (ie Checkers):

<ul class="dropdown">
    <?php
    $sql ='SELECT * FROM cats WHERE cat_parent = 0';
    $result = $db->sql_query($sql);
    while($row = $db->sql_fetchrow($result)){
        $cats[] = array(
            'id' => $row['cat_id'],
            'name' => $row['cat_name'],
            'parent' => $row['cat_parent'],
            'short' => $row['cat_short'],
            'subs' => array()
        );
    }

    foreach($cats as $cat){
        $sql = 'SELECT * FROM cats WHERE cat_parent = '.$cat['id'];
        $result = $db->sql_query($sql);
        while($row = $db->sql_fetchrow($result)){
            $cat['subs'][] = array(
                'id' => $row['cat_id'],
                'name' => $row['cat_name'],
                'parent' => $row['cat_parent'],
                'short' => $row['cat_short']
            );
        }
        $menu[] = $cat;
    }

    foreach($menu as $cat){ ?>
        <li class="ui-state-default"><a class="transition" href="index.php?mode=<?php echo $cat['short']; ?>"><?php echo $cat['name']; ?></a>
        <?php if($cat['subs'] != '0'){ ?>
            <ul class="sub_menu">
            <?php foreach($cat['subs'] as $sub){ ?>
                <li class="ui-state-default"><a class="transition" href="index.php?mode=<?php echo $sub['short']; ?>"><?php echo $sub['name']; ?></a></li>
            <?php } ?>
            </ul>
        <?php } ?>
        </li>
    <?php } ?>
</ul>

How can I rewrite this (or write a function) to be able to utilize 3 layers? I only need the PHP loop necessary, as I can easily manipulate the list item elements with jquery to perform the actual drop down.

share|improve this question
I retaged the question with MYSQL. My guess is you could change your query to something that returned a complete object already well nested. I'm not a MYSQL pro but maybe someone else will be able to help. – ComputerArts Oct 18 '12 at 0:38
@ComputerArts Ah, yes. I hadn't thought about the possibility of restructuring the MySQL query itself. I'm still not sure about the correct syntax to achieve the desired goal, however. – chaoskreator Oct 18 '12 at 0:42
feedback

1 Answer

up vote 0 down vote

The following code (untested, sorry) may do the trick for any number of levels:

<?php
# a key-value list of all cats by their IDs:
$all_cats = array();

# the final list of cats we want:
$cats = array();

$sql ='SELECT * FROM cats';
$result = $db->sql_query($sql);

# fetch all cats
while($row = $db->sql_fetchrow($result)){
    $all_cats[$row['cat_id']] = array(
        'id' => $row['cat_id'],
        'name' => $row['cat_name'],
        'parent' => $row['cat_parent'],
        'short' => $row['cat_short'],
        'subs' => array()
    );
}

# group cats by parent (note reference & to $cat) - we are dealing with the same object
foreach ($all_cats as $id => &$cat) {
    $parent = $cat['cat_parent'];
    if ($parent == 0) {
        $cats[] = $cat;
    } else {
        $all_cats[$parent]['subs'][] = $cat;
    }
}

At the end, $cats will contain an array of first-level cats, with its subs filled properly.

Note that if your table gets too large, this will take a lot of memory, but at least it will hit the database only once.

share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.