lessening queries

[AN-net]

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]

anyone?

[Dean C]

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:

" . 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]

can you explain where i should put the left join and how to use it? cause i never understood those>_<

[Dean C]

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]

thanks so much! 2 less queries for one comment! thanks alot dean

[Dean C]

Glad it works for you !