User Registration Date Problem - custom pickle

  Post new topicReply to topicPrintable Version
<< View previous topic View next topic >>
Share: Del.icio.us  Digg  Google  Spurl  Blink  Furl  Y! MyWeb  
#1   User Registration Date Problem - custom pickle
omitaybi
CZ Newbie
omitaybi has been a member for over 20 year's 20 Year Member
usa.gif massachusetts.gif
Occupation: Silly Girl
Age: 46
Gender: Female
Status: Offline
Joined: Jun 02, 2004
0.00 posts per day
Posts: 12
Points: 978
AIM Address   
There are several posts onm making the user registration date display correctly as a date and not a number. My issue is a bit different since I fuddled up my board. Here's what I did screwy.

When I brought over the phpbb user databse I did so doing it normally and everything worked fine. I soon found out that when new users registered it was set in the database like this:

May 24, 2004

and not the way PHPbb did it like this

1071334961

So I was unsure if I were to change them over I would have dates that started prior to my nuke installation. So I basically set ALL my members regdates to the current date (I was clueless).

So everything looks consistent EXCEPT all my users regdates are wrong.

What I would like to reall do is convert everything the way it should be. I basiaclly muddled my entire installation unrthadoxly (ie I played with the files too much so I don't have an exact idea as to what I have installed - I just know it works. Right now it seems I have 7.3 with 2.0.8.a)

Anyrate I saw this [ Register or login to view links on this board. ] and I wonder... (what does this do exactly and if it's something I should try...)

So my question is: If you are equipped with your old user table with regdates formatted as SUCh (1071334961) and you have a current nuke_users table with regdates like this (May 24, 2004) - and you have about 500 members with WRONG regdates where the correct ones are in your old phpbb_users sql table in this form (1071334961).... How would YOU go about (a) converting the phpbb_users field user_regdate from format (1071334961) correctly to format (May 24, 2004) OR covert all the nuke_users table field set as (May 24, 2004) to format (1071334961) then reimporting colum user_regdate to overwrite OLD members with the correct date - then making Nuke realize them...

Wheeeew that was complicated. icon_surprised.gif


Basically I just want my users to have their OLD regdates and have them be CORRECT and have everything display consistently with no difference between old users and new users.

This is something I very much would like to fix because Mods like the Karma Mad deal with Registration dat icon_sad.gif



Back to top Reply with quote
#2   
Telli
Site Admin
Occupation: Self Employed
Age: 46
Gender: Male
Fav. Sports Team: Detroit Red Wings
Website:
Status: Offline
Joined: May 26, 2003
1.03 posts per day
Posts: 8089
Points: 494,430
   
How many are in the proper format?




_________________
The path of the righteous man is beset on all sides by the inequities of the selfish and the tyranny of evil men. Blessed is he, who in the name of charity and good will, shepherds the weak through the valley of darkness, for he is truly his brother's keeper and the finder of lost children. And I will strike down upon thee with great vengeance and furious anger those who would attempt to poison and destroy my brothers. And you will know my name is the Lord when I lay my vengeance upon thee. Ezekiel 25:17
Back to top Reply with quote
#3   re: User Registration Date Problem - custom pickle
omitaybi
CZ Newbie
omitaybi has been a member for over 20 year's 20 Year Member
usa.gif massachusetts.gif
Occupation: Silly Girl
Age: 46
Gender: Female
Status: Offline
Joined: Jun 02, 2004
0.00 posts per day
Posts: 12
Points: 978
AIM Address   
I have 623 users with incorrect 'reset' user dates.
I have the OLD SQL in the same DB right now just sitting there as a backup.
Iit's called phpbb_users

I have 641 total members.
The table is called nuke_users

So I have 18 new members in nuke_users which are not in phpbb_users.

In addition I have a TON of user based mods so the table is HUGE.
I also hjad made some slight changes to the nuke_users table because some nuke processes use username not user_id.
Therefore items like user_id 2 in nuke_users is not user_id 2 in phpbb_users.

All the dates in nuke_users are like this: May 24, 2003

So nuke_users looks like this now

user_id ... user_regdate ...

1... May 24, 2004
2... May 24, 2004
3... May 24, 2004
4... May 24, 2004
5... May 24, 2004
6... May 24, 2004
7... May 24, 2004
.
.
.
639... June 6, 2004
640... June 7, 2004
641... June 8, 2004

The phpbb_users table looks like this (just example)

user_id ... user_regdate ...

1... 1071334961
2... 1071334961
3... 1080146988
4... 1080147594
5... 1080171894
6... 1080358717
7... 1080259786



Back to top Reply with quote
#4   
Telli
Site Admin
Occupation: Self Employed
Age: 46
Gender: Male
Fav. Sports Team: Detroit Red Wings
Website:
Status: Offline
Joined: May 26, 2003
1.03 posts per day
Posts: 8089
Points: 494,430
   
Your going to have to change each date manually. Or write a script to select the regdate then change the format then reinsert it.

Telli




_________________
The path of the righteous man is beset on all sides by the inequities of the selfish and the tyranny of evil men. Blessed is he, who in the name of charity and good will, shepherds the weak through the valley of darkness, for he is truly his brother's keeper and the finder of lost children. And I will strike down upon thee with great vengeance and furious anger those who would attempt to poison and destroy my brothers. And you will know my name is the Lord when I lay my vengeance upon thee. Ezekiel 25:17
Back to top Reply with quote
#5   re: User Registration Date Problem - custom pickle
omitaybi
CZ Newbie
omitaybi has been a member for over 20 year's 20 Year Member
usa.gif massachusetts.gif
Occupation: Silly Girl
Age: 46
Gender: Female
Status: Offline
Joined: Jun 02, 2004
0.00 posts per day
Posts: 12
Points: 978
AIM Address   
IS there anyway of knowing how to reformat the phpbb date string to the new style? How do I interpret that 108***** into May 24, 2008 etc etc what's the formula? I don't mind doing it by hand or doing it with SQL I just dont know how to interpret the string.



Back to top Reply with quote
#6   
Telli
Site Admin
Occupation: Self Employed
Age: 46
Gender: Male
Fav. Sports Team: Detroit Red Wings
Website:
Status: Offline
Joined: May 26, 2003
1.03 posts per day
Posts: 8089
Points: 494,430
   
MAKE A BACKUP!!

Save this is as fixdate.php place it in your phpbb root and run it once. Then check your databse date tables it will reformat it.

<?php
define('IN_PHPBB', true);
$phpbb_root_path = './';
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'common.'.$phpEx);

//
// Start session management
//
$userdata = session_pagestart($user_ip, PAGE_INDEX);
init_userprefs($userdata);
//
// End session management
//
include($phpbb_root_path . 'includes/page_header.'.$phpEx);

$sql = "SELECT * FROM " . USERS_TABLE . " ORDER BY user_id ASC limit 0,10000";
    $result = $db->sql_query($sql);
    while ($row = $db->sql_fetchrow($result)) {
    $row['user_regdate'] = date("M j, Y", $row['user_regdate']);
    $telli = $row['user_regdate'];
    $telli2 = $row['username'];

echo "$telli $telli2<br/>";
$db->sql_query("UPDATE " . USERS_TABLE . " SET user_regdate='$telli' WHERE username='$telli2'") or mysql_error();
}
include($phpbb_root_path . 'includes/page_tail.'.$phpEx);
?>


the format used to decipher the date is:

date("M j, Y", 1080259786);




_________________
The path of the righteous man is beset on all sides by the inequities of the selfish and the tyranny of evil men. Blessed is he, who in the name of charity and good will, shepherds the weak through the valley of darkness, for he is truly his brother's keeper and the finder of lost children. And I will strike down upon thee with great vengeance and furious anger those who would attempt to poison and destroy my brothers. And you will know my name is the Lord when I lay my vengeance upon thee. Ezekiel 25:17
Back to top Reply with quote
#7   re: User Registration Date Problem - custom pickle
omitaybi
CZ Newbie
omitaybi has been a member for over 20 year's 20 Year Member
usa.gif massachusetts.gif
Occupation: Silly Girl
Age: 46
Gender: Female
Status: Offline
Joined: Jun 02, 2004
0.00 posts per day
Posts: 12
Points: 978
AIM Address   
That worked supremely well. I messed up the first time by not making the regdate on the phpbb_users a VARCHAR(20) but then after I tried it again it set the table correct. I just deleted user 1 & 2 then used a UPDATE nuke_users2, nuke_users SET nuke_users.user_regdate = nuke_users2.user_regdate WHERE nuke_users.user_id = nuke_users2.user_id; to move it over and it went LULLY!

Much Thanks! icon_razz.gif
Let me know if you ever need anything my email is in my profile!
1 problem down only 3 to go lol.



Back to top Reply with quote
Display posts from previous:      
Add To: Del.icio.us  Digg  Google  Spurl  Blink  Furl  Y! MyWeb  
<< View previous topic View next topic >>
Post new topicReply to topic

Jump to 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum