Making my query more efficient

[uber]

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.


$getthreads = $DB_site->query("
SELECT DISTINCT thread.title,thread.lastposter,forum.title as forumtitle,thread.forumid,thread.views,thread.repl ycount,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]

Use the IN construct. Assemble all of the threadids in question and then run the single query:


SELECT dateline, pagetext, threadid, postid FROM {TABLE_PREFIX}post WHERE threadid IN ($threadids)

Implode the array in question using commas to the variable $threadids.

[uber]

Use the IN construct. Assemble all of the threadids in question and then run the single query:


SELECT dateline, pagetext, threadid, postid FROM {TABLE_PREFIX}post WHERE threadid IN ($threadids)

Implode the array in question using commas to the variable $threadids.
Perfect!

Thanks filburt