Getting the last post of a specific topic

[Jorim]

I'm a SQL n00b so I have to ask you guys.

How does the query look, which I need, to get the last post (unparsed) from thread X (vBulletin 3.0.6). I can't figure it out myself :disappointed:

Thanks in advance!

Jorim

[merk]

It could be something like the below, but remember that the query could be very specific and this one will only work for one thread and one post. You might need to do 2 queries to actually make it work for any other purpose.

[sql]
SELECT thread.*, post.*
FROM thread
LEFT JOIN post USING (threadid)
WHERE thread.threadid = <THREAD_ID>
ORDER BY post.dateline DESC
LIMIT 1
[/sql]

[AN-net]

better to use a field called lastpost_id in the thread table

[merk]

Good point - I didnt even think to look if that existed.

Thanks

[sql]SELECT thread.*, post.* FROM thread LEFT JOIN post ON (thread.lastpostid = post.postid) WHERE thread.threadid=<BLAH>[/sql]

[Jorim]

I don't have that field maybe introduced with 3.0.7 ???

I've got an extra question. I'm only interested in the content of the post, nothing more. How do I change merk's first code to a code that places the content in a variable? I thought i'd know, but my ideas don't work

Thanks for you're help

[merk]

The field will exist, it has existed since at least version 2, it might be named slightly differently. I cant remember and cant login to pma to check.

To just get the post content, again, i dont know the exact name of the field, but you would replace

[sql]SELECT thread.*, post.*[/sql]

with

[sql]SELECT post.<FIELDNAME>[/sql]

It will have unprocessed BBCodes in there, you will need to work out how to use the bbcode functions to process them (pretty simple).

[Jorim]

PHP Code:

$getnews = $DB_site->query("SELECT " . TABLE_PREFIX . "post.pagetext FROM " . TABLE_PREFIX . "thread LEFT JOIN " . TABLE_PREFIX . "post ON (" . TABLE_PREFIX . "thread.lastpost = " . TABLE_PREFIX . "post.postid) WHERE " . TABLE_PREFIX . "thread.threadid=7348");
print $getnews; 


Just a test thing , but "Resource id #43" is the output and that's not what i'd like to have

thread.lastpost is wrong or everything is wrong. I've attached a screendumb of the table (thread), but lastpost is likely the one I need...

You're help is again very appreciated...

[Marco van Herwaarden]

PHP Code:

$getnews = $DB_site->query("SELECT " . TABLE_PREFIX . "post.pagetext FROM " . TABLE_PREFIX . "thread LEFT JOIN " . TABLE_PREFIX . "post ON (" . TABLE_PREFIX . "thread.lastpost = " . TABLE_PREFIX . "post.postid) WHERE " . TABLE_PREFIX . "thread.threadid=7348");
while ($news = $DB_site->fetch_array($getnews))
{
   print_r($news);
} 


[Jorim]

I need to buy a book...

Thanks Marco

Unfurtunally stays $news empty. Something is still going wrong, thread.lastpost isn't right or...

[Marco van Herwaarden]

Hmm looking again at your query, i am surprised you don't get an error on it. Try the following:

PHP Code:

$getnews = $DB_site->query("
        SELECT pagetext FROM " . TABLE_PREFIX . "post AS post
        LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON (thread.lastpost = post.postid) 
        WHERE thread.threadid=7348"); 
while ($news = $DB_site->fetch_array($getnews)) 
{ 
   print_r($news); 
}