[Resolved] [Resolved] For me a tricky thing to do(Mysql)

Ceril

Greetings. Now, the times if half past five in the morning and I've been pulling my toes out for this a couple of hours and lets just say my brain aint geting better since I get close to something workable, then it turns into shreads.

Anyways:
I got one TblForumPosts which contains Posts and Topics

ID , int
forumID -Tells what forum the post belongs to, int
UserID -What user made this post', int
timest -timestamp (php: time() ), bigint
subject - Yep, just a subject, varc 25
message - the message, text
SubID -Tells me what ID it is connected to

Now to further explain how this hangs togheter:

If "SubID" is 0 it is a topic
If "SubID" is >0 it is connected to an ID with a SubID=0(Thus its a reply to a topic)

Now what I'am trying to get is the five topics under forumID=1 that have been posted in most recently, oh yes, TblUser.ID=TblForumPosts.UserID so that TblUser.nickname as nickname 🙂

I'am thinking I should have made a TblTopics and TblPosts tabel instead, but even If that would have taken less time I dont give up easily because I want to get this one to work.

Currently I get thoose values from my sql listing 5 topics:

ID - The Topic ID
replies - The number of replies to the Topic
forumID- the forum the topic belongs to, this one is hard to fail on
Subject - The subject of the Topic
timest - The Timestamp... for the original Topic not the last one with matching SubID, thus displaying Topic Creation date and not last post date

Have I managed to explain it somewhat? The sql statment I had been working on which I managed to erase looked along thoose lines🙁Iam realy tired now)

SELECT t.ID, COUNT(p.SubID) as replies, t.forumID, t.subject, u.nickname, p.timest 
FROM TblForumPosts as t, TblForumPosts as p, TblUser as u 
WHERE t.forumID=1 AND t.SubID=0 AND u.ID=t.UserID AND p.SubID=t.ID
GROUP BY t.ID
ORDER BY p.timest
LIMIT 0,5

Any help to try to fix this up would be appreciated :-)

Edit:
I just tried with the above sql and well... I just tried it on a forum(2) that had only a topic and no subids, it didnt give me back much >_<

paulnaj

Hi Ceril,

Sorry, but I cant (100%) understand what you're trying to do, but you can simplify things for yourself by just trying to get the correct records using the main table only.

So, try the most basic query on the main table (I'm guessing here) and build on that ...

// All topics in forum 1 (ordered - recent first)
SELECT t.ID, t.forumID, t.subject
FROM TblForumPosts as t
WHERE t.forumID=1 AND t.SubID=0 
ORDER BY t.timest DESC

// 5 most recent topics in forum 1
SELECT t.ID, t.forumID, t.subject
FROM TblForumPosts as t
WHERE t.forumID=1 AND t.SubID=0 
ORDER BY t.timest DESC 
LIMIT 0, 5

// Now add back in your join to TblUser
SELECT t.ID, t.forumID, t.subject, u.nickname
FROM TblForumPosts as t, TblUser as u
WHERE t.forumID=1 AND t.SubID=0 AND u.ID=t.UserID
ORDER BY t.timest DESC 
LIMIT 0, 5

As I say, I wasn't sure why you were using as both a 'main' table and a 'detail' table so I could have missed something here. :queasy:

Paul 🙂

Ceril

Hello 🙂

What I'am trying to do is to query for the five topics most recently replied to, and display such things as:

How many replys does this topic have? When was the last reply? Who was the User that started the topic? And who made the last reply?

The reason for me using TblForumPosts as t and p is that TblForumPosts includes both the Topics and the replys.

I'am thinking what I need is some sort of way to order the posts tables on timestamps, retrive 5 distinct topic ids that has been posted in, along thoose lines. I'am bad at sql and it is so many tricks one can do with it. But I would love to have some more help if you get any ideas. 🙂

Thanks
/C

Originally posted by paulnaj
Hi Ceril,

Sorry, but I cant (100%) understand what you're trying to do, but you can simplify things for yourself by just trying to get the correct records using the main table only.

So, try the most basic query on the main table (I'm guessing here) and build on that ...
// All topics in forum 1 (ordered - recent first)
SELECT t.ID, t.forumID, t.subject
FROM TblForumPosts as t
WHERE t.forumID=1 AND t.SubID=0 
ORDER BY t.timest DESC

// 5 most recent topics in forum 1
SELECT t.ID, t.forumID, t.subject
FROM TblForumPosts as t
WHERE t.forumID=1 AND t.SubID=0 
ORDER BY t.timest DESC 
LIMIT 0, 5

// Now add back in your join to TblUser
SELECT t.ID, t.forumID, t.subject, u.nickname
FROM TblForumPosts as t, TblUser as u
WHERE t.forumID=1 AND t.SubID=0 AND u.ID=t.UserID
ORDER BY t.timest DESC 
LIMIT 0, 5
As I say, I wasn't sure why you were using as both a 'main' table and a 'detail' table so I could have missed something here. :queasy:

Paul 🙂 [/B]

Roger_Ramjet

Yes , it always sounds like a good idea to use sub_id = 0 as a thread and > 0 as a reply, and it never works. As you are discovering, it never pays to be too clever with SQL and relational DBs.

Restructure your db is the only way you'll get it to work well. Have a table for Threads, and a seperate table for Replies. Then half of your problems will disappear. A post in Threads tells you what the topic is, who started it and when. Posts in Replies are time-stamped so you can tell the order, count them for how many, etc. You will find that this is about the only workable solution, and one used by most bulletin boards.

Ceril

Mmm, Yes it would appear to be the way to go. But I did it mostly to see how I would do something like that, and sad to say: No more one table relations!

Thank you for the time 🙂
/C

Originally posted by Roger Ramjet
Yes , it always sounds like a good idea to use sub_id = 0 as a thread and > 0 as a reply, and it never works. As you are discovering, it never pays to be too clever with SQL and relational DBs.

Restructure your db is the only way you'll get it to work well. Have a table for Threads, and a seperate table for Replies. Then half of your problems will disappear. A post in Threads tells you what the topic is, who started it and when. Posts in Replies are time-stamped so you can tell the order, count them for how many, etc. You will find that this is about the only workable solution, and one used by most bulletin boards.