Count the number of pictures uploaded by members

[mtlurb]

Hello,

My members upload a lot of pictures in the forum (NOT gallery).

And I can't find a way to count how many pictures each members uploaded.

I looked everywhere and can't find anything... 

Is there a widget, plugin or maybe a SQL statement I could run that could get me this info?

(I want to send gifts to the most prolific members).

Thanks,

[Randy Calvert]

There is no built in way to do this within the software.  You would need some sort of third party resource to do this.  

[Nathan Explosion]

Did this a while back - adds a button in the 'Files' section of the ACP, and allows you to export the listing to a CSV file

(NE) Download attachments report 1.0.0.xml

[Richard Arch]

@mtlurb Check this sql out, I've tested this in phpMyAdmin.

Adjust the table prefix (ibf_) for your tables and the type ('jpg','jpeg','gif','png','bmp') of image files to count. 

You can adjust how many days of records are to be included, currently it counts the last 30 days (interval 30 day) from the point it is ran.

SELECT attach_member_id, (SELECT name from ibf_core_members where member_id=attach_member_id) as Name, COUNT(*) as No_of_files
FROM ibf_core_attachments
WHERE attach_date > UNIX_TIMESTAMP(DATE_FORMAT(now() - interval 30 day , '%Y/%m/%d'))
and attach_ext in ('jpg','jpeg','gif','png','bmp')
group by attach_member_id
order by 3 desc;

 

[mtlurb]

Wow love the quick responses... I will test both and get back to you guys!!

Thanks!

[mtlurb]

4 hours ago, Nathan Explosion said:

Did this a while back - adds a button in the 'Files' section of the ACP, and allows you to export the listing to a CSV file

(NE) Download attachments report 1.0.0.xml 3.77 kB · 2 downloads

Just installed this, and I get weird dates going up to 2029... 😕 Not sure what's the issue.

 

[Richard Arch]

Ah, need to alter the sql to only count images in forums.

SELECT attach_member_id, (SELECT name from ibf_core_members where member_id=attach_member_id) as Name, COUNT(*) as No_of_files
FROM ibf_core_attachments
WHERE attach_date > UNIX_TIMESTAMP(DATE_FORMAT(now() - interval 30 day , '%Y/%m/%d'))
and attach_ext in ('jpg','jpeg','gif','png','bmp')
and exists (select 1 from ibf_forums_posts where author_id=attach_member_id and post like CONCAT('%data-fileid="', attach_id, '"%'))
group by attach_member_id
order by 3 desc;

 

[Nathan Explosion]

You'll probably have to do some formatting of the date in Excel to get correct values - I'm just exporting what is displayed in the ACP.

If you can't get that sorted, then here's a change that will export the date as a UNIX timestamp instead.

(NE) Download attachments report 1.0.1.xml

You can then use this formula to recalcultate the UNIX timestamp to a numerical value that Excel can use for a date/time:

=(C2/86400)+DATE(1970,1,1)

 

[mtlurb]

5 hours ago, Nathan Explosion said:

You'll probably have to do some formatting of the date in Excel to get correct values - I'm just exporting what is displayed in the ACP.

If you can't get that sorted, then here's a change that will export the date as a UNIX timestamp instead.

(NE) Download attachments report 1.0.1.xml 4.54 kB · 2 downloads

You can then use this formula to recalcultate the UNIX timestamp to a numerical value that Excel can use for a date/time:

=(C2/86400)+DATE(1970,1,1)

 

This is it!

its perfect! Thanks alot:)

Some members will be very happy with their gifts (I hope)!