vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   Making my query more efficient (https://vborg.vbsupport.ru/showthread.php?t=65192)

uber 05-16-2004 05:00 PM
Making my query more efficient
 
I have a query that looks up the last ten threads that the user has participated in. I also have it lookup the pagetext field of the last post for the title link. The problem I have is, I cannot figure out how to query the last post without running the query in another loop causing an additional ten queries to run.


Code:
$getthreads = $DB_site->query("
        SELECT DISTINCT thread.title,thread.lastposter,forum.title as forumtitle,thread.forumid,thread.views,thread.replycount,thread.threadid,thread.lastpost FROM post
        LEFT JOIN thread ON post.threadid=thread.threadid
        LEFT JOIN forum ON thread.forumid=forum.forumid
        WHERE post.userid = '$bbuserinfo[userid]' ORDER BY thread.lastpost DESC
        LIMIT 10
");

        while($threads = $DB_site->fetch_array($getthreads))
        {

        $getlpbody = $DB_site->query("
                SELECT dateline, pagetext FROM post
                WHERE dateline = $threads[lastpost]
        ");       
       
                $lastpostbody = $DB_site->fetch_array($getlpbody);
                $threads['date'] = vbdate($vboptions['calformat2'],$threads['lastpost']);
                $threads['time'] = vbdate($vboptions['timeformat'],$threads['lastpost']);
                $trimmed_thread_title = fetch_trimmed_title($threads['title'], 32);
                $trimmed_forum_title = fetch_trimmed_title($threads['forumtitle'], 30);
                $trimmed_message_body = fetch_trimmed_title($lastpostbody['pagetext'],250);
                $row_color = ($row_count % 2) ? $color1 : $color2;
                $row_count++;
               
                eval('$usersthreads .= "' . fetch_template('mylizard_mythreads') . '";');
        }

filburt1 05-16-2004 06:57 PM
Use the IN construct. Assemble all of the threadids in question and then run the single query:

[sql]
SELECT dateline, pagetext, threadid, postid FROM {TABLE_PREFIX}post WHERE threadid IN ($threadids)
[/sql]
Implode the array in question using commas to the variable $threadids.

uber 05-17-2004 11:39 AM
Quote:
Originally Posted by filburt1
Use the IN construct. Assemble all of the threadids in question and then run the single query:

[sql]
SELECT dateline, pagetext, threadid, postid FROM {TABLE_PREFIX}post WHERE threadid IN ($threadids)
[/sql]
Implode the array in question using commas to the variable $threadids.
Perfect!

Thanks filburt


All times are GMT. The time now is 01:45 PM.

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.01932 seconds
  • Memory Usage 1,717KB
  • 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
  • (1)bbcode_code_printable
  • (1)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (3)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