Blog Documentation Try Vanilla Cloud
Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
Options

Help: How to reset all users avatar into default or nothing.

maxyaegermaxyaeger New
in Vanilla 2.0 - 2.8

Thanks guys for this great software, im loving it. I came from mybb and so far everything is working smooth after the import, except for the avatars. But that's no big deal, they can just upload it again.

What i want to do is what mysql query should i run to reset all users avatar into the defaul one or use nothing, this way i can avoid broken links as i do have more than 1k members.

Thanks

P.S - I've tried this but no luck
UPDATE GDN_User SET Photo = CONCAT('http://www.mysite.com/forum/uploads/',Photo) WHERE Photo LIKE '%avatar%';

P.P.S - I just want a query that will remove all users avatar and turn into default.

Comments

  • Options
    R_JR_J Ex-Fanboy Munich Admin

    You were very close: the Photo column is NULL by default.
    UPDATE GDN_User SET Photo = NULL no WHERE needed.

    I guess you were trying to convert the old avatars and that's what I would still try if I were you. Could you give an example of an entry in the User table an the corresponding path in your uploads folder? It should be possible to update the Photo column accordingly

  • Options
    maxyaegermaxyaeger New

    Hello R_J thanks for your response.

    Here - http://mysite.com/forum/uploads/./uploads/avatars/avatar_2.jpg?dateline=1300352777

    I dont understand the part /./ (dot)

    The avatars are located here (http://mysite.com/forum/uploads/avatar/ i copied the avatar folder directly to the vanila upload folder.

    Note i also tried uploading a new avatar but it created a new folder called, userpics. Should i move the files (photos) from the avatar folder inside the userpics folder?

  • Options
    R_JR_J Ex-Fanboy Munich Admin 
    UPDATE GDN_User
   SET Photo = REPLACE(
                       Photo,
                       'http://mysite.com/forum/uploads/./uploads/avatars',
                       'avatar'
               )
 WHERE Photo LIKE ('http://mysite.com/forum/uploads/./uploads/avatars%');

    This would work if you have the avatars like that in the db:
    http://mysite.com/forum/uploads/./uploads/avatars/something.jpg

    and you have them like that in your forum
    /uploads/avatar/something.jpg

    It doesn't matter for your SQL why there is the uploads/./uploads. If you have converted it from another forum script, we should call for Linc because he might be interested in that.

    You could always export a table like that, do the conversion with a text editor and import it. I would think of doing it that way for eliminating what is there behind the question mark.

    Concerning the name of the folder in the uploads folder: Vanilla will choose its own naming conventions for new avatars, anyway. I guess it is cleaner if you keep your "old" avatars in a separate folder.

  • Options
    maxyaegermaxyaeger New
    edited March 2016

    Hello, it almost worked.

    The link are now converted into:

    avatar/avatar_4.jpg?dateline=1318001762

    how do we get rid of ?dateline=xxxxxxx

    thanks!

  • Options
    hgtonighthgtonight ∞ · New Moderator

    Are you using MariaDB? If so, use the REGEXP_REPLACE syntax.

    Using MySQL? Use the following UDF: https://github.com/hholzgra/mysql-udf-regexp

    Don't want to deal with this? Leave it as is since anything after a ? in a URL will be parsed as arguments and not affect the loading of the image.

    Search first

    Check out the Documentation! We are always looking for new content and pull requests.

    Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.

  • Options
    R_JR_J Ex-Fanboy Munich Admin

    @R_J said:
    You could always export a table like that, do the conversion with a text editor and import it. I would think of doing it that way for eliminating what is there behind the question mark.

  • Options
    noncenonce Necro-on Forensics ✭
    edited March 2016

    above is a good option. another option might be

    UPDATE GDN_User
    SET Photo = SUBSTRING(Photo, 1, LENGTH(Photo)-20)
    WHERE SUBSTRING(Photo, LENGTH(Photo)-20), 2) = "?d";

    untested may need to change numbers but the truncation might give you some idea.

  • Options
    noncenonce Necro-on Forensics ✭
    edited March 2016

    Howdy Max, @maxyaeger

    you probably solved your problem already.

    you said:

    avatar/avatar_4.jpg?dateline=1318001762

    how do we get rid of ?dateline=xxxxxxx

    if the ?dateline always has the same number of digits (10 in your first example, but 7 in your second example)

    I made an error in the above comment looking at it again.

    try this if all ?dateline rows have a 10 digit random number

    UPDATE GDN_User
    SET Photo = SUBSTRING(Photo, 1, LENGTH(Photo)-20)
    WHERE like %dateline%;

    or if there were different number of digits following the dateline=

    if 10 digits

    UPDATE GDN_User
    SET Photo = SUBSTRING(Photo, 1, LENGTH(Photo)-20)
    WHERE SUBSTRING(Photo, -20), 9) = "?dateline";

    if 9 digits

    UPDATE GDN_User
    SET Photo = SUBSTRING(Photo, 1, LENGTH(Photo)-19)
    WHERE SUBSTRING(Photo, -19), 9) = "?dateline";

    if 8 digits

    UPDATE GDN_User
    SET Photo = SUBSTRING(Photo, 1, LENGTH(Photo)-18)
    WHERE SUBSTRING(Photo, -18), 9) = "?dateline";

  • Options
    maxyaegermaxyaeger New

    Thank you very much sir, i will try this again.

  • Options
    noncenonce Necro-on Forensics ✭

    @maxyeager

    upon re-reading my last comment I note that I seem to have added too many ) right hand parentheses in the where statements.

    notice the extra paren which is wrong WHERE SUBSTRING(Photo, -20), 9) = "?dateline";

    you will need to correct the syntax in this and related where statements and remove the extra parentheses

    WHERE SUBSTRING(Photo, -20, 9) = "?dateline";

    UPDATE GDN_User
    SET Photo = SUBSTRING(Photo, 1, LENGTH(Photo)-20)
    WHERE SUBSTRING(Photo, -20, 9) = "?dateline";

    parenthetical details must be heeded.

  • Options
    maxyaegermaxyaeger New
    edited March 2016

    Thank you very much the above query is working!

    There's another problem, sorry i didnt notice this before, the link is still broken because it's looking for navatar wherein the avatars are named avatar_xx

    I checked the sql table and under photo it looks ok. ./uploads/avatars/avatar_2.jpg

    But when i visit the forum the images for avatar are still broken, i looked it up on firebug and the link it's looking for is...

    forum/uploads/avatar/navatar_3784.jpg" alt="Max" class="ProfilePhoto ProfilePhotoSmall">

    Is there a way to remove "n" via sql?

    Note i already tried reuploading my files i double checked and the import tool from mybb > to vanila does this, it adds n to avatar.

  • Options
    noncenonce Necro-on Forensics ✭
    edited March 2016

    good to know that a simple truncation solved the problem, it didn't look like a regex problem.
    multiple ways to solve a problem. some better than others.
    the better way to fix your problem is not to change the code, but to do a mass rename or copy of the jpgs in the uploads/avatar folder and prepend an n to the avatar_ to the files.

    you can google how to mass rename files or mass copy files. depending on the tool you use for your os.

    here are about 15 ways to accomplish this in linux. all very simple.

    http://www.cyberciti.biz/tips/renaming-multiple-files-at-a-shell-prompt.html

    similar ways with other os's

  • Options
    maxyaegermaxyaeger New

    gotcha thanks again. solved

  • Options
    maxyaegermaxyaeger New

    Hello guys, kinda late but i just noticed that my profile avatars are broken, they're working fine on the discussion and comments page but broken on the profile page.

    Reason for this is, the profile page is requesting for pavatar.jpg while the on the discussion and comments its navatar.jpg

    Is there any way just to reset the avatars into default or none , i think my members dont mind re-uploading their avatars.

  • Options
    vrijvlindervrijvlinder Papillon-Sauvage MVP

    Please try using the plugin DefaultAvatar

    ❌ ✊ ♥. ¸. ••. ¸♥¸. ••. ¸♥ ✊ ❌

  • Options
    maxyaegermaxyaeger New

    Using the default avatar will it force users to have a different avatar? Is there any mysql code i can run to change their avatar into none this way the site looks more better without the broken avatar links.

Sign In or Register to comment.