vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   lessening queries (https://vborg.vbsupport.ru/showthread.php?t=62172)

AN-net 03-05-2004 12:55 AM
lessening queries
 
ok i know 25 queries is alot so can someone give me some suggestion on how to fix up this code or give me some suggestions on how to lessen the queries:
PHP Code:
////Begin Entry///
if($view=="entry"){
/*Begin Checking for Image Id*/
if(empty($imgid)){
        $url="gallery.php?view=index";
        $errormessage="There was no image identity identified.If you followed a bad link please report it to a board administrator. Your are now being redirected to the gallery index.";    
        eval('print_output("' . fetch_template('STANDARD_REDIRECT') . '");');
}
/*End Checking for Image Id*/
else{
$fentry= $DB_site->query("SELECT * FROM gallery WHERE image_id='$imgid' AND gallery_id='$id'");
while($entry= $DB_site->fetch_array($fentry))
{
$imagesize= getimagesize($entry[image_url]);
/*########Begin Calculating Rating######*/
if($entry[image_totalvotes]==0){
$rating=0;
}
else{
$calcrating= $entry[image_totalrating]/$entry[image_totalvotes];
$rating= round($calcrating);
}
/*########End Calculating Rating######*/

/*######Begin Constructing Entry Date########*/
$entryimagedate=date("m/d/Y - g:i A",$entry[image_date]);
/*######End Constructing Entry Date########*/
    
eval('$entrybits .= "' . fetch_template('gallery_entry_lvl1') . '";');
}
    mysql_free_result($fentry);
/*###############Begin Displaying Comments###############*/
$fcomment=$DB_site->query("SELECT * FROM gallery_comments WHERE image_id='$imgid' AND gallery_id='$id' ORDER BY comment_num ASC");
while($comment=$DB_site->fetch_array($fcomment)){
$fuserinfo=$DB_site->query("SELECT user.usertitle,user.avatarid,usertextfield.signature FROM user,usertextfield WHERE user.userid='$comment[commenter_id]' AND usertextfield.userid='$comment[commenter_id]'");
$commenter=$DB_site->fetch_array($fuserinfo);
if($comment[show_sig]==1){
    require_once('./includes/functions_bbcodeparse.php');
    $commenter['signature'] = parse_bbcode($commenter['signature'], 0, 1);
    $show['signature'] = true;
}
else{
    $show['signature'] = false;
}
if($comment[show_avatar]==1){
$favatar=$DB_site->query("SELECT user.userid,avatar.avatarpath FROM user,avatar WHERE user.userid='$comment[commenter_id]' AND user.avatarid=avatar.avatarid");
$avatar=$DB_site->fetch_array($favatar);
if($commenter[avatarid]==0){
$show['customava']="true";
}
else{
$show['customava']="false";
}
}
$DB_site->free_result($favatar);
$commentdate=date("m/d/Y - g:i A",$comment[comment_date]);
eval('$commentbits .= "' . fetch_template('gallery_entry_commentbits') . '";');
}
/*###############End Displaying Comments###############*/
$DB_site->free_result($favatar);
$DB_site->free_result($fuserinfo);
mysql_free_result($fcomment);
    $DB_site->query("UPDATE gallery SET image_views=image_views+1 WHERE gallery_id='$id' AND image_id='$imgid'");
/*#######Begin Constructing Nav Bits##########*/
$fgalnav=$DB_site->query("SELECT gallery_index.gallery_name,gallery.image_name FROM gallery,gallery_index WHERE gallery_index.gallery_id='$id' AND gallery.image_id='$imgid'");
$galnav=$DB_site->fetch_array($fgalnav);
    $navbits = array();
    $navbits["#"] = "Gallery";
    $navbits["gallery.php?view=index"] = "Gallery Index";
    $navbits["gallery.php?view=gal&id=$id"] = $galnav[gallery_name];
    $navbits[''] = $galnav[image_name];

    $navbits = construct_navbits($navbits);
/*#######End Constructing Nav Bits##########*/
    eval('$navbar = "' . fetch_template('navbar') . '";');
    mysql_free_result($fgalnav);
    eval('$newcomment = "' . fetch_template('gallery_entry_newcomment') . '";');
    eval('print_output("' . fetch_template('gallery_entry') . '");');
}
}
/////End Entry////// 

AN-net 03-06-2004 08:44 PM
anyone?

Dean C 03-06-2004 09:45 PM
It's because you're getting the avatar inside of a loop. Hence increasing your queries dramatically. Do a LEFT JOIN in the query - or even still there may be a vB3 function to get a user's avatar without using any queries getting it from a cache but don't quote me on it :)

EDIT- direct from showthread.php:

Code:
" . iif($vboptions['avatarenabled'], "LEFT JOIN " . TABLE_PREFIX . "avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid = user.userid)") . "

AN-net 03-06-2004 09:46 PM
can you explain where i should put the left join and how to use it? cause i never understood those>_<

Dean C 03-06-2004 09:51 PM
Use this:

PHP Code:
$fuserinfo=$DB_site->query("
SELECT user.usertitle,user.avatarid,usertextfield.signature,avatar.* FROM user,usertextfield
" . iif($vboptions['avatarenabled'], "LEFT JOIN " . TABLE_PREFIX . "avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid = user.userid)") . "
WHERE user.userid='$comment[commenter_id]' AND usertextfield.userid='$comment[commenter_id]'"); 
Untested :)

AN-net 03-06-2004 10:24 PM
thanks so much! 2 less queries for one comment! thanks alot dean:)

Dean C 03-07-2004 10:55 AM
Glad it works for you :)!


All times are GMT. The time now is 08:56 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.01134 seconds
  • Memory Usage 1,762KB
  • 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
  • (2)bbcode_php_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (7)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