hi,
I'm trying to build a simple bulletin board, just topics and posts.
created following tables:
CREATE TABLE `topics` (
`topic_id` int(8) NOT NULL auto_increment,
`author_id` int(8) unsigned NOT NULL,
`topic_title` varchar(255) collate utf8_unicode_ci default NULL,
`topic_date_entered` datetime default NULL,
`topic_status` enum('live','hidden','locked') collate utf8_unicode_ci
NOT NULL default 'live',
PRIMARY KEY (`topic_id`),
KEY `topic_author` (`topic_author`),
KEY `topic_date_entered` (`topic_date_entered`),
KEY `topic_status` (`topic_status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=3041 ;
CREATE TABLE `posts` (
`post_id` int(4) unsigned NOT NULL auto_increment,
`topic_id` varchar(255) NOT NULL,
`author_id` int(8) unsigned NOT NULL,
`post_date` datetime NOT NULL,
`content` text,
PRIMARY KEY (`post_id`),
KEY `topic_id` (`topic_id`),
KEY `post_date` (`post_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3417 ;
CREATE TABLE `authors` (
`author_id` int(8) unsigned NOT NULL auto_increment,
`Username` varchar(20) NOT NULL default '',
`Password` varchar(20) NOT NULL default '',
`Name` varchar(30) NOT NULL default '',
`Date_Reg` varchar(30) NOT NULL default '',
PRIMARY KEY `Username` (`Username`),
KEY `Username` (`Salesperson_No`),
KEY `Password` (`Password`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I was trying to create the query but wasn't successful.
only thinig I did for now is
$query = mysql_query("
SELECT t.topic_id, t.topic_title, t.author_id,
t.topic_date_entered, a.Name
FROM topics as t
LEFT JOIN authors as a ON (t.author_id=a.author_id)
WHERE t.topic_status = 'live'
ORDER BY t.topic_id DESC
LIMIT 25");
while($result = mysql_fetch_array($query))
{
$query2 = mysql_query("
SELECT COUNT(*) AS counter, MAX(post_date) as post_date,
MAX(post_id) as post_id, author_id
FROM posts
WHERE topic_id = $result['topic_id']
GROUP BY topic_id");
while($result2 = mysql_fetch_array($query2))
{
echo '<b>'.$result['topic_title'].'</b> by
'.$result['Name'].' '.$result2['counter'].'
'.$result2['post_date'].'<br>'; # shortened version
}
}
and, of course, it's wrong.
want to have on topics listing: topic's title | author's name | no. of
replies | last post date | last post autor's name.
Thanks for any help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
