[Resolved] JOIN sql queries with MAX results

weekender

hi all

i have coded a basic form, and need to order threads by most recent reply - so when someone replies to an old thread, the thread moves to the top of the view. We've all seen it...

i have the thread to get my posts - this gets all posts in that forum that are not replies (no parent_post)

$sql = "SELECT * FROM posts ".
    "WHERE parent_forum = 1 AND parent_post = 0 ".
    "ORDER BY posted DESC";

and i have the sql to get the most recent reply to the thread. This converts the timestamps to unix and gets the maximum value where the post is either post #5 or a reply to post #5

$sql = "SELECT MAX( UNIX_TIMESTAMP ( posted ) ) ".
    "FROM posts ".
    "WHERE id=5 OR parent_post=5";

so what i want to do is merge the two into something like this bad pseudo attempt below

$sql = "SELECT *, MAX ( UNIX_TIMESTAMP ( posted ) ) ".
    "FROM posts ".
    "WHERE id=5 OR parent_post=5 ".
    "ORDER BY MAX ( UNIX_TIMESTAMP ( posted ) ) DESC";

but i have no skill in doing that whole this.x and this.y shebang.

thanks, adam

LordShryku

Not so sure I understand bud....
MAX is used to pull one record. Using that in conjunction with a SELECT * will pull all records, and that max timestamp will only be the one record, repeated on each row.

Also, the aliasing isn't really needed since this is all coming from the same table. Aliasing is generally used when joining tables.

weekender

sorry, did an all nighter last night and again tonight. possibly not making the greatest sense.

i have my posted table with fields

id, title, content, author, parent_forum, parent_post

so all threads store the id of the parent forum. all replies store the id of the parent_post, and all posts which start a thread (ie are the first in that thread) don't have a parent_post

so if i want all the posts in a thread i do WHERE id=$id OR parent_post = $id, and this gives me the first thread and all those that have that thread id in parent_post

and to get all the threads in a forum (ie to show the index), i do "WHERE parent_forum=$forum_id"

but then how do i sort this, so the threads are sorted by the maximum posted date in that collection of posts?

sh*t, think that's even more occluded than the first thread. sorry mate, thanks for trying!

another way of saying it: how does this site do the select sql so when you post a reply to an old thread, it moves to the top?

weekender

just worked it out

SELECT * FROM posts ORDER BY posted DESC

for each one of those...

SELECT * FROM posts WHERE id = $parentid

simple when you take a fresh look...

thanks Lordy

LordShryku

Hold up bud...think you're still doubling your work. If you select the first time around, why select from the same table again for each one?

weekender

you are bloody good.

so i don't need to re-query, i just don't output the ones that are replies

excellent! cheers me old matey

LordShryku

Yar! Either that, or I'm just missing something (which is entirely possible).

weekender

just hit me how long it's been since i asked rather than answered (or tried). Feels good to be young and noob-ile again.

Then it hit me that i don't think i've ever seen you not answering a post. In fact, have i ever seen a post not involving you in some part of the process?

when do you sleep?

LordShryku

I leave a lot alone. There's still tons I don't know. There's people around here, like Weedpacket and Buzzly, who've forgotten more than I even know. But I try to help as much as I can, for what I do know.

And the sleep thing: between the hours o 4:00 and 4:12 each morning. And I leave the computer on to play music. 😃