[RESOLVED] converting a db entry from a text date to unixTS

schwim

Hi there everyone,

I've got a community site that moved to another CMS last year. Although it didn't seem a big deal to me at the time, it seems that members take their registration date very seriously. I hear about it quite often, and it's kind of a pain in the keester.

So, I looked at my DB backup before making the switch, and pared it down to user_id and reg date. What I need to do now is turn:

Sep 18, 1998

into

906076800

Here's what I have to work with:

INSERT INTO users (user_id, user_regdate) VALUES ('2','Sep 18, 1998');

I've got about 3,000 entries that this needs to be done for, and I just need the script to generate the sql data, and I'll input it myself after making a db backup.

I would appreciate any help you might be able to provide, as I'm not quite sure where to start with this endeavor.

thanks,
json

NogDog

$date = 'Sep 18, 1998';
$unixDate = strtotime($date);
$query = "INSERT INTO `users` (`user_id`, `user_regdate`) VALUES ('2', '$unixDate')";

schwim

I should note that I found the following on the web, but it's the exact reverse of what I need to do:

<?php
$timestamp=1075995075;
$thedate = date("M j Y", $timestamp);
print  $thedate;
?>

Is there a way to simply reverse this action?

thanks,
json

[EDIT]Thanks nogdog, I'll start working on it posthaste and let you know of my progress!

thanks,
json
[/EDIT]

schwim

Hi there,

Just posting to thank you nogdog, as it worked a treat. I really appreciate the help.

Also, I wanted to post the script for anyone who might find themselves in the same pickle:

<?php

$autoconn = mysql_connect(localhost,db_user,db_pass) or die ("DBUser or DBPass was refused when attempting to connect to MySQL Host.");
$dbi=mysql_select_db(database,$autoconn) or die("Unable to make master connection to database: " . mysql_error());


$query = "SELECT user_id, user_regdate FROM users";

$result = mysql_query($query) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query);

while ($row = mysql_fetch_assoc ($result)) {

	$user_id = $row[user_id];
	$user_regdate = $row[user_regdate];

	$date = $user_regdate;
	$unixDate = strtotime($date);

	echo("UPDATE users SET user_regdate = ".$unixDate." WHERE user_id = ".$user_id.";<br>");

}

?>

thanks,
json