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

maxyaeger

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.

R_J

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

maxyaeger

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?

R_J

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.

maxyaeger

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!

hgtonight

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.

R_J

@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.

nonce

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.

nonce

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";

maxyaeger

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

nonce

@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.

maxyaeger

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.

nonce

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

maxyaeger

gotcha thanks again. solved

maxyaeger

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.

vrijvlinder

Please try using the plugin DefaultAvatar

maxyaeger

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.