How to query all detail rows of all sub-hierarchy of all root rows?

Question

There are two tables : one is the master and one the detail ( foreign key ). In the master table there is a hierarchy structure : each master row has its parent hierarchy ( self-foreign key ). Perhaps the DML of the tables can explain it clearly :

CREATE TABLE classe_menu (                          
               class_menu_code int(10) NOT NULL auto_increment,  
               class_menu_lib varchar(50) default NULL,          
               class_menu_comment text,                          
               class_menu_deleted tinyint(1) default '0',        
               class_menu_ordre int(11) default NULL,            
               class_menu_parent int(10) default NULL,           
               PRIMARY KEY  (class_menu_code) ,                                                                                   
               KEY association_108_fk (class_menu_parent),                                                                 
               CONSTRAINT fk_association_108 FOREIGN KEY (class_menu_parent) REFERENCES classe_menu (class_menu_code)                   
             ) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE menu (                                                                                           
          menu_code int(10) NOT NULL auto_increment,                                                                  
          class_menu_code int(10) default NULL,                                                                       
          menu_lib varchar(100) default NULL,                                                                         
          menu_url varchar(255) default NULL,                                                                         
          menu_titre varchar(100) default NULL,                                                                       
          menu_parent decimal(10,0) default NULL,                                                                     
          menu_visible tinyint(1) default NULL,                                                                       
          menu_ordre decimal(2,0) default NULL,                                                                       
          menu_action varchar(50) default NULL,                                                                       
          menu_icone_img varchar(255) default NULL,                                                                   
          menu_icone_flag tinyint(1) default '0',                                                                     
          menu_icone_title varchar(50) default NULL,                                                                  
          menu_deleted tinyint(1) default '0',                                                                        
          menu_tooltip varchar(25) default NULL,                                                                      
          menu_trace text,                                                                                            
          menu_image varchar(255) default NULL,                                                                       
          menu_contextuel tinyint(1) default '0',                                                                     
          menu_logo varchar(255) default NULL,                                                                        
          menu_display int(1) default '0',                                                                            
          PRIMARY KEY  (menu_code),                                                                                   
          KEY association_109_fk (class_menu_code),                                                                 
          CONSTRAINT fk_association_109 FOREIGN KEY (class_menu_code) REFERENCES classe_menu (class_menu_code)  
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='liste des menus du site'

As you can see 'classe_menu' is the master table and 'menu' is the detail table ; but inside the master table 'classe_menu' there is also a hierarchy structure implemented by the 'class_menu_parent' column : a 'classe_menu' row is a root of the hierarchy if its 'class_menu_parent' is NULL.

Now what I want is to get all 'classe_menu' root rows and the 'menu' rows for all 'classe_menu' , that is for all root 'classe_menu' and all of its children.

How to do that ?

You want just the root (NULL class_menu_parent) classe_menu record, and ALL the menu rows for that record PLUS for any other classe_menu records that are under that root item's hierarchy, correct? — Sean, Jul 05 '12 at 07:19
How do you want the data returned - seeing as it's a one-to-many relationship; do you want it all in one resultset, with the classe_menu columns (repeated) on the left, and the menu columns on the right? Or in 2 separate queries? Do you pass in a parameter (class_menu_code), or you want it for ALL root records? Can you use temp tables & cursors, or is this query going to be called frequently and needs to be highly efficient? — Sean, Jul 05 '12 at 07:21
For your first question the answer is yes. For the second one , I want the data to be returned inside a PHP array : the array will have one `class_menu` column argument , say class_menu_lib, and one `menu` column argument, say menu_titre. — pheromix, Jul 05 '12 at 07:26

Braiba · Accepted Answer · 2012-07-05T08:36:08.503

1

function getMenuChildren($code,$code_is_class_menu=false){
    $children = array();
    $sql = 'SELECT * FROM menu WHERE '.($code_is_class_menu?'class_menu_code':'menu_parent').' = '.$code;
    $res = mysql_query($sql);
    while ($row = mysql_fetch_assoc($res)){
        $row['children'] = getMenuChildren($row['menu_code']);
        $children[] = $row;
    }
    return $children;
}

$class_menus = array();
$sql = 'SELECT * FROM classe_menu WHERE class_menu_parent IS NULL';
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res)){
    $row['children'] = getMenuChildren($row['class_menu_code'],true);
    $class_menus[] = $row;
}

print_r($class_menus);

edited Jul 05 '12 at 08:36

answered Jul 05 '12 at 07:35

Braiba

571
3
8

He needs something recursive to get the hierarchical data: http://stackoverflow.com/questions/4345909/mysql-recursion – Sean Jul 05 '12 at 07:38
Ah, couldn't see anything in the question saying the output needed to be recursive. In which case that will require some php code, which while fairly simple is not something I want to be typing out on my phone. I'll edit my answer once I get to work if no-one has beaten me to it. – Braiba Jul 05 '12 at 07:46
@SeanW > are the left and right data to be entered manually ? and what if an update is made on the hierarchy : how to recalculate the left and right values ? – pheromix Jul 05 '12 at 08:06
Edited in some PHP code. Hopefully that should work, but obviously I can't run it without actually setting up the database and everything so let me know if it gives any errors. – Braiba Jul 05 '12 at 08:37
the idea is there but it seems there are some changes to make ! – pheromix Jul 05 '12 at 09:17
What is it that needs changing? If your question has been answered and you just want to make some tweaks yourself for how you want to use the data then you should mark my answer as accepted. Otherwise, let us know what you want to be different so we can help. – Braiba Jul 05 '12 at 09:34

How to query all detail rows of all sub-hierarchy of all root rows?

1 Answers1