Profile Enhancements - Unique Avatars

*** Staff note: The author of this modification has passed away in a diving accident. We wish his family all strength in dealing with this traggic issue. ***

Have you ever been frustrated by the fact that many of your users choose the same avatars from your pre-defined avatars?

Now you can prevent users from selecting avatars that others are already using.



Features

• No template or file edits, just a single .xml file to install
• Phrase-based - update language strings as you like
• Can either omit the used avatars from the display, or show them as unavailable
• Can optionally display a count of how many times each avatar is used
• All options configurable via the admin control panel
• Install routine adds an additional index to ensure database performance not impacted on larger boards

The inspiration from this mod came from a similar one for vBulletin 3.0 by VBDev. Even though there is almost no code in common between these mods I've sought and obtained his permission to release this.



Installation (30 seconds)

• Download the attached .xml file
• Go into your admin control panel -> Plugins & Products -> Manage Products -> Add/Import Product.
• Press the browse button to locate the .xml file you just downloaded click OK.
• Ensure that "Allow Overwrite" is set to "Yes" and press "Import"
• Note: requires MySQL version 4.1 or higher.

Configuration
VBulletin Options -> Unique Avatar

Version History
1.0 - Initial release (02.06.2007)
1.1 - 1 minor bugfix (20.01.2008)

See screenshots for further details.

[mfyvie]

Hi Andy, I can't think why that is happening. The query is correct. Can you confirm that you aren't using any table prefixes (in other words, the tables in your database are actually called avatar and user). Also please tell us the version of php and mysql that you are using. You are the first to report any problems with this, so I'm interested to know if there is something different about your setup.

Mark

[txspaderz]

Does this conflict at all with the default Avatar / No Avatar image?

[mfyvie]

Originally Posted by txspaderz

Does this conflict at all with the default Avatar / No Avatar image?

I don't know - try it and see. It's fairly specific in what it does, so chances are there won't be a conflict. Please update this thread with the result.

[Andyucs]

Originally Posted by mfyvie

Hi Andy, I can't think why that is happening. The query is correct. Can you confirm that you aren't using any table prefixes (in other words, the tables in your database are actually called avatar and user). Also please tell us the version of php and mysql that you are using. You are the first to report any problems with this, so I'm interested to know if there is something different about your setup.

Mark

phpMyAdmin - 2.10.0.2

PHP 4.3.11

no table prefixes

hope that helps

[mfyvie]

Originally Posted by Andyucs

phpMyAdmin - 2.10.0.2

PHP 4.3.11

no table prefixes

hope that helps

Mysql version?

[Andyucs]

sorry

MySQL client version: 4.1.21

[mfyvie]

Originally Posted by Andyucs

sorry

MySQL client version: 4.1.21

Hi Andy, I sent you a PM a couple of days ago offering to help. If I don't hear back from you via this PM then I'll assume you've resolved this issue.

[mfyvie]

Would you like something that will automatically REMOVE the avatar for those users who already have the same avatar as others?

The following two queries will do this. The first one will report how many duplicates you have, the second will delete the avatars.

WARNING: The second query will actually change your database. I've tested it on my system and am confident that it works. However, if you run this query on a live system and it breaks it, and you didn't back it up - don't come running to me about it!

First query:

select avatarid,count(1) from user
where avatarid>0
group by avatarid
having count(1) > 1
order by 2 desc

This will simply report how many cases of duplication you have. Once you can see the extent of the problem you can run this:

Second query:

update user u1, user u2
set u1.avatarid=0
where u1.avatarid=u2.avatarid
and u1.userid!=u2.userid
and u1.userid>u2.userid
and u1.avatarid>0
and u2.avatarid>0

You can then re-run the first query to confirm that it worked - if it did, you'll get 0 rows returned.

Note that these queries, as well as the one I posted a few days back will work even if you don't have this mod installed (might be a bit slow due to lack of an extra index though).

If there are two users using the same avatar it won't delete both of their avatars - the lowest userID (usually the oldest member) gets to keep theirs, only the "new" guy will lose theirs.

Once you've used this to eliminate all the duplicates you can be sure that no more will happen when you are running this mod.

The only side effect is the users might wonder where their avatar went...

By the way - in case anyone thinks I am any good at making or changing these queries - I'm not. I gave it to some database gurus to do. They had to scratch their heads a bit before coming up with the final solution. They said it wasn't so simple to figure out.

[JD45]

Thanks for the hack and agreed, this should be a default feature in VB.

[JordanT]

I have the same problem as Andy:

Database error in vBulletin 3.6.6:

Invalid SQL:

            SELECT avatar.avatarid, avatar.title, avatar.minimumposts, avatar.avatarpath, avatar.imagecategoryid, avatar.displayorder, count(1)
            FROM vb_user , vb_avatar
            WHERE user.avatarid = avatar.avatarid
            AND avatar.imagecategoryid = 4
            AND minimumposts <= 64
            GROUP by avatar.avatarid
            UNION
            SELECT avatarid, title, minimumposts, avatarpath, imagecategoryid, displayorder, 0
            FROM vb_avatar
            WHERE avatarid not in (select distinct avatarid from user)
            AND imagecategoryid = 4
            AND minimumposts <= 64
            ORDER by displayorder, title
            LIMIT 0,10;

MySQL Error  : Table 'host4_vb.user' doesn't exist
Error Number : 1146
Date         : Tuesday, June 5th 2007 @ 05:32:23 PM
Script       : http://www.host4posts.com/profile.php?do=editavatar
Referrer     : http://www.host4posts.com/usercp.php
IP Address   : 66.170.53.178
Username     : Jordan
Classname    : vB_Database