Making sub-menus finding parent

[asleboeuf]

Hello everyone,
[img src=\"http://worcesterwideweb.com/work/dam.jpg\" border=\"0\" alt=\"IPB Image\" /]

Im trying to make submenus for a cms im playing with, but i cant find the correct SQL SELECT im suppose to use to get the submenu items. basicly I want it to show up like this

Home
About us
Contact
Test Page
- test page sub

and for each sub to be categorized under its parent. As you see i have a parent column with the id of the items parent. What sql statement would I use to select all subitems of the parent?

I have the code working for my parents but not for the child, here is the current child code:

[code]
       $query3 = $database->query("SELECT * FROM pages WHERE parent = id");

while ($list3 = mysql_fetch_array($query3))
{
               $id = stripslashes($list3['id']);
        $date = stripslashes($list3['date']);
        $title = stripslashes($list3['title']);
        $linktitle = stripslashes($list3['linktitle']);
        $pagename = stripslashes($list3['pagename']);
        $description = stripslashes($list3['description']);
        $menuitem = stripslashes($list3['menuitem']);
        $parent = stripslashes($list3['parent']);
            
    
        $items .=  '<div id="anylinkmenu'.$h++.'" class="anylinkcss"><a  href="'.$siteroot.'pages/'.$id.'/'.$pagename.'.html">'.$linktitle.' </a></div>';
        echo $items;
        } [/code]

any help is greatly appreciated thanks :)

[michaellunsford]

I'd create another table with just the subs and link them to the main page keyfields.

[emehrkay]

maybe something like this would work

SELECT
title,
(SELECT title FROM table WHERE parent = id) AS submenu
FROM
table
WHERE
parent = 0

my logic says select only the items with no parent and select their children. however,t hat subquery might return more than one result - build from here

[freakus_maximus]

Here's a bit that I use. There are some class settings and in here which you can remove and/or add your own. If you dont use CSS you can also see where I have added FONT tags.

Maybe it will give you a step in the right direction for yours. I was having problems also until I figured out the right SELECT statment, well actually it's that magical ORDER BY statement.

In my table, I have every entry with a 'project' and a 'subproject'. Similar to a 'catagory' and 'subcatagory' others might use.

Essentially, I am take the now ordered list and displaying the 'project' first, then the 'subproject'. After that each item (url and name) are listed under the 'subproject'. Once all the items are listed, it adds the next 'subproject' and it's items. This continues until there are no additional 'subproject' for that 'project'. Then it proceeds to process the next 'project' and so on.

This works well for me. I used this for creating a dynamic navigation menu.

[code]
//Row 0 - id
//Row 1 - name
//Row 2 - type
//Row 3 - url
//Row 4 - project
//Row 5 - subproject
  
//Retrieve Links and build Navigation Menu for Group Links
$result = mysql_query ("SELECT * FROM yourtablename ORDER BY project, subproject ASC")
  or die ("Query failed");
while($row=mysql_fetch_row($result))
{
If ($currentgroup != $row[4]) {
echo '</ul>';
    echo '<P align="left"><U><STRONG><FONT color="orange" size="2">'. $row[4] .'</U></FONT></STRONG><BR>';
}
    If ($currentsub != $row[5]) {
        If ($currentgroup == $row[4]) {
}    
    echo '<span class="topictitle">'. $row[5] .'</span><br>';
$currentsub = $row[5];
}
echo ('<span class="gensmall"><A href="'.$row[3].'"><li>'.$row[1].'</A></span>');
echo '<br>';
$currentgroup = $row[4];
}
echo '</ul>'; [/code]

[asleboeuf]

Excellent thank you that was really helpfull!

good to have communities like this :)