vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)
-   -   Getting the last post of a specific topic (https://vborg.vbsupport.ru/showthread.php?t=95708)

Jorim 09-05-2005 07:36 PM
Getting the last post of a specific topic
 
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.

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

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

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

[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 09-06-2005 10:06 AM
1 Attachment(s)
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 09-06-2005 10:14 AM
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 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:
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); 
} 

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):
PHP Code:
$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:
PHP Code:
$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
Quote:
Originally Posted by MarcoH64
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:

PHP Code:
$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:

PHP Code:
$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); 


All times are GMT. The time now is 04:14 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01254 seconds
  • Memory Usage 1,789KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (7)bbcode_php_printable
  • (1)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (17)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete