bump threads in a forum...

sid

ok... here is my problem...

i am working with mySql 3.23 and php 4.xx on this page http://www.nevergetoveryou.net/ and have a problem with the forum.

i implemented a whole flat mode for the basically threaded forum without problems.

each forum has its own table - lets call it table "forumtable" - and has the following fields which are of concern for this issue:

id => unique id for each posting
parent_id => for threaded mode: id of the message the user answered to (if it is the first msg. in a thread it is '0')
thread_id => holds the id of the thread the message is part of
date => timestamp of posting

what i would like to change now is the following:

the threads always stay in the order they were posted in

SELECT * FROM forumtable WHERE parent_id = '0' ORDER BY postdate DESC

this way the output result consists of all the messages which are the first in a thread (i.e. those which started a thread). then you just have to recurse the subitems and voila.

the thing i want to change now is, that whenever a new message in a thread is posted, it gets "bumped" to the top, resulting in a displayed forumview, which is sorted by the most recently posted in threads.

i just cannot get my queries and php to produce the right result for me.

my idea:

sort the table by thread_id, find for each thread_id the message with the highest value of date - then sort the thread_id's according to that result. finally get the rows of the table with parent_id = '0' this time ordered by the value of the thread_id's we just calculated.

i hope this is understandable - anyways - i could not make it work and would be very happy about some help. it should just be a few lines of code, but i seem to be retarded to get my mySql skills together for that.

thanks a lot - sid stargraphX

merkinmuffley

select thread_id, max(date) as lastpost from forumtable group by thread_id order by max(date) desc;

hope that helps
/nick

sid

if it works you are my hero! ill try it later and will let you know!

sid

hmmmm... unfortunately it wont work after all... this error is being generated...

MySQL Error: Invalid SQL: SELECT thread_id, max(date) as lastpost from forumtable group by thread_id order by max(date) desc
Database error: 1111 (Invalid use of group function)

what does the "as lastpost" do exactly?
doesnt the "parent_id='0'" part have to be somewhere in the WHERE clause too?

merkinmuffley

the "as" clause is for aliasing a column of the result set. if you say "select foo AS bar from myTable", you get a single-column result set with the data from myTable.foo. but, the column is called "bar" in the result set.

if you group your result set by thread, you don't need to select only the first post from each thread to get a result set where each thread is represented once. then again, this doesn't leave you an easy way to get the subject or poster for the first post. also I'm not sure why the group by thing didn't work; I don't work with mySql and I don't know its capabilities for the group by function.

sid

i mainly worked with sql at the college so far and am not so familiar with the specifications of mysql itself... hmmm... ill try to get this to work somehow... i might check out how other forum programmers solved that problem

still i would be happy about specific ideas.

thanks again - sid

ultraslacker

Use the alias for the aggregate in the ORDER BY. Use a join to get the subject and poster for the latest post of each thread.

Also, date is a reserved word - you may run into problems with later releases of mysql.

sid

i know about the date issue - but thats still a remaining part of the original code, which was not written by me. it hasnt caused any problems so far.

ill try the join and see if it brings any new results...

ultraslacker

Of course it won't with the current versions of mysql - it is reserved per sql 92 standard, which mysql only partially supports.

jweissig

it should say ->

"select thread_id, max(date) as lastpost from forumtable group by thread_id order by lastpost desc;"

the reason that it failed was you can't sort or order by things like max() thats why you have to aliase them to something like "lastpost" then you have to do a "order by lastpost".

cya
- Justin
😃

chriskl

This is from our forums code. It does exactly what you want. Obviously your schema will be different to ours, so you'll need to modify it. It's also not quite as well written as it could be. Think 'self-join'.

SELECT MAX(fp.top_id) AS top_id,
MAX(fp.post_id) AS post_id,
MAX(fp.subject) AS subject,
MAX(fp.author) AS author,
(COUNT(*) - 1) AS num_replies,
MAX(fp2.datetime) AS latest_reply,
to_char(MAX(fp2.datetime), 'FMHH:MIam, Month DD YYYY') AS nice_latest_reply
FROM forums_posts fp,
forums_posts fp2
WHERE fp.top_id = fp2.top_id
AND fp.forum_id = {$forum_id}
AND fp.parent_id IS NULL
GROUP BY fp.top_id
ORDER BY latest_reply DESC