tekrscom Posted September 12, 2009 Share Posted September 12, 2009 MySQL - 5.0.81 PHP - 5.2.5 Hi everybody... I'm sitting here trying to think of the query logic that I would use in order to accomplish this specific task... I have 2 tables in particular, the first one is ForumThreads and the second one is ForumReplyThreads... What I am trying to do is put on my front page kind of a simulated magpierss feed display of the last say 5 posts... since I did away with phpBB3 and built my own for my little site... So I am trying to figure out how I would go about getting the last 5 threads and/or replies... The problem is that they are in 2 different tables... It would seem that I would almost be better off trying to figure out how to combine them together in a temporary table, defining each as either a post or a reply, and then extracting the last however many... Anyone have any good solution? Quote Link to comment Share on other sites More sharing options...
kickstart Posted September 12, 2009 Share Posted September 12, 2009 Hi Why are they split into 2 seperate tables? Anyway, I would suggest using something like SELECT "Message" AS messageType, ID, PostTime FROM ForumThreads UNION SELECT "Reply" AS messageType, ID, PostTime FROM ForumReplyThreads ORDER BY PostTime DESC LIMIT 0,5 All the best Keith Quote Link to comment Share on other sites More sharing options...
tekrscom Posted September 12, 2009 Author Share Posted September 12, 2009 Hi Why are they split into 2 seperate tables? Anyway, I would suggest using something like SELECT "Message" AS messageType, ID, PostTime FROM ForumThreads UNION SELECT "Reply" AS messageType, ID, PostTime FROM ForumReplyThreads ORDER BY PostTime DESC LIMIT 0,5 All the best Keith Thanks, I will try that... In the beginning I did weigh the option of making it one table and just define whether it was a post or a reply thread... but decided to keep the tables smaller and more spread out... Quote Link to comment Share on other sites More sharing options...
kickstart Posted September 12, 2009 Share Posted September 12, 2009 Hi Personally I would say that was a mistake. Makes you logic more complicated and queries less efficient. I would have a table of threads, and then a table of posts recording which threads they belong to. If you wanted to split the first post onto a new thread it would be difficult with you current setup. All the best Keith Quote Link to comment Share on other sites More sharing options...
tekrscom Posted September 12, 2009 Author Share Posted September 12, 2009 Hi Personally I would say that was a mistake. Makes you logic more complicated and queries less efficient. I would have a table of threads, and then a table of posts recording which threads they belong to. If you wanted to split the first post onto a new thread it would be difficult with you current setup. All the best Keith If I'm understanding you correctly, I think that is what I have... I took a snapshot of my database structure and attached it... let me know if you don't mind if you think it could be improved upon... [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
kickstart Posted September 12, 2009 Share Posted September 12, 2009 Hi Not quite. You have the first post details on the thread table. I would chop out the "ThreadContent" field (and related fields) and just put them with the rest of the posts. All the best Keith Quote Link to comment Share on other sites More sharing options...
tekrscom Posted September 12, 2009 Author Share Posted September 12, 2009 Hi Not quite. You have the first post details on the thread table. I would chop out the "ThreadContent" field (and related fields) and just put them with the rest of the posts. All the best Keith Yeah, that is a good idea... thank you... much appreciated Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.