Jump to content

Archived

This topic is now archived and is closed to further replies.

Monkeyarris

Selecting last replied to topics in a forum

Recommended Posts

Hi everyone,

 

I\'m currently writing my own forum for a site, but have run into a problem....

 

How do you select the last replied to topics and sort them in a descending fashion?

 

I\'ve tried $sql = \"SELECT * FROM forums WHERE forumno = \'$board\' ORDER BY postid DESC GROUP BY threadid\";

 

But this doesn\'t work it only works when you swap the Order by and Group by statements around which then only selects the first posts to a thread and I want it the OTHER way around :cry:

 

Any help would be greatly appreciated and you will also receive a \'Monkeyarris thinks im cool badge\' :P

Share this post


Link to post
Share on other sites

Assuming your db does not support subqueries, this will list the latest post for each thread

 

[php:1:0308db28b1]<?php

$sql = \"SELECT * FROM forums WHERE forumno = \'$board\' ORDER BY threadid, postid DESC \";

$res = mysql_query($sql);

$lastid=0;

while ($row = mysql_fetch_array($res)) {

if ($row[\'threadid\'] != $lastid) {

echo \"$row[\'threadid\'] $row[\'postid\']<br>\";

}

$lastid = $row[\'threadid\'];

}

?>[/php:1:0308db28b1]

 

hth

Share this post


Link to post
Share on other sites

Hmm this doesn\'t work Barand.... it gives the most recent posts not most recently replied to topics.

 

What I need is to be able to get a list of posts in desc order and group them by threadid which then leave me with the the most recnt post for each thread in a descending order.

 

Any other ideas any one? The badge is still up for grabs :P

Share this post


Link to post
Share on other sites

Heres the SQL dump I used:-

 

[php:1:a63316ff89]CREATE TABLE forums (

postid int(8) unsigned NOT NULL auto_increment,

forumno int(8) unsigned NOT NULL default \'0\',

threadid int(8) unsigned NOT NULL default \'0\',

date varchar(15) NOT NULL default \'\',

user_id int(8) unsigned NOT NULL default \'0\',

ip varchar(30) NOT NULL default \'\',

subject varchar(50) NOT NULL default \'\',

comment blob NOT NULL,

deleted int(1) unsigned NOT NULL,

newthread int(1) unsigned NOT NULL,

PRIMARY KEY (postid),

KEY game_id (user_id)

) TYPE=MyISAM;[/php:1:a63316ff89]

 

 

Will I need to change the structure then?

Share this post


Link to post
Share on other sites

Nah, looks great.

 

SELECT thread.threadID, Max(post.postID) AS MaxOfpostID

FROM post WHERE post.threadID = thread.threadID

GROUP BY thread.threadID

ORDER BY Max(post.postID) DESC;

 

:)

Share this post


Link to post
Share on other sites

Hmm that doesn\'t work either.... it looks like your trying to call data from two tables where theres only one \'forums\'

 

I\'ve even tried looking at phpbb code to see how it works but to no avail :(

 

Is there no way to filter out duplicate values in an array?

Share this post


Link to post
Share on other sites

Then why do you have these lines in your create script?

 

  forumno int(8) unsigned NOT NULL default \'0\', 

 threadid int(8) unsigned NOT NULL default \'0\', 

 

There are 3 distinct entities in your table: forum, thread and post. What happens when the only post in a thread is deleted? That thread dissappears. What happens when the only post in a forum is deleted? That forum disappears.

 

You need to normalise your tables my friend. :)

Share this post


Link to post
Share on other sites
Heres the SQL dump I used:-

 

[php:1:1ca2a8e291]CREATE TABLE forums (

postid int(8) unsigned NOT NULL auto_increment,

forumno int(8) unsigned NOT NULL default \'0\',

threadid int(8) unsigned NOT NULL default \'0\',

date varchar(15) NOT NULL default \'\',

user_id int(8) unsigned NOT NULL default \'0\',

ip varchar(30) NOT NULL default \'\',

subject varchar(50) NOT NULL default \'\',

comment blob NOT NULL,

deleted int(1) unsigned NOT NULL,

newthread int(1) unsigned NOT NULL,

PRIMARY KEY (postid),

KEY game_id (user_id)

) TYPE=MyISAM;[/php:1:1ca2a8e291]

 

 

Will I need to change the structure then?

 

I\'d strongly advise using a proper datetime type for date fields instead of varchar and also do not call it \'date\' as it\'s a reserved word. Use something like \'post_date\'.

Share this post


Link to post
Share on other sites
What I need is to be able to get a list of posts in desc order and group them by threadid which then leave me with the the most recnt post for each thread in a descending order.

 

The code I posted will give the latest post for each thread but not necessarily in desc time order by thread. For that you would need the date field, but as its varchar (and not datetime) I don\'t know if the contents would give the correct sorted sequence.

 

Edit: just realised we _can_ use postid, I\'ll amend my code and resubmit.

Share this post


Link to post
Share on other sites

[php:1:5cdcaf3177]<?php

$sql = \"SELECT * FROM forums WHERE forumno = \'$board\' ORDER BY threadid, postid DESC \";

$res = mysql_query($sql);

$lastid=0;

while ($row = mysql_fetch_array($res)) {

if ($row[\'threadid\'] != $lastid) {

$array[$row[\'threadid\']] = $row[\'postid\']<br>\";

}

$lastid = $row[\'threadid\'];

}

?>[/php:1:5cdcaf3177]

We now have array with key = thread and value = lastest postid for thread. Sort to reverse order and display.

 

[php:1:5cdcaf3177]<?php

arsort ($array);

 

foreach ($array as $thread=>$post) {

echo $thread $post;

}

?>[/php:1:5cdcaf3177]

Share this post


Link to post
Share on other sites

They say it\'s a very fine line between genius and insanity :D

 

Where\'s my badge?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.