07-07-12 04:18 AM - Post#86190    

Hi,
Subscribers on my site are allowed avatars as well as other enhanced permissions. Non subscribers are not. If they do not renew a subscription they loose their enhanced permissions but their avatars still show. I can delete them as I notice it Once I do this the permission set prevents them using/uploading another unless they resubscribe.

I need a query to show me non-subscribers using an avatar so I can clear them out. May prompt some renewals.

Many thanks!

(For anyone else uses avatars as part of their subs - if I get no response here I'll me asking a 3rd party to devise the query for me. Can't imagine it will be expensive. If anyone else needs it I will gladly pass it on. If the cost is a bit more substantial I may ask for a small payment)

07-08-12 09:26 AM - Post#86191    

    In response to sb1963

Try this

SELECT a.user_id, a.info_display_name
FROM fbb_user_info a, fbb_user_groups b
WHERE a.user_id = b.user_id AND b.group_id <> '12' AND a.info_avatar IS NOT NULL order by user_id

You will have to substitute your group id number for your subscription group where my group number is 12.

This should work but if they are in the another group as well as the subscription group, they will show up in the list. I'm not sure how you have your forums set up, whether everyone is in just one group or can be in multiple groups. It might need tweaking but it is a good start.

DoorDing.com

07-08-12 09:40 AM - Post#86192    

    In response to dman

Well, maybe scratch that. I think that just tells when they have an avatar that was uploaded. Let me think some more about it after coffee.

DoorDing.com

07-08-12 09:56 AM - Post#86193    

    In response to dman

OK I believe this should tell you if they have anything selected but "No Avatar" in their profile. Again, this will show people in your subscription group if they are also in another group.

SELECT a.user_id, a.info_display_name, a.info_avatar_type
FROM fbb_user_info a, fbb_user_groups b
WHERE a.user_id = b.user_id AND b.group_id <> '12' AND a.info_avatar_type <> 0 order by user_id

DoorDing.com

07-08-12 12:14 PM - Post#86194    

    In response to dman

Or you could also show the group ids with the query by using this

SELECT a.user_id, a.info_display_name, a.info_avatar_type, b.group_id
FROM fbb_user_info a, fbb_user_groups b
WHERE a.user_id = b.user_id AND b.group_id <> '12' AND a.info_avatar_type <> 0 order by user_id

DoorDing.com

07-09-12 05:21 PM - Post#86195    

    In response to dman

Many thanks! I'll give that a go.

07-11-12 02:58 PM - Post#86196    

    In response to sb1963

Did it in Mysql admin so I could export to spreadsheet. Used the filter & easy to see 20 odd users who'd not renewed & still had avatars.
Excellent - many thanks

07-11-12 03:37 PM - Post#86197    

    In response to sb1963

No problem. I'm sure there is a way to filter out the ones in the sub group with a statement but I'm not all that great at them.

DoorDing.com