amount of album comments on forumhome

[Taragon]

Hello,

Currently I have a product installed which shows all latest album pictures on forumhome.
Now I would also like to include the amount of comments to each album picure to this mod.

Currently it looks like this:

PHP Code:

if (
    (
        $vbulletin->options['socnet'] & $vbulletin->bf_misc_socnet['enable_albums']
        AND
        $permissions['genericpermissions'] & $vbulletin->bf_ugp_genericpermissions['canviewmembers']
        AND
        $permissions['albumpermissions'] & $vbulletin->bf_ugp_albumpermissions['canviewalbum']
        AND
        $vbulletin->options['afm_onoff']
    )
)
{
    $displayrecords = ($vbulletin->options['afm_sayi'] ? $vbulletin->options['afm_sayi'] : 4);
        switch ($vbulletin->options['afm_dt'])
        {
            case 1: $sorgu = "ORDER BY dateline DESC LIMIT 0, $displayrecords"; break;
      case 2: $sorgu = "ORDER BY rand(NOW()) DESC LIMIT 0, $displayrecords"; break;
    }  
  $lpictures = $db->query("
  SELECT albumpicture.*, album.*, user.username, user.usergroupid,
  IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid 
  FROM " . TABLE_PREFIX . "albumpicture AS albumpicture
  LEFT JOIN " . TABLE_PREFIX . "album AS album ON(album.albumid = albumpicture.albumid)
  LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.userid = album.userid)
  WHERE album.state = 'public' 
  $sorgu  
  ");
  if ($db->num_rows($lpictures))
  {
    while ($lpicture = $db->fetch_array($lpictures))
    {
    $lpicture['musername'] = fetch_musername($lpicture);
    $lpicture['title'] = trim(strip_quotes($lpicture['title']));

    eval('$resimler .= "' . fetch_template('afm_picture') . '";');
    }
  }
  $db->free_result($lpictures);
  eval('$resimler = "' . fetch_template('afm_latestfm') . '";');
} 


By looking at the debug info I need to implement parts of this

PHP Code:

SELECT SQL_CALC_FOUND_ROWS
    picturecomment.*, user.*, picturecomment.ipaddress AS messageipaddress
    ,deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason
    ,avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight, customavatar.width_thumb AS avwidth_thumb, customavatar.height_thumb AS avheight_thumb, filedata_thumb, NOT ISNULL(customavatar.userid) AS hascustom
    
FROM picturecomment AS picturecomment
LEFT JOIN user AS user ON (picturecomment.postuserid = user.userid)
LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid)
LEFT JOIN deletionlog AS deletionlog ON (picturecomment.commentid = deletionlog.primaryid AND deletionlog.type = 'picturecomment')

WHERE picturecomment.pictureid = 71
    AND (picturecomment.state IN ('visible','moderation','deleted'))

    
ORDER BY picturecomment.dateline
LIMIT 0, 10 


Would someone be able to assist me with this perhaps, as I'm just starting to figure out php/mysql?

Demo: http://die16-clan.com/forum.php?styleid=141

[Dismounted]

[sql]SELECT COUNT(commentid) AS comments
FROM picturecomment
WHERE pictureid = X[/sql]
You probably just want to add this onto the select declarations:

COUNT(commentid) AS count