Recursive breaking categories

[jkkenzie]

If i have a list of categories like:

1. Billboards and advertisement section

  1(a)City Clock

    1(a)(i)Application fee  (has columns for approved price and proposed price)

    1(a)(ii)Advertisement Per year (has columns for approved price and proposed price)

 

  1(b)Billboards

    1(b)(i)Application fee for construction (has columns for approved price and proposed price)

    1(b)(ii)Charge per year without advertisement (has columns for approved price and proposed price)

 

  1©Something here.....

    1©(i) something here...(has columns for approved price and proposed price)

    1©(ii)something here...(has columns for approved price and proposed price)

    1©(iii) something here... (has columns for approved price and proposed price)

    e.t.c

 

THEN SOME CATEGORIES LOOK LIKE:category 2

2. Category name

  2(a)something here..(has columns for approved price and proposed price)

  2(b)something here..

    2(b)(i)something here..(has columns for approved price and proposed price)

    2(b)(ii)something here..(has columns for approved price and proposed price)

 

 

 

NOW: i have categories without sub categories and others with sub-sub categories , a main category doesn't does have a column for prices ( or lets just say other columns) , a sub category WITHOUT its sub category will have columns for prices, if a sub category has its sub category, then its subcategory should have columns for prices (this is where my problem is - how do i handle such a situation in php Mysql?? )

[Muddy_Funster]

would need to see your table design (prefferably with a couple of lines of sample data too)

[jkkenzie]

I have realised this is a Tree Data Structure with some siblings not complete i.e others are long and others are short. It goes to the fourth sibling.

I have just structed the db as below: it seems i will need to add a table for every Level (children of the same line) of the tree:

 

CREATE TABLE IF NOT EXISTS `price_category` (
  `act_code` int(20) NOT NULL,
  `name` text NOT NULL,
  PRIMARY KEY  (`act_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `price_sub_category`
--

CREATE TABLE IF NOT EXISTS `price_sub_category` (
  `id` int(11) NOT NULL auto_increment,
  `act_code` int(20) NOT NULL,
  `name` text NOT NULL,
  `aprovedp1` int(20) default NULL,
  `aprovedp1y` char(20) default NULL,
  `aprovedp2` int(20) default NULL,
  `aprovedp2y` char(20) default NULL,
  `proposedp` int(20) default NULL,
  `proposedpy` char(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `price_sub_sub_category`
--

CREATE TABLE IF NOT EXISTS `price_sub_sub_category` (
  `id` int(11) NOT NULL auto_increment,
  `act_code` int(20) NOT NULL,
  `sub_code` int(20) NOT NULL,
  `name` text NOT NULL,
  `aprovedp1` int(20) default NULL,
  `aprovedp1y` char(20) default NULL,
  `aprovedp2` int(20) default NULL,
  `aprovedp2y` char(20) default NULL,
  `proposedp` int(20) default NULL,
  `proposedpy` char(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;