20i Reseller Hosting

php/mysql bulk convert (1) date and (2) md5 passwords

Discussion in 'Scripts and Coding' started by woopwoop, Aug 4, 2012.

Thread Status:
Not open for further replies.
  1. woopwoop United States

    woopwoop Well-Known Member

    Joined:
    Jan 2007
    Posts:
    2,220
    Likes Received:
    37
    I've searched and think I'm getting closer but can't find answers which work.

    I have two problems to solve. Can anyone help?

    (1) I'm trying to convert a bunch of dates in a mysql db from varchar(20) eg. stored as "Aug 28, 2006" into int(10) format e.g. "1156741200" but can't figure out the sql query to run.

    (2) I have a table with user info in it. Passwords are stored there encrypted md5. I need to change everyone's password. I could generate a list of plain text random passwords and put them in a column next to the user id's. I'm not sure of how to get the md5 version of the random password that I give them. Also I'm guessing the sql query would be:

    Code:
    UPDATE table_name SET password=md5value WHERE user_id=some_value
    Any help really appreciated... my headache is now full blown! :confused:
     
  2. Domain Forum

    Acorn Domains Elite Member

    Joined:
    1999
    Messages:
    Many
    Likes Received:
    Lots
    articles.co.uk
     
  3. woopwoop United States

    woopwoop Well-Known Member

    Joined:
    Jan 2007
    Posts:
    2,220
    Likes Received:
    37
    I fixed my second problem by mail merging some data and then copying and pasting 20,000 lines like:
    Code:
    UPDATE members SET password = sha1("newrawpassword") WHERE id_member = "some_number"; 
    But still not sur about #1, the date issue :(
     
  4. alex

    alex Active Member

    Joined:
    Sep 2007
    Posts:
    839
    Likes Received:
    33
    Apologies if this isn't quite what you're after, but regarding the time issue have you looked at the strtotime() function in php?

    strtotime("Aug 28, 2006"); outputs 1156716000. Is this what you're after? If so you could loop through the rows and replace.
     
    Last edited: Aug 4, 2012
  5. retired_member36

    retired_member36 Retired Member

    Joined:
    Jun 2011
    Posts:
    897
    Likes Received:
    17
  6. alex

    alex Active Member

    Joined:
    Sep 2007
    Posts:
    839
    Likes Received:
    33
    EDIT: Nothing to see here
     
    Last edited: Aug 4, 2012
  7. woopwoop United States

    woopwoop Well-Known Member

    Joined:
    Jan 2007
    Posts:
    2,220
    Likes Received:
    37
    Thanks Suggys - I'll take a look.

    @Alex It's a bit tricky because the date is saved with format varchar(20) and in the format "mmm dd, yyyy" eg. Aug 28, 2006.

    not sure if that would still work but I can give it a try:
    strtotime("Aug 28, 2006") would output 1128290400.

    Thanks for reply.

    Edit Missed your other reply Alex, I think that it should output "1128290400" but will give it a try. Thanks again.
     
    Last edited: Aug 4, 2012
  8. alex

    alex Active Member

    Joined:
    Sep 2007
    Posts:
    839
    Likes Received:
    33
    We appear to have different outputs :confused:
     
  9. woopwoop United States

    woopwoop Well-Known Member

    Joined:
    Jan 2007
    Posts:
    2,220
    Likes Received:
    37
    I'm trying to compare the two databases and it shows that

    Aug 28, 2006 = 1156741200

    So I was wrong with my other number and this is very close to the number you got (1156716000)


    Edit I just thought that maybe one of the DBs is also looking at time of day and this could give the discrepancy. For my purpose I don't care about time, so your method will work great.

    Thanks for the help. I will try it when I wake up (gotta sleep it's 5am and I've been up all night!)
     
  10. alex

    alex Active Member

    Joined:
    Sep 2007
    Posts:
    839
    Likes Received:
    33
    I was wondering if that was causing it. Hopefully it will work, but give me a shout if it doesn't and I'll be happy to ponder it over with you. Until then hope you catch up on sleep!
     
Thread Status:
Not open for further replies.