Selecting last replied to topics in a forum

[Monkeyarris]

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

 

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

[Barand]

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

[Monkeyarris]

Thanks Barand ill give it a go now, your badge is in the post!

[Monkeyarris]

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

[michael yare]

What is your DB structure?

 

forum (forumID)

thread (threadID, forumID)

post (postID,threadID)

 

??

[Monkeyarris]

Heres the SQL dump I used:-

 

[php:1:a63316ff89]CREATE TABLE forums (

postid int( unsigned NOT NULL auto_increment,

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

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

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

user_id int( 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?

[michael yare]

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;

 

[Monkeyarris]

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?

[michael yare]

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.

[Barand]

Heres the SQL dump I used:-

 

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

postid int( unsigned NOT NULL auto_increment,

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

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

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

user_id int( 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\'.

[Barand]

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.

[Barand]

[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]

[Monkeyarris]

Haha! Thank you Barand your a certified genius! This works a treat

 

Thank you VERY VERY much

[Barand]

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

 

Where\'s my badge?

[Monkeyarris]

In the post