vb.org Archive - Getting the last post of a specific topic

Page 1 of 2

Show 40 post(s) from this thread on one page

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); 

}

All times are GMT. The time now is 11:37 AM.

Page 1 of 2

Show 40 post(s) from this thread on one page

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01102 seconds Memory Usage 1,743KB 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 (3)bbcode_php_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)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 pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: