MyBB Community Forums

Full Version: [F] Birthdays on calendar
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I recently merged from phpbb3 to mybb 1.4.7 using the beta 5 merge system. Pretty much everything worked correctly, but I finally hit an, admittedly minor, issue. Birthdays were successfully imported from phpbb3. I can browse the DB and see them in the mybb_users table. However, they do not show up on the calendar as expected. Granted, I thought that rebuilding the birthday cache would do it, but it doesn't seem to do anything. The only way I've found to get them to show up is for a member to manually update their birthday via their profile...even if it is already correct. This generates the calendar entry.

I would be fine with doing the necessary mass inserts/updates in the DB for my members, but I haven't been able to determine just where the calendar is looking for birthdays. If someone could explain that, I'd be very appreciative.
Can you give me an example of a birthday that doesn't work in the DB and one that does?

Thanks
Ryan
Actually, I think I just figured it out. It seems as though mybb doesn't like numbers padded with 0 (ex. 23-01-1972). MyBB want that date to be 23-1-1972 and when it is updated that way in the DB, they show up on the calendar. The merge system imported them that way, but it's easy enough for me to fix myself. Thanks! If I ha not looked at the Birthday field again I would have never noticed.
Ah - I'll get that fixed in the merge system. Does it apply to days as well?
Yep, days and months. I was able to fix it by running several queries such as:
Update `mybb_users`
set `birthday` = REPLACE(birthday, '01-', '1-');
Update `mybb_users`
set `birthday` = REPLACE(birthday, '02-', '2-');

etc...
that would catch both the day and month portions. I'm used to Oracle and Teradata so I was expecting to be able to use a regular expression to fix them all in on fell swoop, but I soon found out that it seems MySQL doesn't support that...or at least I never got the syntax right.

On a side note, I knew my members weren't going to like losing their avatars, so I devised a few queries to do all the work for me. I copied the contents of the phpbb avatar directory over to the correct location for my mybb install. Then I ran these queries on my phpbb3 db:
//uploaded avatars
SELECT Concat( 'Update mybb_users set avatar = \'./uploads/avatars/abb8d198a5beccd4dc3361807e66127a_', user_avatar, '\'\, avatartype = \'upload\'\, avatardimensions=\'',user_avatar_width,'\|',user_avatar_height, '\'  where username = \'',REPLACE(username,'\'','\\\''),'\'\;')
from phpbb_users
where user_avatar_type = 1


//Remote Avatars
SELECT Concat( 'Update mybb_users set avatar = \'', user_avatar, '\'\, avatartype = \'remote\'\, avatardimensions=\'',user_avatar_width,'\|',user_avatar_height, '\'  where username = \'',REPLACE(username,'\'','\\\''),'\'\;')
from phpbb_users
where user_avatar_type = 2

Those queries actually created all of the update statements needed to update the mybb database with the users' avatar info. One last thing needed was to remove the weird salt that phpbb3 put into some of the avatar filenames. I never have figured out why this only happened to some of the avatars. Anyway, I ran this query:
//UPDATE mybb avatars that had weird extra SALT concatenated into filename
Update mybb_users
set avatar =
CONCAT(substring(avatar,1, instr(avatar,"abb8d198a5beccd4dc3361807e66127a")-1),'abb8d198a5beccd4dc3361807e66127a_',
substring(avatar, instr(avatar, "_")+1,(instr(avatar, substring_index(avatar, "_", -1)) - 1)- (instr(avatar, "_")+1)),
substring(avatar, instr(avatar, substring_index(avatar, ".", -1))-1,length(avatar)))
where avatar RLIKE'[[:alnum:]]+_[[:alnum:]]{1,5}_[[:alnum:]]+[[=period=]][[:alnum:]]{3,4}';
to remove the salt from the filenames in the mybb db. The salt is board specific I believe, but you can find it by looking at some of the avatar names on the phpbb side.

I post all of this not because I think it needs to be in the merge system. On the contrary, I believe it would be difficult to do this on a global level. But, maybe somebody will see this and find it helpful.
This bug has been fixed in the latest version of the MyBB Merge System.

Please note the latest code is not live on the site or for download. An update for the MyBB Merge System will be released which contains this fix.

With regards,
The MyBB Group