PHP / MYSQL CMS - Logic Help

[adammc]

Hi guys,

I am in the process of extending my CMS application that I offer clients.

One client in particular wants to be able to add their own pages and subpages which will also show on the css menu / sub menu.

I cant get my head around how best to organise the DB / process.

I have built 2 tables for the menu item names:

CREATE TABLE `Menu` (
`menu_id` int(11) NOT NULL auto_increment,
`menu_name` varchar(80) default NULL,
`type` varchar(20) NOT NULL default '',
PRIMARY KEY (`menu_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;

CREATE TABLE `MenuSub` (
`sub_menu_id` int(11) NOT NULL auto_increment,
`menu_id` mediumint(8) unsigned NOT NULL default '0',
`sub_menu_name` varchar(80) default NULL,
PRIMARY KEY (`sub_menu_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;

Now.... My problem is to how best create the table which houses the content (html) for each of these menu's pages.

CREATE TABLE `Pages` (

Any advice would be GREATLY appreciated :)

[adammc]

Can anyone help?

[Sagaris]

The table for storing the HTML pages needs to have some way of identifying unique pages (a page id that auto increments for example) as a primary key, and a field for storing the HTML at a bare minuimum - you may wish to add in other data such as publish date etc but let's cover the basics first!

You then need to modify your menu tables to include a field in the menu tables that contain the relevant page id kept in the pages table.

You may find that it would be easier to create a table that contains the page html as well as the information about whether it is a main menu or sub menu page, the menu name etc and then use some programming logic to create the menu based on whether the page should be displayed in the main menu or the sub menu etc.

[adwatson]

If you're going to tie one page to each sub menu item, this seems like it'd work (unless I'm missing something)

create table pages (
page_id int(11) not null autoincrement,
sub_menu_id int(8) not null,
page_title char(255),
page_content text,
primary key (page_id)
);

So each page is tied to a sub menu item by the sub_menu_id field. Your php would just take page_id as a parameter, and do a database lookup to grab the page's content and the menu info.