[SOLVED] Getting menu and child links with one query

[jordanwb]

I have the following two classes in my database:

 

CREATE TABLE IF NOT EXISTS `scm_menus` (
  `menu_id` int(11) NOT NULL auto_increment,
  `menu_name` varchar(32) NOT NULL,
  PRIMARY KEY  (`menu_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `scm_menu_links` (
  `link_id` int(11) unsigned NOT NULL auto_increment,
  `link_name` varchar(32) NOT NULL,
  `link_href` text NOT NULL,
  `link_target` varchar(16) NOT NULL default '_self',
  `link_parent` int(10) unsigned NOT NULL,
  `link_order` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`link_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

Now the field "link_parent" in scm_menu_links refers to "menu_id" in scm_menus. I also want the links to be ordered by "link_order" ascending. I want to store all links in an array like so:

 

$menus['menu_id']['name'] = {Content of 'menu_name'}

$menus['menu_id']['links'][0] = {First Link}

$menus['menu_id']['links'][1] = {First Link}

 

 

I hope what I'm asking makes sense because I don't know how to ask the question. 

[toplay]

Try a query similar to this:

 

select

          menu_name

        , link_name

        , link_href

        , link_target

 

from  scm_menus

join    scm_menu_links

on      link_parent = menu_id

 

order by  link_order ASC

 

 

Just read each row from the result to build the array however you want it to be.

 

Note: The link_parent should be the same size as the menu_id. One should not be int(10) and the other int(11) (and whether negative allowed or not).

 

 

 

[jordanwb]

Sweet it works. Thanks. I forgot ordering my menu_id but I know how to add that.