Insight required pertaining to displaying levels of categories

[saint4]

Hi,

I'm coding a marketplace and I need insight how to best let a logged in user select multiple categories their job falls under. The categories may have sub categories and them sub categories may have their own sub categories and so forth.

I have no issues with displaying main categories with a checkbox (no subcategories). You can check as many categories as required.

Alternatively, if the main categories have sub categories then I have no problems with displaying main categories as a heading and the sub categories as selectable checkboxes. You can check as many sub categories as required. Once complete the code will also add the main category into the array.

The issue arises, if I decided to have more than two levels of categories.

As an example; say this is our tree of categories (Includes all its sub categories):

Programming and Databases -> Web Development -> Web Design -> Graphic Design -> Logo Design

Programming and Databases -> Web Development -> Programming -> PHP

Marketing & Communications -> Market Research

Admin Support

If I was posting a project and I need to select Logo Design, PHP, Market Research and Admin Support as categories the job falls under. What is the best way to do this?

I've looked at ebay for reference, but you can only select two categories. You select the first one and then click a link to set a second.
This can be time consuming if there's 6,7,8 categories you want to file the project under.

I might be overlooking something but I can't see the answer. What is the best way to tackle this?

[Derokorian]

I would use one table for all categories, with optional parent_id, as such:

CREATE TABLE `categories` ( 
   `id` INT unsigned NOT NULL AUTO_INCREMENT, 
   `parent_id` INT NULL, 
   `title` varchar(75) NOT NULL, 
   PRIMARY KEY(`id`),
   KEY `parent` (`parent_id`)
);

-- Insert your sample data
INSERT INTO `categories` (`id`,`parent_id`,`title`) VALUES
(1,NULL,'Programming and Databases'),
(2,1,'Web Development'),
(3,2,'Web Design'),
(4,3,'Graphic Design'),
(5,4,'Logo Design'),
(6,2,'Programming'),
(7,6,'PHP'),
(8,NULL,'Marketing & Communications'),
(9,8,'Market Research'),
(10,0,'Admin Support');

Then I would use a many-to-many matching table to represent the choices, below is a basic posting table, and the matching table:

CREATE TABLE `postings` (
   `id` INT unsigned NOT NULL AUTO_INCREMENT,
   `title` varchar(75) NOT NULL,
   `desc` TEXT NOT NULL,
   PRIMARY KEY(`id`)
);

-- Insert the posting
INSERT INTO `postings` (`id`,`title`,`desc`) VALUES (1,'Saint4''s Sample Job','Just a sample posting to illustrate');

CREATE TABLE `posting_category` (
   `posting_id` INT unsigned NOT NULL,
   `category_id` INT unsigned NOT NULL,
   KEY `post_cat` (`posting_id`,`category_id`)
);

-- Insert the picks for the posting
INSERT INTO `posting_category` (`posting_id`,`category_id`) VALUES
(1,5),
(1,7),
(1,9),
(1,10);

Then finding the selected categories is as simple as issuing a joined select such as

SELECT  `id` FROM  `categories` c LEFT JOIN  `posting_category` pc ON pc.`category_id` = c.`id` WHERE pc.`posting_id` =1 

then when looping thru the categories to create the check boxes, you simply check if that categories ID is in the result set. Hope this gives you a good idea of how I'd do it. If you have more questions feel free to ask I'm sure I didn't explain what I'm thinking very well!

[saint4]

Thanks for your help.

I've already setup the database, it's not the database I need help with.

I'm merely picking brains for the best way to display to the user a way they can select categories within one category and then be able to select a subcategory from other categories.