Problem in creating a dynamic unordered list using php

[AtifMahmood]

I am stuck in creating a dynamic unordered list using php. Regarding Database, i have following tables:

1. Users - contains the user information (userid and username)
2. Groups - contains the groups information (groupid and group description)
3. SubGroups - contains the sub group information (subgroupid and subgroup description)
4. Groupitems - contains the combination of group with their subgourp item (groupid and subgroupid)
5. usergroups - contains the information that user have rights of the groups (userid and groupid)

Now using the above tables, i have write the script in my php file to get an dynamic list which shows in Hierarchy like but unable to get the desired results as attached.

GroupID-1

SubgroupID1

SubgroupID2

GroupID-2

SubgroupID1

SubgroupID2

Here is my PHP code:

<?php
$qry_secs="SELECT DISTINCT groupid FROM usersgroup WHERE userid =0";
$result_secs = mysql_query($qry_secs);
while($row_secs = mysql_fetch_assoc($result_secs)) {
echo '<ul>'.$row_secs['groupid'];
$newqry = "SELECT distinct subgroupid FROM groupitems WHERE groupid ".$row_secs['groupid'];
$result = mysql_query($newqry);
while($row = mysql_fetch_assoc($result)) {
echo '<li><a href="#">' . $row['subgroupid'] . '</li>';
}
echo '</ul>';
} ?>

[Ch0cu3r]

$qry_secs="SELECT DISTINCT groupid FROM usersgroup WHERE userid =0";

You are selecting groupId from the usersgroup table. Shound't it be the usergroups table

 

 

5. usergroups - contains the information that user have rights of the groups (userid and groupid)

$newqry = "SELECT distinct subgroupid FROM groupitems WHERE groupid ".$row_secs['groupid'];

You need to add a   =   before   ".$row_secs['groupid']; in your query

 

 

Having said that, doing sub queries on results from another query is very inefficient for the database. You should look into using JOINS, untested code

result_secs = mysql_query("SELECT  ug.groupid, 
        gi.subgroupid
FROM usersgroups AS ug 
JOIN groupitems AS gi ON USING(groupid)
WHERE ug.userid = 0");

$usergroups = array();
while($row_secs = mysql_fetch_assoc($result_secs)) 
{
    $usergroups[$row['groupid']][] = $row['subgroupid'];
}

echo '<ul>';
foreach($usergroups as $groupid => $subgroups)
{
    echo '<li><b>Group ID: ' . $groupid . '</b><ul>';
    foreach($subgroups as $subgroupid)
    {
        echo '<li><a href="#">' . $subgroupid . '</li>'; 
    }

    echo '</ul></li>';
}

echo '</ul>';

Edited February 26, 2014 by Ch0cu3r