vb.org Archive
Page 1 of 2 1 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)
-   -   Last xx posts, can you correct my code pls. (https://vborg.vbsupport.ru/showthread.php?t=108971)

FFMG 02-26-2006 08:11 PM
Last xx posts, can you correct my code pls.
 
Hi,

The code below displays the last 5 posts, but I am not happy with making that many queries.
Would there be a better way of doing it with just one querry?

Code:
        global $db;

        $threads = $db->query_read("
                SELECT threadid,title,lastpost,lastposter
                FROM " . TABLE_PREFIX . "thread
                WHERE visible=1 AND open=1 ORDER BY lastpost DESC LIMIT $count
        ");


        $res = "";
        if ($num_thread = $db->num_rows($threads))
        {
                $data = false;
                while ($thread_get = $db->fetch_array($threads))
                {
                        $lastpost = $thread_get['lastpost'];
                        $poster = $thread_get['lastposter'];
                        $tid = $thread_get['threadid'];

                        $post = $db->query_read(
                                "SELECT postid FROM " . TABLE_PREFIX . "post WHERE threadid=$tid ORDER BY postid DESC LIMIT 1"
                        );

                        if( $num_post = $db->num_rows($threads))
                        {
                                while( $getp = $db->fetch_array($post) )
                                {
                                        $pid = $getp['postid'];
                                        $title = $thread_get['title'];
                                        $title = substr($title,0,100);

                                        $ref = "<a style=\"font-size:xx-small;\" href=\"showthread.php?p=$pid#post$pid\">$title</a>";

                                        $data .="
                                                        <tr>
                                                                <td  align=\"left\" width=\"100%\">
                                                                $ref
                                                                </td>
                                                        </tr>";
                                }
                                $db->free_result($post);
                        }
                }

                if( $data !== false )
                {
                        $res = "
                        <table class=\"tborder\" cellpadding=\"$stylevar[cellpadding]\" cellspacing=\"$stylevar[cellspacing]\" border=\"0\" width=\"100%\" align=\"center\">
                        <tr>
                                <td  align=\"center\" width=\"100%\" class=\"tcat\" style=\"font-size:x-small;\">Recent Posts<br />
                                <a style=\"font-size:xx-small;\" href=\"faq.php?faq=new_faq_item#faq_new_faq_item1\"><u>HTTPpoint is an Ad revenue sharing forum</u></a>
                                </td>
                        <tr>

                        ".$data."

                        <tr>
                                <td  align=\"center\" width=\"100%\" class=\"tcat\" style=\"font-size:x-small;\">
                                <a style=\"font-size:xx-small;\" href=\"showthread.php?t=3526\">Discuss this SEO tool</a>
                                </td>
                        <tr>

                        </table>";
                }
        }
        $db->free_result($threads);

        return $res;
Thanks

FFMG

Xenon 02-26-2006 08:37 PM
hmm, that doesn't reveal the last five posts, but the five last threads posted in

you base your lissting on a threadquery, base it on a post query and use an innerjoin, to get rid of the ammount of queries

FFMG 02-26-2006 09:10 PM
Quote:
Originally Posted by Xenon
hmm, that doesn't reveal the last five posts, but the five last threads posted in
Yes you are right, sorry.

Quote:
Originally Posted by Xenon
you base your lissting on a threadquery, base it on a post query and use an innerjoin, to get rid of the ammount of queries
Sorry, I am not sure I understand, how would you do that?

FFMG

Xenon 02-26-2006 09:15 PM
something like

[sql]
SELECT *
FROM post
INNER JOIN thread USING(threadid)
ORDER BY post.dateline DESC
LIMIT xx[/sql]

that's just a vague statement, but you get what i mean i think

that query is enough for it alone to get all needed information without running more queries additionaly in a loop

FFMG 02-26-2006 09:31 PM
Quote:
Originally Posted by Xenon
something like

[sql]
SELECT *
FROM post
INNER JOIN thread USING(threadid)
ORDER BY post.dateline DESC
LIMIT xx[/sql]
That seems to work great.
I just have two more questions,

How would I get only one post per thread? So that even if 2 or more posts are from the same thread, (and are technically one of the last xx posts), only one would be returned.

And would it be faster to do 'SELECT * ...' or 'SELECT threadid, title...'

Many thanks

FFMG

Xenon 02-27-2006 12:34 PM
well, the first one is a bit tricky

the fastest way is to get for example the last 3 times xx post, and then while running through the results, store the threadids in an array and if a new result is there, which has a threadid already cached throw it away...

the second one: it's better to just use the fieldnames you really need, yes

Marco van Herwaarden 02-27-2006 12:48 PM
Actually, since you are look for the latest X threads that had a new post, changing back to using the thread table as primary should be better.

FFMG 02-27-2006 01:41 PM
Quote:
Originally Posted by MarcoH64
Actually, since you are look for the latest X threads that had a new post, changing back to using the thread table as primary should be better.
Sorry I am not sure I follow, what do you mean?

FFMG

Xenon 02-27-2006 02:42 PM
hmm, not always marco.

he needs some informations of the post itself as far as i understood, and therefore he would have to join the post table, but just joining on the post's dateline could be wrong and isn't that efficient...

of course it's doable, yes, not sure which way would be the fastest, something which could be interesti9ng in running a few benchmark tests

FFMG 03-07-2006 12:38 PM
Quote:
Originally Posted by Xenon
the fastest way is to get for example the last 3 times xx post, and then while running through the results, store the threadids in an array and if a new result is there, which has a threadid already cached throw it away...
Sorry, I have been away for a while.

Wouldn't GROUP BY work in this case?

Code:
SELECT *
FROM post
INNER JOIN thread
USING ( threadid )
GROUP BY (
post.threadid
)
ORDER BY post.dateline DESC
LIMIT xx
FFMG


All times are GMT. The time now is 11:17 AM.
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

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.01777 seconds
  • Memory Usage 1,756KB
  • 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
  • (2)bbcode_code_printable
  • (5)bbcode_quote_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