sorting parent/child in one query?

xwin

(mysql btw)

Hi, I'm wondering how I can sort parent and children into an html table with just one query...

I have a table, lets say it has ID, parentID, listPOSITION, and data values

ID = id of entry (1,2,3,...)
parentID = id of parent (1,2,3,...)
listPOSITION = position under parent (1,2,3,...)
data = some text

how can I group them when extracting so my table shows up something like

parent1
---p1child1
---p1child2
---p1child3
parent2
---p2child1

...basically so children go under their parents and I can differentiate between parent and children cells for styling. Is this possible with just one query SELECT * FROM table and some clever looping?

If not, anyone know the next best way?

I know how to do it with many many queries, but want to be as efficient as possible.

please point me in the right direction... thanks!🙂

bradgrafelman

Are parent and child entries in separate tables? If not, why? How do you tell which row is a parent entry and which is a child? Have you normalized your DB design?

xwin

hi, thanks for the reply

No, all are in the same table because the eventual idea is to allow unlimited nesting of parent/child combinations if I can ever figure out how. Root parents will have a blank parentID

NogDog

Untested:

SELECT t1.ID, t1.data, t2.ID, t2.data
FROM table_name AS t1
LEFT JOIN table_name AS t2 ON t1.ID = t2.parentID
ORDER BY t1.ID, t2.listPOSITION

xwin

hi, I think this is what I'm trying to do - http://www.ideashower.com/solutions/create-a-parent-child-array-structure-in-one-pass/ -- however it doesn't explain how to display the data in heirarchical form... could someone help me out?🙂

$refs = array();
$list = array();

$sql = "SELECT item_id, parent_id, name FROM items ORDER BY name";
$result = mysql_query($sql);
while($data = @mysql_fetch_assoc($result)) {
    $thisref = &$refs[ $data['item_id'] ];

$thisref['parent_id'] = $data['parent_id'];
$thisref['name'] = $data['name'];

if ($data['parent_id'] == 0) {
    $list[ $data['item_id'] ] = &$thisref;
} else {
    $refs[ $data['parent_id'] ]['children'][ $data['item_id'] ] = &$thisref;
}
}

thanks for any help!