Invision Community 4: SEO, prepare for v5 and dormant account notifications Matt November 11, 2024Nov 11
Posted November 10, 20222 yr 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,
November 10, 20222 yr Community Expert There is no built in way to do this within the software. You would need some sort of third party resource to do this.
November 11, 20222 yr 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
November 11, 20222 yr @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;
November 11, 20222 yr Author Wow love the quick responses... I will test both and get back to you guys!! Thanks!
November 11, 20222 yr Author 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.
November 11, 20222 yr 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;
November 11, 20222 yr 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)
November 11, 20222 yr Author 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)!