Jump to content


Photo

Selecting last replied to topics in a forum


  • Please log in to reply
14 replies to this topic

#1 Monkeyarris

Monkeyarris
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 10 August 2003 - 12:01 PM

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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 10 August 2003 - 07:39 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Monkeyarris

Monkeyarris
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 11 August 2003 - 11:16 AM

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

#4 Monkeyarris

Monkeyarris
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 12 August 2003 - 12:30 PM

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

#5 michael yare

michael yare
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationLondon, UK

Posted 12 August 2003 - 02:54 PM

What is your DB structure?

forum (forumID)
thread (threadID, forumID)
post (postID,threadID)

??

#6 Monkeyarris

Monkeyarris
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 12 August 2003 - 04:10 PM

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?

#7 michael yare

michael yare
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationLondon, UK

Posted 13 August 2003 - 11:49 AM

Nah, looks great.

SELECT thread.threadID, Max(post.postID) AS MaxOfpostIDFROM post WHERE post.threadID = thread.threadIDGROUP BY thread.threadIDORDER BY Max(post.postID) DESC;

:)

#8 Monkeyarris

Monkeyarris
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 14 August 2003 - 09:04 AM

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?

#9 michael yare

michael yare
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationLondon, UK

Posted 14 August 2003 - 12:37 PM

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. :)

#10 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 14 August 2003 - 12:45 PM

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\'.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#11 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 14 August 2003 - 12:59 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#12 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 14 August 2003 - 01:09 PM

[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]
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#13 Monkeyarris

Monkeyarris
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 14 August 2003 - 02:53 PM

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

Thank you VERY VERY much :D

#14 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 14 August 2003 - 05:06 PM

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

Where\'s my badge?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#15 Monkeyarris

Monkeyarris
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 15 August 2003 - 09:06 AM

In the post :P




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users