Relational table - return all parents

[darcuss]

Hi

 

I'd appreciate help if anyone can spare me their valuable time.

 

I have a table of relations that refer to another table of items. The relations describes items, categories and subcategories of varying depth.

 

Something like this:

+--------+-------+

| parent | child |

+--------+-------+

|      1 |    2 |

|      1 |    3 |

|      2 |    4 |

|      2 |    5 |

|      2 |    6 |

|      2 |    7 |

|      2 |    8 |

|      3 |    9 |

|      3 |  10 |

|      3 |  11 |

|      4 |  12 |

|      4 |  13 |

+--------+-------+

relates to something like this

+--------+-------+

| id    | item |

+--------+-------+

|      1 |    item1 |

|      2 |    item2 |

|      3 |    item3 |

|      4 |    item4 |

|      5 |    item5 |

|      6 |    item6 |

 

and so on...

 

I need a query that returns all of the ancestors of an item. For example, to query '4' it would return:

+--------+-------+

| id    |    item  |

+--------+-------+

|      1 |    item1 |

|      2 |    item2 |

+--------+-------+

 

Hopefully this is clear and someone can save my brain from more stress today. Thanks very much in advance!

[veridicus]

The only way to do it in one query is if you have a hard set limit on depth.  Otherwise you would need application code or a stored procedure to continuously loop as it queries for each parent.

[rkrass]

cant do it in a single query.

heres the basic code. not sure where i got this from, but does the job

(ull have to modify to fit your specific issue)

$nav_query = mysql_query("SELECT * FROM `table` ORDER BY `category_id`");
$tree = "";                                     // Clear the directory tree
$depth = 1;                                     // Child level depth.
$top_level_on = 1;                      // What top-level category are we on?
$exclude = array();                     // Define the exclusion array
array_push($exclude, 0);        // Put a starting value in it
$tree = "<select name='cat1'><option value=''></option>";
while ( $nav_row = mysql_fetch_array($nav_query) )
{
        $goOn = 1;                      // Resets variable to allow us to continue building out the tree.
        for($x = 0; $x < count($exclude); $x++ )                // Check to see if the new item has been used
        {
                if ( $exclude[$x] == $nav_row['category_id'] )
                {
                        $goOn = 0;
                        break;                          // Stop looking b/c we already found that it's in the exclusion list and we can't continue to process this node
                }
        }
        if ( $goOn == 1 )
        {
                $tree .= "<option value='".$nav_row['category_id']."'>".$nav_row['title'] . "</option>";                            // Process the main tree node
                array_push($exclude, $nav_row['category_id']);          // Add to the exclusion list
                if ( $nav_row['category_id'] < 6 )
                { $top_level_on = $nav_row['category_id']; }

                $tree .= build_child($nav_row['category_id']);          // Start the recursive function of building the child tree
        }
}
$tree .= "</select>";
function build_child($oldID)                    // Recursive function to get all of the children...unlimited depth
{
        global $exclude, $depth;                        // Refer to the global array defined at the top of this script
        $child_query = mysql_query("SELECT * FROM `table` WHERE parent_id=" . $oldID);
$tempTree = "";
        while ( $child = mysql_fetch_array($child_query) )
        {
                if ( $child['category_id'] != $child['parent_id'] )
                {
		$tempTree .= "<option value='".$child['category_id']."'>";
                        for ( $c=0;$c<$depth;$c++ )                     // Indent over so that there is distinction between levels
                        { $tempTree .= " "; }
                        $tempTree .= "- " . $child['title'] . "</option>";
                        $depth++;               // Incriment depth b/c we're building this child's child tree  (complicated yet???)
                        $tempTree .= build_child($child['category_id']);                // Add to the temporary local tree
                        $depth--;               // Decrement depth b/c we're done building the child's child tree.
                        array_push($exclude, $child['category_id']);                    // Add the item to the exclusion list
                }
        }

        return $tempTree;               // Return the entire child tree
}

[darcuss]

Cheers guys. Was kinda hoping there would be some swish query but i was expecting it not to be possible.

 

 

[fenway]

Cheers guys. Was kinda hoping there would be some swish query but i was expecting it not to be possible.

It would be possible if you used a list adjacency model instead of a hierarchy.

[darcuss]

Cheers for the tip, looking into that now.

[darcuss]

Hi again

 

I looked into this and while it looks great, I wonder what the implications are where users input data. The re-enumeration of the right node values must make a heavy hit on large databases, is this something to worry about? Maybe there's an alternative that is better where frequent user input is involved?

 

Thanks again for advice.

[fenway]

The only other option is a stored procedure.