[SOLVED] Custom forum code, got me stumped. Help plz

[ameyemad]

Hi all

 

Can someone help me sort this one out? My current code is

$forums=@mysql_query("Select 
T.replies AS TOPICREPLIES,
T.replytime AS REPLYTIME,
T.replyusername AS REPLYUSERNAME,
T.id AS TOPICID
,T.subject AS TOPICSUBJECT,
T.username AS TOPICUSERNAME,
F.id AS FORUMID,
F.subject AS FORUMSUBJECT,
T.timeposted AS TOPICTIMEPOSTED 
From (forumforums F LEFT OUTER JOIN forumtopics T ON F.id=T.forumid) Order By T.replytime Desc");

Now I want only the last topic reply (in forumtopics) for each forum (in forumforums). But it doesn't limit to just the last post made in each forum, it will show more than just the last post. Any ideas? I can't get GROUP to work?

 

Thanks for your help!

[PugJr]

Something just looks terribly wrong about your code...Maybe its me being an amateur.  :'(

 

 

From what it sounds like, this would help:

 

$querytopic = "SELECT * FROM topics ORDER BY time DESC" or die();

 

In this case you would need your topics to have a time by last they were posted, so if someone posts, bring the time to time(). That will give you all your topics by DESC. Now just select the first row and bingo! Your done. I think.

[ameyemad]

sorry, that will only show the topics. this code should show the forums as well as the last post in each topic, in each forum.

[PugJr]

sorry, that will only show the topics. this code should show the forums as well as the last post in each topic, in each forum.

 

Topic* 

 

What that code does there if you limit it to 1 will display the most recent posted topic. If you want the post, that shouldn't be too hard to modify. Do the same as topic, except switch it with post. The only issue I could think that will occure is if two topics/posts have the same amount of time...Then I'm not sure how it decides.

[ameyemad]

sorry if you misunderstood. here is the 2 tables:

 

table1 - forumforums

id

subject

 

table2 - forumtopics

id

forumid

username

timeposted

msg

subject

replies

replyusername

replytime

 

so what i need is each forum in forumforums shown in order of last post done in any topic that's part of the forum. The forumforums.id will equal forumtopics.forumid as shown in my code. But for some reason I cannot group table1 or table2 properly so that it shows each forumforums.id once only

[xsist10]

Have you tried grouping on the ForumId. That might give you the result you want. Try this:

 

SELECT [Fields]

FROM forumforums f

JOIN forumtopics t ON (f.id=t.forumid)

GROUP BY t.forumid

ORDER BY t.replytime DESC

[ameyemad]

thanks xsist10, I did try that and it works to a certain extent. except it only shows forums that has topics in them and does not display the forums that are empty.

[xsist10]

Then just change the GROUP to use the forum id on the forum table and use an IF_NULL to populate the topic fields [edit: and use a LEFT JOIN].

 

e.g.:

 

SELECT [Fields ... ], IFNULL(t.subject, 'No topics found')

FROM forumforums f

LEFT JOIN forumtopics t ON (f.id = t.forumid)

GROUP BY f.id

ORDER BY t.replytime DESC

[ameyemad]

Awesome, that looks like it got it. It's been racking my brain for hours grrr.

 

Thanks for your help!

[xsist10]

No problem. Glad I could help.