[bulbasnore]

WARNING: you could lose your whole board mucking with SQL and this procedure could more easily corrupt your data, so if you don't have enough experience or you don't have a backup, run for your life! (or use WetWired's cool trick above).

BTW, this will PROBABLY ONLY WORK if you store your custom avatars in your database!

Our board doesn't have much activity with custom avatars, only staff are allowed to have them, except for certain days when we open them up to regular members. For April Fools, we got a new set of Avatars and I wanted to randomly assign them to everyone. The problem is that if someone does have a custom avatar and then starts switching standard avatars after the random avatars are set, they will wipe out their custom avatar, so, you need a backup.

With that in mind, here's what I did:

In phpmyadmin:

1) Under OPERATIONS copy the customavatar table to customavatar070331
2) Under SQL:

SELECT userid,avatarid,avatarrevision FROM user

3) Use the export button on that result page.
3a) Select the SQL export (default)
3b) turn off (uncheck) Structure
3c) turn on (check) Data & for export type select UPDATE
3d) check zip (or some other compression you like)
3e) hit GO to download the file

OPEN the downloaded file and make sure its valid and shows the update statements that include all 3 fields.

To review, at this point, you've 1) backed up the customavatar table and 2) exported the avatarid/revision data from the user table by userid.

Now here's the fun part, where you randomize everyone's Avatars!

But first you'll test it on your avatar!!! In this case, I used userid 1997, put your test user's userid in place of that.

This statement let's you pick avatars by avatarid, where x is the lowest ID you want to use and z = (highest +1) - the lowest.

update user set `avatarid` = FLOOR( x + ( RAND( ) * z ) ) where userid = 1997;

To use avatars 1 to 100:

update user set `avatarid` = FLOOR( 1 + ( RAND( ) * 100 ) ) where userid = 1997;

To use avatars 819 to 1463:

update user set `avatarid` = FLOOR( 819 + ( RAND( ) *644 ) ) where userid = 1997;

After you check that it works right on your userid, just remove the 'where' clause at the end.

So, now your little prank is over, how do you put it back?

In phpmyadmin:
1) Select the user table, & use the INSERT function to upload the uncompressed SQL export. This will update all the avatar info to the way it was when you exported it.
2) Select the customavatar table:
2a) Under OPERATIONS rename it to customavatars070402
3) Select the customavatar070331 table:
3a) Under OPERATIONS rename it to customavatars

Done!