CODEWITHSUNDEEP
php
user2710234
user2710234

Reputation: 3225

Showing mysql rows grouped together

I have a table called menu with columns (sequence, page_seq, name, parent_id)

sequence and page_seq are irrelevant

the rows with the parent_id blank are the top menu items and then the rows with parent_id not blank are sub menu items

so, lets say there is a row with the name Top Item with parent_id blank, sequence as 1 and another row with the name Sub 1 with a parent_id of 1 so when you hover over Top Item you will se Sub 1 as a sub menu item.

i am trying to display these on my backend admin panel in groups. so, there will be the Top Menu item with its sub links below it like...

Top Item 1
- Sub 1
- Sub 2

Top Item 2
- Sub 3
- Sub 4

and so on...

here is the PHP i have so far:

<table border="0" cellpadding="5" cellspacing="5">
    <tr>
        <td><strong>Name</strong></td>
    </tr>
    <?php
    $sql=" SELECT * FROM menu where parent_id = '' ";
    $rs=mysql_query($sql,$conn) or die(mysql_error());
    while($result=mysql_fetch_array($rs))
    {
        echo '<tr>
        <td>'.$result["name"].'</td>
        </tr>';
        $sql2="SELECT * from menu where parent_id != '' ";
        $rs2=mysql_query($sql2,$conn) or die(mysql_error());
        while($result2=mysql_fetch_array($rs2))
        {
            echo '<tr>
            <td><dir>'.$result2["name"].'</dir></td>
            </tr>';
        }
}
?>
</table>

but its displaying the Top Menu items with every sub menu item below each one.

any ideas how i can achieve this?

Upvotes: 1

Views: 75

Answers (2)

James
James

Reputation: 4783

ok, it seems you use the same table to store parent and child.
Parents are defined by parent_id being blank.
Childs are defined by parent_id not being blank.
I don't know how Childs and parents are linked.

Your first query sets about a loop and gets a parent. For each loop you want another loop to show all childs for that parent.
You need to define in the second loop, which parent is currently in play from the first loop to know which childs to display:

$sql2="SELECT * from menu where parent_id != '' AND name = $result['XX']"

Where $result['XX'] is the current parent from the first loop and is whatever links parent to childs, however I have no idea what you use to determine what parents are linked to what childs - name?

Suggestion/info You have no checking (maybe you do and not here for simple examples). What happens if either query finds nothing? Or if first loop finds a parent, and second loop finds no child?
You need to check things, make sure you have data, results, etc, otherwise you could end up with blank menu items, or worse.

It looks like you could improve this with two tables, one for all parents and one for all childs. With some unique reference from the parent table used in the childs table as the link between parents and childs.
So you can update and select some childs for a given parent, or find a parent for a given child, etc.

Or at least if you still use one table, strictly define parents and childs by having a column that determines them. ie type=parent, type=child. This will remove the possible issues from your determining something's role based on it having no role (ie parent_id = empty).

As always, it's not necessary better suited to your exact requirements, and is why it's a suggestion/info :)

Upvotes: 0

Kenny
Kenny

Reputation: 5410

Make sure that during $sql2 you are selecting all the children where the parent is the sequence (=id) of the top menu item that you are currently in?:

$sql2= 'SELECT * from menu where parent_id = "'.$result['sequence'].'"';

Upvotes: 1

Related Questions