vb.org Archive - Profile Enhancements

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vBulletin 3.6 Add-ons (https://vborg.vbsupport.ru/forumdisplay.php?f=194)

- - Profile Enhancements - Unique Avatars (https://vborg.vbsupport.ru/showthread.php?t=148711)

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

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

Quote:

Originally Posted by txspaderz (Post 1260313)

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.

Quote:

Originally Posted by mfyvie (Post 1260297)

phpMyAdmin - 2.10.0.2

PHP 4.3.11

no table prefixes

hope that helps

Quote:

Originally Posted by Andyucs (Post 1260322)

phpMyAdmin - 2.10.0.2

PHP 4.3.11

no table prefixes

hope that helps

Mysql version?

sorry

MySQL client version: 4.1.21

Quote:

Originally Posted by Andyucs (Post 1260347)

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.

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:

Code:

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:

Quote:

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.

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

I have the same problem as Andy:

Code:

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

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (5)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (4)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01088 seconds Memory Usage 1,746KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (5)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (4)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: