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

[woopwoop]

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:

UPDATE table_name SET password=md5value WHERE user_id=some_value

Any help really appreciated... my headache is now full blown!

 

[woopwoop]

I fixed my second problem by mail merging some data and then copying and pasting 20,000 lines like:

UPDATE members SET password = sha1("newrawpassword") WHERE id_member = "some_number";

But still not sur about #1, the date issue

 

[alex]

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.

 

[retired_member36]

Hi woop

If you dont find the answer your looking for here, try

http://forums.devshed.com/php-development-5/

Ive posted some things on there my self that i couldnt solve and the guys are very helpful.

 

[alex]

EDIT: Nothing to see here

 

[woopwoop]

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.

 

[alex]

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

We appear to have different outputs

 

[woopwoop]

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!)

 

[alex]

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!)

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!