Acorn Arcade forums: Programming: SQL puzzle
| |
SQL puzzle |
| |
AndrewD (17:58 28/5/2005)
Phlamethrower (18:50 28/5/2005)
AndrewD (19:00 28/5/2005)
rich (19:03 28/5/2005)
AndrewD (19:11 28/5/2005)
rich (20:17 28/5/2005)
AndrewD (20:22 28/5/2005)
rich (20:27 28/5/2005)
Phlamethrower (21:07 28/5/2005)
|
| |
|
Andrew Duffell |
Message #66000, posted by ad at 17:58, 28/5/2005 |
Posts: 3262
|
I've written some forum software that I use on a website of mine, and have a problem with displaying the forum list in order.
SELECT * FROM forum_forums WHERE userlevel<=".$user_data[userlevel]."
Table forum_posts contains: date, forumid, topicid, posterid, message I'm wanting the SELECT above to order by the date of the last post in that forum, so that the forum with the most recent post is a t the top of this list.
Completly lost as to how to do it... any suggestions?
If you need any more details about the database structure, please say.
Cheers,
Andrew |
| |
 [ Log in to reply ] |
| |
|
Jeffrey Lee |
Message #66001, posted by Phlamethrower at 18:50, 28/5/2005, in reply to message #66000 |
 Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot stuff
Posts: 15100
|
I hope you're self-taught, otherwise whoever taught you obviously didn't do a very good job 
ORDER BY |
| |
[ Log in to reply ] |
| |
|
Andrew Duffell |
Message #66002, posted by ad at 19:00, 28/5/2005, in reply to message #66001 |
Posts: 3262
|
I hope you're self-taught, otherwise whoever taught you obviously didn't do a very good job
I am.
ORDER BY
Well I know that much since I use it all the time. Problem is that I want to order the forums by the dates of the posts which are held in a different table. |
| |
[ Log in to reply ] |
| |
|
Richard Goodwin |
Message #66003, posted by rich at 19:03, 28/5/2005, in reply to message #66002 |
 Dictator for life
Posts: 6828
|
Well I know that much since I use it all the time. Problem is that I want to order the forums by the dates of the posts which are held in a different table. In that case it sounds like you've designed the database wrong
How about ordering it by ID, which should be similarly chronological?
________
 Cheers,
Rich.
|
| |
[ Log in to reply ] |
| |
|
Andrew Duffell |
Message #66007, posted by ad at 19:11, 28/5/2005, in reply to message #66003 |
Posts: 3262
|
-- --------------------------------------------------------
--
-- Table structure for table `forum_forums`
--
CREATE TABLE `forum_forums` (
`fid` int(11) NOT NULL auto_increment,
`name` varchar(30) default NULL,
`description` text,
`userlevel` int(11) NOT NULL default '0',
`hidden` int(11) NOT NULL default '0',
PRIMARY KEY (`fid`),
UNIQUE KEY `name` (`name`)
) TYPE=MyISAM COMMENT='This is the table for the forum list i.e General, News, Sugg' AUTO_INCREMENT=25 ;
-- --------------------------------------------------------
--
-- Table structure for table `forum_messages`
--
CREATE TABLE `forum_messages` (
`mid` int(11) NOT NULL auto_increment,
`fromuid` int(11) default NULL,
`touid` int(11) default NULL,
`message` text,
`date` varchar(35) default NULL,
`messageread` int(11) default NULL,
PRIMARY KEY (`mid`)
) TYPE=MyISAM AUTO_INCREMENT=244 ;
-- --------------------------------------------------------
--
-- Table structure for table `forum_posts`
--
CREATE TABLE `forum_posts` (
`spid` int(11) NOT NULL auto_increment,
`pid` int(11) default NULL,
`fid` int(11) default NULL,
`posterid` int(11) default NULL,
`message` text,
`date` varchar(32) default NULL,
`file` blob,
PRIMARY KEY (`spid`)
) TYPE=MyISAM COMMENT='Post posts... if you get my drift  ' AUTO_INCREMENT=1071 ;
-- --------------------------------------------------------
--
-- Table structure for table `forum_topics`
--
CREATE TABLE `forum_topics` (
`pid` int(11) NOT NULL auto_increment,
`fid` int(11) default NULL,
`topicname` varchar(30) default NULL,
`locked` int(11) NOT NULL default '0',
`date` varchar(32) NOT NULL default '2004-01-01 00:00:00',
`type` int(11) NOT NULL default '10',
PRIMARY KEY (`pid`)
) TYPE=MyISAM COMMENT='This table contains the topic details' AUTO_INCREMENT=129 ;
-- --------------------------------------------------------
--
-- Table structure for table `forum_userdata`
--
CREATE TABLE `forum_userdata` (
`uid` int(11) NOT NULL auto_increment,
`name` varchar(15) default NULL,
`pass` varchar(32) default NULL,
`email` varchar(40) default NULL,
`online` bigint(14) default NULL,
`userlevel` int(11) NOT NULL default '0',
`realname` varchar(64) default NULL,
`location` varchar(32) NOT NULL default '',
`mobilenumber` varchar(12) NOT NULL default 'n/a',
`address` text NOT NULL,
`sig` varchar(64) default NULL,
PRIMARY KEY (`uid`),
UNIQUE KEY `uid` (`uid`,`name`)
) TYPE=MyISAM PACK_KEYS=0 COMMENT='Table for forum at [url removed]' AUTO_INCREMENT=43 ;
-- --------------------------------------------------------
--
-- Table structure for table `forum_userlevel`
--
CREATE TABLE `forum_userlevel` (
`level` int(11) NOT NULL default '0',
`name` varchar(10) NOT NULL default '',
PRIMARY KEY (`level`)
) TYPE=MyISAM; |
| |
[ Log in to reply ] |
| |
|
Richard Goodwin |
Message #66008, posted by rich at 20:17, 28/5/2005, in reply to message #66007 |
Dictator for life
Posts: 6828
|
Hmm... I know how I'd probably kludge it if the DB can't be changed, but it's not very SQL-y. Read the latest date for each forum; convert it into something useful (e.g. "20050528210931"); add "--foo" on the end, where foo IDs the forum (fid?); put all of said hashes into an array; sort alphabetically; extract the ID of the forum back out of each item in the array. That's how I had to do everything when I used flat file databases.
To do it properly I'd probably modify the DB to cache a copy of the last date something was posted to each forum if you know you're going to be using it. i.e. when someone edits a post, you write the date field to the forum_posts table as normal, and you cache a copy of that date to the relevant forum_forums field. Example:
CREATE TABLE `forum_forums` (
`fid` int(11) NOT NULL auto_increment,
`name` varchar(30) default NULL,
`description` text,
`userlevel` int(11) NOT NULL default '0',
`hidden` int(11) NOT NULL default '0',
`date_cache` varchar(14) default NULL,
PRIMARY KEY (`fid`),
UNIQUE KEY `name` (`name`)
) TYPE=MyISAM COMMENT='This is the table for the forum list i.e General, News, Sugg' AUTO_INCREMENT=25 ;
(I'm storing the date as a text string e.g. "20050528210931", there's also the proper date format you've used elsewhere, and you even could store Unix time. The reason I choose this way is so I can do maths on it - how long since the last login, how many days until a subscription runs out, and because it's easy to read for a human).
________
Cheers,
Rich.
|
| |
[ Log in to reply ] |
| |
|
Andrew Duffell |
Message #66009, posted by ad at 20:22, 28/5/2005, in reply to message #66008 |
Posts: 3262
|
That is what I have at the moment, but I'm trying to get rid of that field because duplicating things seems bad, and it is a problem when topics are moved from one forum to another.
WRT your other suggestion. It would work, but seems a bit long winded. I'm sure there most be a better way with SQL (sub-queries?) to do it without duplicating values. 
ta!
Andrew |
| |
[ Log in to reply ] |
| |
|
Richard Goodwin |
Message #66010, posted by rich at 20:27, 28/5/2005, in reply to message #66009 |
Dictator for life
Posts: 6828
|
That is what I have at the moment, but I'm trying to get rid of that field because duplicating things seems bad, and it is a problem when topics are moved from one forum to another. It's not bad; you can either cache a copy of something for speed, or do things the long-winded way. Given the whole size of the database when a forum's involved, adding a few extra digits is fairly trivial - you're only adding a few characters per *forum*, not per *post*.
It's not a problem - just make sure you update the forum fields when you make the move. If you're really anal, make the move, and then check for the next latest post in the forum you've moved out of. But really, both forums have seen activity at the time of the move, so you could just cache the time of the move in both.
WRT your other suggestion. It would work, but seems a bit long winded. Which is why I'd modify the database to do the heavy lifting.
I'm sure there most be a better way with SQL (sub-queries?) to do it without duplicating values. Probably, but I'm not that great a database programmer. You might need to yell for Tim's help.
________
Cheers,
Rich.
|
| |
[ Log in to reply ] |
| |
|
Jeffrey Lee |
Message #66011, posted by Phlamethrower at 21:07, 28/5/2005, in reply to message #66010 |
Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot stuff
Posts: 15100
|
How about:
SELECT *
FROM forum_forums
WHERE userlevel<=".$user_data[userlevel]."
AND (SELECT fid, MAX(date)
FROM forum_posts
GROUP BY fid
WHERE forum_forums.fid = forum_posts.fid)
ORDER BY date
Syntax might be a bit dodgy, but the "SELECT fid, MAX(date) ... GROUP BY fid" should return the max post date per forum, for each forum. Then it joins that with the forum list, and sorts by the max post date.
[Edited by Phlamethrower at 22:08, 28/5/2005] |
| |
[ Log in to reply ] |
| |
|
Acorn Arcade forums: Programming: SQL puzzle |
