[Solved]: Number of comments below thumbnail?

[lurkalot]

Hi all,

I'm running SMF 1.1.5 / Tinyportal 0.9.8, bridged with Coppermine 1.4.18, and currently using coppermineFetch to pull images on to my front page (Latest gallery images).  I have searched but not found the answer.

Is it possible, and can someone tell me what code I need to show the number of comments under each thumb? please.  So far I have it showing the title, who uploaded the pic, and the number of views. 

Code I'm using as follows, Added to a center phpbox in Tinyportal.

Code Select Expand


echo "<center>";
chdir("../gallery/cpmfetch/");
include "cpmfetch.php";                   
$objCpm = new cpm();
$options = array("imageheight"=>"80","subtitle" => "<center>{{pTitle}} <br> {{pHits}} Views <center> <FONT color=#ff0000 size=2>Image By</FONT></STRONG> <br> {{pOwner_name}}</center>");
$objCpm->cpm_viewLastAddedMedia(2,4,$options,$filter);
$objCpm->cpm_close();
chdir("../../");
echo '<a href="http://cameracraniums.com/gallery?action=gallery">View more latest images</a>';
echo "</center>";


And a link to my site, www.cameracraniums.com

[Nibbler]

What MySQL version?

[lurkalot]

What MySQL version?

Thanks, according to my server control panel it's,  5.0.51a-community

[Nibbler]

Edit your cpmfetch_config.php file, look for

Code Select Expand

'cfSQLPictureSelect' => 'p.filepath AS pFilepath, p.filename AS pFilename, p.aid AS pAid, p.filesize AS pFilesize, p.title AS pTitle, p.caption AS pCaption, p.owner_name AS pOwner_name, p.owner_id as pOwnerId, p.ctime AS pCtime, p.hits AS pHits, p.pid AS pPid, p.pic_rating AS pPic_Rating,  p.votes AS pVotes, p.pwidth AS pWidth, p.pheight AS pHeight, p.user1 as pUser1, p.user2 as pUser2, p.user3 as pUser3, p.user4 as pUser4, c.cid as cCid, c.name as cName, c.description as cDescription, c.pos as cPos, c.parent as cParent, c.thumb as cThumb',


And add something to get the comment count, like this:

Code Select Expand


'cfSQLPictureSelect' => 'p.filepath AS pFilepath, p.filename AS pFilename, p.aid AS pAid, p.filesize AS pFilesize, p.title AS pTitle, p.caption AS pCaption, p.owner_name AS pOwner_name, p.owner_id as pOwnerId, p.ctime AS pCtime, p.hits AS pHits, p.pid AS pPid, p.pic_rating AS pPic_Rating,  p.votes AS pVotes, p.pwidth AS pWidth, p.pheight AS pHeight, p.user1 as pUser1, p.user2 as pUser2, p.user3 as pUser3, p.user4 as pUser4, c.cid as cCid, c.name as cName, c.description as cDescription, c.pos as cPos, c.parent as cParent, c.thumb as cThumb, (SELECT COUNT(*) FROM ' . $this->cfg['table_prefix'] . 'comments WHERE pid = p.pid) AS pComCount',


If the variable doesn't work then hardcode the name of the comments table.

You should then be able to use {{pComCount}} to get the number of comments.

[lurkalot]

Thanks for trying to help.  I added the code you gave to my cpmfetch_config.php file, but it just displays the error message below

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE pid = p.pid) AS pComCount, a.aid AS aAid, a.title AS aTitle, a.description' at line 1
SELECT p.filepath AS pFilepath, p.filename AS pFilename, p.aid AS pAid, p.filesize AS pFilesize, p.title AS pTitle, p.caption AS pCaption, p.owner_name AS pOwner_name, p.owner_id as pOwnerId, p.ctime AS pCtime, p.hits AS pHits, p.pid AS pPid, p.pic_rating AS pPic_Rating, p.votes AS pVotes, p.pwidth AS pWidth, p.pheight AS pHeight, p.user1 as pUser1, p.user2 as pUser2, p.user3 as pUser3, p.user4 as pUser4, c.cid as cCid, c.name as cName, c.description as cDescription, c.pos as cPos, c.parent as cParent, c.thumb as cThumb, (SELECT COUNT(*) FROM WHERE pid = p.pid) AS pComCount, a.aid AS aAid, a.title AS aTitle, a.description AS aDescription, a.visibility as aVisibility, a.pos as aPos, a.category as aCategory, a.thumb as aThumb, a.keyword as aKeyword FROM cpg_pictures AS p LEFT JOIN cpg_albums AS a ON p.aid = a.aid LEFT JOIN cpg_categories AS c ON a.category = c.cid WHERE 1 AND p.approved='YES' AND ((a.visibility = 0 OR a.visibility IS NULL) AND (a.alb_password IS NULL OR a.alb_password = "")) ORDER BY p.ctime DESC LIMIT 0,8

Can you tell me how to hardcode the name of the comments table, I haven't a clue where to start. 

[Nibbler]

Like this:

Code Select Expand


'cfSQLPictureSelect' => 'p.filepath AS pFilepath, p.filename AS pFilename, p.aid AS pAid, p.filesize AS pFilesize, p.title AS pTitle, p.caption AS pCaption, p.owner_name AS pOwner_name, p.owner_id as pOwnerId, p.ctime AS pCtime, p.hits AS pHits, p.pid AS pPid, p.pic_rating AS pPic_Rating,  p.votes AS pVotes, p.pwidth AS pWidth, p.pheight AS pHeight, p.user1 as pUser1, p.user2 as pUser2, p.user3 as pUser3, p.user4 as pUser4, c.cid as cCid, c.name as cName, c.description as cDescription, c.pos as cPos, c.parent as cParent, c.thumb as cThumb, (SELECT COUNT(*) FROM cpg_comments WHERE pid = p.pid) AS pComCount',


[lurkalot]

Thank you Nibbler, that has worked nicely.