comments in gallery

[heavensportal]

I was wondering if there is some plugin or code to add that can show the comments count of each member without needing to go to users and going through all the pages. Something like SMF has for posters.

[Αndré]

Where do you want to display that information? Next to the user name for each comment? On a separate statistics page? Or maybe somewhere else?

[heavensportal]

Where do you want to display that information? Next to the user name for each comment? On a separate statistics page? Or maybe somewhere else?

A separate page would be ok or even some code to apply to my forum so other members will be inspired to comment on the remarkable artwork.

[Αndré]

So you want a simple table like

Code Select Expand


user name | comments
--------------------
foo       | 47
bar       | 11
without pagination, right? Do you want to hide users without comments, i.e. is there a minimum number of comments a user needs to appear in the list?

[heavensportal]

yes simple like that  so I can put it in a block in my smf forum or in the gallery itself, the easiest one for you to do.

user name total comments made are the only thing needed and only the ones that comment should be in there.

I am trying to get them to comment more so the minimum amount would be say 10 comments, then they will see how many as they accumulate them...

This way the members can see the listing too for incentive.

I hope that makes sense.

[Αndré]

Copy this to a new PHP file in your gallery root:

Code Select Expand

<?php

$min_comments = 10;

define('IN_COPPERMINE', true);
require('include/init.inc.php');
$result = cpg_db_query("SELECT ".$cpg_udb->field['username']." AS user_name, COUNT(*) as num_comments FROM ".$cpg_udb->usertable." AS u INNER JOIN {$CONFIG['TABLE_COMMENTS']} AS c ON c.author_id = u.".$cpg_udb->field['user_id']." GROUP BY user_name HAVING num_comments >= $min_comments ORDER BY num_comments DESC");
pageheader();
starttable();
echo "<tr><td><strong>User name</strong></td><td><strong>Comments</strong></td></tr>";
while ($row = mysql_fetch_assoc($result)) {
    echo "<tr><td>{$row['user_name']}</td><td>{$row['num_comments']}</td></tr>";
}
endtable();
pagefooter();

[heavensportal]

copy/pasted into new php called comments.php and uploaded but where am I supposed to go to see the listing?

[Αndré]

Visit the file with your browser. If you want to add it to the menu, you could use the custom menu link config option:
- http://documentation.coppermine-gallery.net/en/configuration.htm#admin_theme_custom_lnk_name
- http://documentation.coppermine-gallery.net/en/configuration.htm#admin_language_custom_lnk_url

[heavensportal]

Maybe I did something wrong, somehow because when I type the url into the browser for the file I get

Critical error
There was an error while processing a database query

I even changed the name to top-comments.php with same results

[Αndré]

Please enable debug mode to get the extended error message. The file name doesn't matter.

[heavensportal]

I also added a ?> at the end of the file too, was that wrong?

Here is what I get for the debug

Critical error

There was an error while processing a database query.

While executing query 'SELECT user_name, COUNT(*) as num_comments FROM `thefanta_attic`.smf8_members AS u INNER JOIN cpg14x_comments AS c ON c.author_id = u.user_id GROUP BY user_name HAVING num_comments >= 10 ORDER BY num_comments DESC' in top-comments.php on line 7 mySQL error: Unknown column 'user_name' in 'field list'


File: /home/thefanta/public_html/attic/cpg/include/functions.inc.php - Line: 272

[Αndré]

I just updated the above code, so it also works with bridged galleries.

[heavensportal]

My gallery hates me:

Critical error

There was an error while processing a database query.

While executing query 'SELECT user_name, COUNT(*) as num_comments FROM `thefanta_attic`.smf8_members AS u INNER JOIN cpg14x_comments AS c ON c.author_id = u.real_name GROUP BY user_name HAVING num_comments >= 10 ORDER BY num_comments DESC' in top-comments.php on line 7 mySQL error: Unknown column 'user_name' in 'field list'


File: /home/thefanta/public_html/attic/cpg/include/functions.inc.php - Line: 272

[Αndré]

Sorry, my mistake. Updated code again.

[heavensportal]

yes! it appears now however, it is pulling comments from the forum post counts and not the actual gallery comments done.

So sorry to put you through all this

[heavensportal]

oops forgot to add that the totals are waaaaaaay off.

one is listed at 3000+ when she only has 95 forum posts and 5 gallery comments

[Αndré]

Please try the updated code again. Seems that I'm a little bit tired today

[heavensportal]

Eureka it is showing now, thank you so very much and sorry to put you through the problem dealing with my silly site....it never behaves normal even with out of the box, unedited software.

Thank you very much.

[heavensportal]

It's me again...is there any way to NOT show the admins comments in that coding you were so kind to create for this.

[Αndré]

Try

Code Select Expand

<?php

$min_comments = 1;

define('IN_COPPERMINE', true);
require('include/init.inc.php');
$result = cpg_db_query("SELECT ".$cpg_udb->field['username']." AS user_name, COUNT(*) as num_comments FROM ".$cpg_udb->usertable." AS u INNER JOIN {$CONFIG['TABLE_COMMENTS']} AS c ON c.author_id = u.".$cpg_udb->field['user_id']." WHERE ".$cpg_udb->field['usertbl_group_id']." NOT IN (".implode(', ', $cpg_udb->admingroups).") GROUP BY user_name HAVING num_comments >= $min_comments ORDER BY num_comments DESC");
pageheader();
starttable();
echo "<tr><td><strong>User name</strong></td><td><strong>Comments</strong></td></tr>";
while ($row = mysql_fetch_assoc($result)) {
    echo "<tr><td>{$row['user_name']}</td><td>{$row['num_comments']}</td></tr>";
}
endtable();
pagefooter();