Jump to content

Selecting last replied to topics in a forum


Monkeyarris

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

Link to comment
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

Link to comment
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

Link to comment
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?

Link to comment
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. :)

Link to comment
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\'.

Link to comment
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.

Link to comment
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]

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.