forum query

[jasonc310771]

I have slowly been creating my own form all seem to be working so far and i have just to add the new lines of queries to show when each category was last updated.

below are the cutdown versions of my forum DB's

what i am after is a way to get the contents of the field 'created_on' from the `forum_topics` DB.

but for each of the categorys,

the categorys are shown and then this new query i need, needs to use the category id on showing the categorys to then look at the most recent post made in the `forum_comments` which is first referenced in the `forum_topics`

oh BTW i can not just add a new field to place this info in the the forum_category table as this would show what could be a deleted posts details and would still need to get a query to find the date of the last post anyway!

so i think this method i what i am after..
(excuse the crude method!)

select 'created_on' in `forum_comments` where 'topic_id' in `forum_comments` = 'topic_id' in `forum_topics` and 'category_id' in `forum_topics` = 'category_id' in `forum_category` and where `confirmed' in all tables = '1'

i think this is correct been over it a few time to make sure it is what i believe is needed of the query.

my Mysql tables

CREATE TABLE IF NOT EXISTS `forum_category` (
`category_id` bigint(20) NOT NULL auto_increment,
`confirmed` text,
`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 ;

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,
`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=38 ;

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 '',
`created_on` datetime NOT NULL default '0000-00-00 00:00:00',
`confirmed` text,
KEY `postcounter` (`postcounter`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=54 ;

[Fumigator]

Was there a question in there somewhere?
:confused:

[guelphdad]

Please show some representative rows of your tables and the expected output you are trying to achieve.

[jasonc310771]

CREATE TABLE IF NOT EXISTS `forum_category` (
`category_id` bigint(20) NOT NULL auto_increment,
`confirmed` text,
`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 ;

--
-- Dumping data for table `forum_category`
--

INSERT INTO `forum_category` (`category_id`, `confirmed, `category`, `created_by`, `created_on`) VALUES
(1, '1', 'Your stories', 'Admin', '0000-00-00 00:00:00'),
(2, '1', 'Something else', 'jason', '0000-00-00 00:00:00'),
(3, '1', 'Some other random category', 'Admin', '0000-00-00 00:00:00');

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

--
-- Table structure for table `forum_comments`
--

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 '',
`created_on` datetime NOT NULL default '0000-00-00 00:00:00',
`confirmed` text,
KEY `postcounter` (`postcounter`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=54 ;

--
-- Dumping data for table `forum_comments`
--

INSERT INTO `forum_comments` (`postcounter`, `topic_id`, `comment`, `username`, `created_on`, `confirmed`) VALUES
(53, 37, 'mmm', 'Jason', '2008-12-08 21:51:00', '1'),
(52, 36, '22222222222222', 'Jason', '0000-00-00 00:00:00', '1');

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

--
-- Table structure for table `forum_topics`
--

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,
`created_on` datetime NOT NULL default '0000-00-00 00:00:00',
`confirmed` text,
KEY `topic_id` (`topic_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=38 ;

--
-- Dumping data for table `forum_topics`
--

INSERT INTO `forum_topics` (`topic_id`, `category_id`, `topic`, `username`, `created_on`, `confirmed`) VALUES
(36, 2, '2', 'Jason', '2008-12-08 21:42:39', '0'),
(37, 2, 'mmmmmm', 'Jason', '2008-12-08 21:51:00', '1');



the expected results need to be if possible...

the results string would need to have all the results for each of the categorys last post made which is stored in the comments table

just like codingforums.com...
http://www.codingforums.com/index.php
shows the most recent posters username and the date and time of the post made in the comments