- listing and category is a many to many relationship meaning listings
can have multiple categories, and categories can have multiple listings
and this is why listing2category is used
- feel free to change indexes if you need to but give reasons why
- below are sample queries needed
CREATE TABLE `item2cat` (
`item_id` int(11) unsigned NOT NULL default '0',
`cat_id` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`item_id`,`cat_id`),
KEY `cat_id_2` (`cat_id`,`item_id`)
) TYPE=MyISAM;
CREATE TABLE `category` (
`cat_id` mediumint(8) unsigned NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`description` text NOT NULL,
`p` mediumint(8) unsigned default NULL,
PRIMARY KEY (`cat_id`),
KEY `p` (`p`)
) TYPE=MyISAM;
CREATE TABLE `item` (
`item_id` mediumint(8) unsigned NOT NULL auto_increment,
`approved` tinyint(1) NOT NULL default '1',
`title` varchar(100) NOT NULL default '',
`membership` tinyint(2) NOT NULL default '1',
`expires` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`item_id`),
KEY `membership` (`membership`),
KEY `date_expire` (`date_expire`,`admin_appr
oved`),
FULLTEXT KEY `firmname` (`title`)
) TYPE=MyISAM;
- Select all items in a given category that have not expired (expires <NOW()) and are approved (approved=1)
and order by the membership type (higher memberships show first) then limit in order to page results
- Get a result set containing 2 columns, cat_id, and item_count. item_count is a sum of the items in cat_id and all
of cat_id's children.
- We want to display 2 levels of the category on a single page at a time showing the main category name
followed by its children. Beside both the category name and the children name a number which is the item count for that category.
So we need 1 or 2 queries that can get the information needed to display this. Tedious/slow PHP code should not be required to
display the tree from the result set. We would need to set the root cat_id (NULL for top level) and the query could get the corresponding data.
