list category titles for a forum

[newbeee]

i have been tried to get 'all' the categorys to be listed no mater what.

 

but have them ordered by the stickypost (high number first) of the category table

and then the comments stickypost field (again high number first)

and then by the created_on in the comments (most recent date first - yyyy-mm-dd).

 

if there are no comments or topics in the category then the categories still show.

 

all of the topics or comments should have their 'confirmed' fields = 1, if not then these are disgarded and not used in the query.

 

 

 

here are my tables...

CREATE TABLE IF NOT EXISTS `forum_category` (
  `category_id` bigint(20) NOT NULL auto_increment,
  `confirmed` text,
  `stickypost` tinyint(4) NOT NULL default '0',
  `category` text NOT NULL,
  `created_by` text NOT NULL,
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4;

INSERT INTO `forum_category` (`category_id`, `confirmed`, `stickypost`, `category`, `created_by`, `created_on`) VALUES
(1, '1', 1, 'General', 'Jason (admin)', '2009-01-30 00:00:00'),
(2, '1', 0, 'Your stories', 'Jason (admin)', '2009-01-30 00:16:57'),
(3, '1', 0, 'Something else', 'Jason (admin)', '2009-01-30 00:27:59');



CREATE TABLE IF NOT EXISTS `forum_topics` (
  `topic_id` bigint(20) NOT NULL auto_increment,
  `category_id` bigint(20) NOT NULL default '0',
  `topic` longtext NOT NULL,
  `username` text NOT NULL,
  `stickypost` tinyint(4) NOT NULL default '0',
  `created_by` text NOT NULL,
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `confirmed` text,
  `views` bigint(20) NOT NULL default '0',
  KEY `topic_id` (`topic_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


CREATE TABLE IF NOT EXISTS `forum_comments` (
  `postcounter` bigint(20) NOT NULL auto_increment,
  `topic_id` bigint(20) NOT NULL default '0',
  `comment` longtext NOT NULL,
  `username` varchar(65) NOT NULL default '',
  `stickypost` tinyint(4) NOT NULL default '0',
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `confirmed` text,
  KEY `postcounter` (`postcounter`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

 

this is the query i have come up with so far and the fields i need available in the results.

i know i have used the long way to query but this will help me understand the query so i can hopefully learn to do this myself one day.

SELECT
`forum_category`.`category_id`,
`forum_category`.`category`,
`forum_category`.`stickypost`,
`forum_category`.`confirmed`,

`forum_topics`.`category_id`,
`forum_topics`.`topic_id`,
`forum_topics`.`confirmed`,
`forum_topics`.`views`,

`forum_comments`.`postcounter`,
`forum_comments`.`topic_id`,
`forum_comments`.`created_on`,
`forum_comments`.`username`,
`forum_comments`.`confirmed`

FROM
forum_category WHERE `forum_category`.`confirmed` = '1'  ,

JOIN forum_topics WHERE `forum_topics`.`confirmed` = '1',
JOIN forum_comments WHERE `forum_comments`.`confirmed` = '1'

ORDER BY `forum_category`.`stickypost` DESC, `forum_comments`.`created_on` DESC

[fenway]

This seems awfully similar to your other topic... what's different here