If user's regdate is 0 on myBB

[ewe]

I've been dealing with this issue for a while and am quite pleased to have fixed it last night.

First, in functions_forums.php around line 21 where other variables are being defined add the line: I'm not sure why Insert didn't like just time() but making it a variable fixed my problem.

$time = time();

Then find this line(around 22):

    $query = "INSERT INTO {$mybbprefix}users (uid, username, password, salt, loginkey, email, postnum, avatar, avatardimensions, avatartype, usergroup, additionalgroups, displaygroup, usertitle, regdate, lastactive, lastvisit, lastpost, website, icq, aim, yahoo, msn, birthday, birthdayprivacy, signature, allownotices, hideemail, subscriptionmethod, invisible, receivepms, receivefrombuddy, pmnotice, pmnotify, threadmode, showsigs, showavatars, showquickreply, showredirect, ppp, tpp, daysprune, dateformat, timeformat, timezone, dst, dstcorrection, buddylist, ignorelist, style, away, awaydate, returndate, awayreason, pmfolders, notepad, referrer, referrals, reputation, regip, lastip, longregip, longlastip, language, timeonline, showcodebuttons, totalpms, unreadpms, warningpoints, moderateposts, moderationtime, suspendposting, suspensiontime, suspendsignature, suspendsigtime, coppauser, classicpostbit, loginattempts, usernotes)
                                       VALUES ('', '$username', '$fpass','$salty','$loginkey', '$email', '0', '$avatar', '', '0', '2', '', '0', '', 'time()', 'time()', 'time()', '0', '', '', '', '', '', '$birthday', 'all', '', '1', '0', '0', '0', '1', '0', '1', '1', '', '1', '1', '1', '1', '0', '0', '0', '', '', '0', '0', '0', '', '', '0', '0', '0', '', '', '', '', '0','0','0','$ip', '$ip','$altip','$altip','','0','1', '0', '0', '0','0','0','0','0','0','0','0','0','1','')";

Change 'time()' to $time the first value matters the most as it doesn't get updated because it's your registration date

    $query = "INSERT INTO {$mybbprefix}users (uid, username, password, salt, loginkey, email, postnum, avatar, avatardimensions, avatartype, usergroup, additionalgroups, displaygroup, usertitle, regdate, lastactive, lastvisit, lastpost, website, icq, aim, yahoo, msn, birthday, birthdayprivacy, signature, allownotices, hideemail, subscriptionmethod, invisible, receivepms, receivefrombuddy, pmnotice, pmnotify, threadmode, showsigs, showavatars, showquickreply, showredirect, ppp, tpp, daysprune, dateformat, timeformat, timezone, dst, dstcorrection, buddylist, ignorelist, style, away, awaydate, returndate, awayreason, pmfolders, notepad, referrer, referrals, reputation, regip, lastip, longregip, longlastip, language, timeonline, showcodebuttons, totalpms, unreadpms, warningpoints, moderateposts, moderationtime, suspendposting, suspensiontime, suspendsignature, suspendsigtime, coppauser, classicpostbit, loginattempts, usernotes)
                                       VALUES ('', '$username', '$fpass','$salty','$loginkey', '$email', '0', '$avatar', '', '0', '2', '', '0', '', '$time', '$time', '$time', '0', '', '', '', '', '', '$birthday', 'all', '', '1', '0', '0', '0', '1', '0', '1', '1', '', '1', '1', '1', '1', '0', '0', '0', '', '', '0', '0', '0', '', '', '0', '0', '0', '', '', '', '', '0','0','0','$ip', '$ip','$altip','$altip','','0','1', '0', '0', '0','0','0','0','0','0','0','0','0','1','')";

Now to use SQL to fix the users who have already registered:
You'll need to replace 'yoursite' with the names of your databases.

UPDATE yoursite_mybbforum.mybb_users AS myBB
JOIN yoursite_adopts.adopts_users AS mys 
SET myBB.regdate = UNIX_TIMESTAMP(CONCAT(mys.membersince,' 00:00:00')) 
WHERE myBB.regdate = 0 AND myBB.uid=mys.uid

If you would prefer to test this a few times before running it on every user (I know I did) You can convert the crazy string of numbers back into a time that humans can understand using this site.

UPDATE yoursite_mybbforum.mybb_users AS myBB
JOIN yoursite_adopts.adopts_users AS mys 
SET myBB.regdate = UNIX_TIMESTAMP(CONCAT(mys.membersince,' 00:00:00')) 
WHERE myBB.regdate = 0 AND myBB.uid=mys.uid AND mys.username = 'testaccount'

For those who are curious, an explination

  Spoiler: Caution: Wall of Text 

Mysdia saves a user's date joined as a string that looks like Y-m-d or 2017-03-24. MyBB has regdate set as a Unix Timestamp which is the number of seconds from January 1, 1970. MySQL can convert string times that look like 'YYYY-MM-DD HH:MM:SS' to a Timestamp but the data in the users table doesn't include hours, minuets and seconds. Once again MySQL is handy and we can concatenate - that means stick together - membersince from the Users table and a string of ' 00:00:00'. This means that according to the database, everyone registered at midnight. That's okay because it's really just the date that matters in this instance and we can't really do better, anyway.

 

[kristhasirah]

Thanks for sharing with us the fix ^^