forum.coppermine-gallery.net
Support => cpg1.5.x Support => cpg1.5 plugins => Topic started by: heavensportal on January 25, 2014, 03:20:42 am
-
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.
-
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?
-
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.
-
So you want a simple table like
user name | comments
--------------------
foo | 47
bar | 11without 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?
-
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.
-
Copy this to a new PHP file in your gallery root:
<?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();
-
copy/pasted into new php called comments.php and uploaded but where am I supposed to go to see the listing?
-
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
-
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
-
Please enable debug mode to get the extended error message. The file name doesn't matter.
-
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
-
I just updated the above code, so it also works with bridged galleries.
-
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
-
Sorry, my mistake. Updated code again.
-
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
-
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
-
Please try the updated code again. Seems that I'm a little bit tired today ;)
-
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. :-*
-
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.
-
Try
<?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();
-
Thank you Andre, but the admins still show in the listing could it be cause it is a bridged gallery?
-
I used the available admin group data from the bridge file. Of course you could replace the following code and insert your actual admin group ID.
Find
".$cpg_udb->field['usertbl_group_id']." NOT IN (".implode(', ', $cpg_udb->admingroups).")and replace with something like
".$cpg_udb->field['usertbl_group_id']." != 123where "123" is the group ID.
-
the 123=admin 1 admin2 admin3?
-
No, you have to insert the group ID of the administrator group.
-
ok, thanks I shall go hunt up where that is located. most likely in forum since bridged.
-
my code now looks like this
<?php
$min_comments = 25;
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']." != 101 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();
I changed it to the code shown in the cpg database but the admins are still showing:
http://thefantasiesattic.net/attic/cpg/top-comments.php
the very first name is an admin, sorry to be such a bother.
-
Try 1 instead of 101, as Coppermine internally adds +100 to the forum's group IDs to avoid conflicts. Also make sure that "thelufias" is assigned to the admin group as primary group.
-
ok I will try one and in the forum we are all assigned group id as 1
ok I did it and we are all still in the list... I may just have to live with it... :-[
unless I am totally misunderstanding something, which would not surprise me
-
ok I will try one and in the forum we are all assigned group id as 1
Have you verified that in the database via e.g. phpMyAdmin?
-
yes sir I surely did here are the images in case I did not verify it right.
-
Is this an SMF1 or SMF2 board? According to the bridge file:
'usertbl_group_id' => 'id_post_group', // name of 'group id' field in users table
'grouptbl_group_id' => 'id_group', // name of 'group id' field in groups tablethe column name in the user table (for SMF2) should be "id_post_group".
It should work if you replace
$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']." != 101 GROUP BY user_name HAVING num_comments >= $min_comments ORDER BY num_comments DESC");
with
$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 group_id != 1 GROUP BY user_name HAVING num_comments >= $min_comments ORDER BY num_comments DESC");
-
yes like your forum here it is 2.0.6 since 2.0.7 is still not behaving.
I checked and have both columns so since this and the others are updated from within the admin area, they must have been added? I don't know but will try with that number and let you know.
-
My line now reads
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 group_id != 19 GROUP BY user_name HAVING num_comments >= $min_comments ORDER BY num_comments DESC");pageheader();
am now getting Critical error
There was an error while processing a database query
-
Please enable debug mode to get the extended error message. Priorly, try if it works when you replace
group_idwith
u.group_id
-
code used:
$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 u.group_id != 19 GROUP BY user_name HAVING num_comments >= $min_comments ORDER BY num_comments DESC");pageheader();
error:
Fatal error: Cannot redeclare cpggetmicrotime() (previously declared in /attic/cpg/include/init.inc.php:25) in attic/cpg/include/init.inc.php on line 27
-
Something went wrong here. The error message you posted is no database error message and regardless of the query should have happened before (i.e. it's not related to our recent changes).
When I visit http://thefantasiesattic.net/attic/cpg/top-comments.php I just get a blank page.
-
ok, this ishows what I got when I clicked the link in your reply. I had debug turned on and for admins only so bet that is why you did not see anything,
Let me start with a fresh file then do the edit you first gave me with the last additional code also.
-
ok, this is the error (I turned debug back on for everyone this time
Critical error
There was an error while processing a database query.
While executing query 'SELECT real_name AS user_name, COUNT(*) as num_comments FROM `thefanta_attic`.smf8_members AS u INNER JOIN cpg14x_comments AS c ON c.author_id = u.id_member WHERE group_id != 19 GROUP BY user_name HAVING num_comments >= 25 ORDER BY num_comments DESC' in top-comments.php on line 7 mySQL error: Unknown column 'group_id' in 'where clause'
File: attic/cpg/include/functions.inc.php - Line: 272
using this code
<?php
$min_comments = 25;
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 group_id != 19 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();
after changing to u.group_id the error is:
While executing query 'SELECT real_name AS user_name, COUNT(*) as num_comments FROM `thefanta_attic`.smf8_members AS u INNER JOIN cpg14x_comments AS c ON c.author_id = u.id_member WHERE u.group_id != 19 GROUP BY user_name HAVING num_comments >= 25 ORDER BY num_comments DESC' in top-comments.php on line 7
mySQL error: Unknown column 'u.group_id' in 'where clause'
-
Replace
group_idwith
id_group
Sorry, my mistake.
-
no need to be sorry, you must go through millions of code daily.
well the page is back up and the debug is on bottom of page but admins are still showing, they just don't want to be left out...LOL
-
Your latest code checks for group ID 19, but your admin group ID is probably 1?
-
eeek, I was still using the group post id number...changed it and now the admins are no longer listed.....happy dance.
Thanks for putting up with this old woman trying to learn stuff.
Have a wonderful Valentine's day.
-
It seems the bridge file is documented wrongly, at least from our result here:
'usertbl_group_id' => 'id_post_group', // name of 'group id' field in users table
'grouptbl_group_id' => 'id_group', // name of 'group id' field in groups table
Basing on that documentation I created my first code, using usertbl_group_id. Instead, it seems we need to use grouptbl_group_id.
Please try if the following code works as expected:
<?php
$min_comments = 25;
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['grouptbl_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();
-
:-*
Yes everything is as it was with the last code before this one...replace with this one and admins are still gone, thank you so very much.