Log in

View Full Version : Getting the last post of a specific topic

Jorim
09-05-2005, 07:36 PM
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
09-06-2005, 12:00 AM
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.


SELECT thread.*, post.*
FROM thread
LEFT JOIN post USING (threadid)
WHERE thread.threadid = <THREAD_ID>
ORDER BY post.dateline DESC
LIMIT 1
AN-net
09-06-2005, 02:56 AM
better to use a field called lastpost_id in the thread table;)
merk
09-06-2005, 04:14 AM
Good point - I didnt even think to look if that existed.

Thanks :)

SELECT thread.*, post.* FROM thread LEFT JOIN post ON (thread.lastpostid = post.postid) WHERE thread.threadid=<BLAH>
Jorim
09-06-2005, 09:03 AM
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 :D
merk
09-06-2005, 09:05 AM
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

SELECT thread.*, post.*

with

SELECT post.<FIELDNAME>

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
09-06-2005, 10:06 AM
$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
09-06-2005, 10:14 AM
$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
09-06-2005, 10:21 AM
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
09-06-2005, 10:32 AM
Hmm looking again at your query, i am surprised you don't get an error on it. Try the following:
$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);
}
Jorim
09-06-2005, 10:50 AM
:up: Yeah, it works :banana:

Thanks guys :D

[edit] (interesting option this...)

++++, it isn't working at all :( I looked at the wrong page, very stupid...

$news stays empty :(
Marco van Herwaarden
09-06-2005, 11:14 AM
Lol, sorry but that query is totally wrong. :D
thread.lastpost is a timestamp, not a postid.

Use the following, this should work (if 7348 is an existing threadid):
$getnews = $DB_site->query_first("
SELECT *
FROM " . TABLE_PREFIX . "post AS post
WHERE post.threadid=7348
AND post.visible = 1
ORDER BY post.dateline DESC
LIMIT 1");
print_r($getnews);
Jorim
09-06-2005, 12:32 PM
That's not funny :p but everything works now. Thank you very very mutch! :D

I've got one final question and then I'l shut up :) I'd like te have the attachement aswel.

Will this work:
$news = $DB_site->query_first("
SELECT pagetext, attach
FROM " . TABLE_PREFIX . "post AS post
WHERE post.threadid=884
AND post.visible = 1
ORDER BY post.dateline DESC
LIMIT 1");
Or do I have to use extra query's for that ??
Marco van Herwaarden
09-06-2005, 12:34 PM
No, that will not work. :D

'attach' will not contain the attachment data. How to retrieve the attachment data depends on how your attachments are stored (database/filesystem), but is much more complicated then just a query.
Jorim
09-06-2005, 12:36 PM
I thought so. Is it possible to get the attachement of that specific post and can you help me?

Thanks again :D
Marco van Herwaarden
09-06-2005, 12:36 PM
Previous reply updated.
Jorim
09-06-2005, 05:05 PM
Previous reply updated.
I'm using the normal settings of vBulletin I guess that's with the database. I've got a few table's with attachement information...

This query works, but the part to display the image that's attached (that's what I want) won't. What do I wrong??? :ermm:

$news = $DB_site->query_first("
SELECT post.pagetext, post.postid
FROM " . TABLE_PREFIX . "post AS post
WHERE post.threadid=884
ORDER BY post.dateline DESC
LIMIT 1");

$attach = $DB_site->query_first("
SELECT attachment.filename, attachment.filesize, attachment.visible, attachmentid, counter
FROM " . TABLE_PREFIX . "attachment AS attachment
WHERE attachment.postid = {$news['postid']}");

//$attachment['attachmentextension'] = file_extension($attach['filename']);
//$attachment['filename'] = $attach['filename'];
//$attachment['attachmentid'] = $attach['attachmentid'];
//$attachment['filesize'] = vb_number_format($attach['filesize'], 1, true);
//$attachment['counter'] = $attach['counter'];
//eval('$attachment = "' . fetch_template('postbit_attachment') . '";');
//$attachment = str_replace('"attachment.php', '"' . $vboptions['bburl'] . '/attachment.php', $attachment);
$attachment = '<a href="' . $vboptions['bburl'] . '/attachment.php?' . $session['sessionurl'] . 'attachmentid=' . $attach['attachmentid'] . '" target="_blank"><img border="0" src="' . $vboptions['bburl'] . '/attachment.php?' . $session['sessionurl'] . 'attachmentid=' . $attach['attachmentid'] . '&amp;stc=1&amp;thumb=1" /></a>';

Fixed it :D:D:D:D

Thanks everybody for your help! :up:

$news = $DB_site->query_first("
SELECT post.pagetext, post.postid
FROM " . TABLE_PREFIX . "post AS post
WHERE post.threadid=884
ORDER BY post.dateline DESC
LIMIT 1");

$attach = $DB_site->query_first("
SELECT attachment.filename, attachment.filesize, attachment.visible, attachmentid, counter
FROM " . TABLE_PREFIX . "attachment AS attachment
WHERE attachment.postid = {$news['postid']}");

$attachment['attachmentextension'] = file_extension($attach['filename']);
$attachment['filename'] = $attach['filename'];
$attachment['attachmentid'] = $attach['attachmentid'];
$attachment['filesize'] = vb_number_format($attach['filesize'], 1, true);
$attachment['counter'] = $attach['counter'];
eval('$attachment = "' . fetch_template('postbit_attachmentimage') . '";');
$attachment = str_replace('"attachment.php', '"' . $vboptions['bburl'] . '/attachment.php', $attachment);