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:

////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.signatu re 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:


$fuserinfo=$DB_site->query("
SELECT user.usertitle,user.avatarid,usertextfield.signatu re,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 :)!