Oke... I finally have a question to ask again! YAY!
here's the deal... working on a "bulletin board" type system, although its not for PHP, it will be accessible via PHP in the near future (if I ever finish writing it :p)
One of the functions I have has to return all the information on a particular board (# of boards is whatever mySQL can handle really) and I can currently get EVERYTHING but 1 item in 1 query... and was curious if maybe I am overlooking a simple way to roll them all into 1....
SELECT b.boardDesc AS `desc` , b.boardOwners AS owners, b.flagAnonymous AS fAnon, b.flagOwnerRemove AS fRemove, b.flagOwnerRead AS fRead, b.flagOwnerPost AS fPost, count(p.boardID) AS note_count
FROM tblBoards AS b, tblBoardPosts AS p
WHERE b.boardID =1 GROUP BY b.boardID LIMIT 1
obviously the ID=1 is just the test value I was using since I only have 1 board currently
what this returns....
desc -> "some text to describe the purpose of the board"
owners->"comma, delimted, string, listing, the, owners"
fAnon->0/1 //makes posters names invisible or visible
fRemove->0/1 //only owners can remove posts
fRead->0/1 //only owners can read/see posts
fPost->0/1 //only owners can post
note_count-># total notes for that board
what I need...
last_note-># ID of last note posted for that board....
current DB schema (and its probably way off base for what I really want...)
#
# Table structure for table `tblBoardPosts`
#
CREATE TABLE `tblBoardPosts` (
`boardID` int(11) NOT NULL default '0',
`boardPostID` int(11) NOT NULL default '0',
`parentID` int(11) NOT NULL default '0',
`postAuthor` varchar(16) NOT NULL default '',
`postDate` int(11) NOT NULL default '0',
`postTitle` varchar(50) NOT NULL default '',
`postBody` text,
FULLTEXT KEY `title` (`postTitle`,`postBody`)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `tblBoards`
#
CREATE TABLE `tblBoards` (
`boardID` int(11) NOT NULL auto_increment,
`boardName` varchar(16) NOT NULL default '',
`boardDesc` text,
`boardOwners` varchar(50) NOT NULL default 'Pidge, Rantic, Tekky',
`flagAnonymous` tinyint(4) NOT NULL default '0',
`flagOwnerRemove` tinyint(4) NOT NULL default '0',
`flagOwnerRead` tinyint(4) NOT NULL default '0',
`flagOwnerPost` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`boardID`),
UNIQUE KEY `boardName` (`boardName`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;
in the end... I want to be able to TREE replies but currently, the display method for that escapes me due to wanting to be able to delete notes from the board too.... (what happens if you delete a parent note... etc...)
Thanks in advance for any help... 🙂
