[SOLVED] converting dates between databases

[ukscotth]

Hi,

 

Im in the process of changing the software my social networking site runs off and im currently trying to pull the data from the old database and load it into the new one. 

 

The part im stuck with is the dates as they are stored differently in both databases. Heres the old and the new structures :

 

OLD

 

field : account_created

type: varchar

example : 24th Dec 2009 at 13.19

 

NEW

 

field : joindate

type : int(10)

example : 1246980713

 

Id like to pull the dates from the old database, convert them and then load them into the new one using SELECT etc. its just the conversion part im stuck with

 

I also have a similar problem with the d.o.b fields :

 

OLD

 

field : birth_day

type : (int11)

example : 24

 

field : birth_month

type : (int11)

example : 6

 

field : birth_year

type : (int11)

example : 1980

 

NEW

 

field : birthday

type : char(10)

example : 05-21-1978

 

Hope that makes and I hope someone can help.

 

Thanks very much.

 

Scott

[phporcaffeine]

Have a look at: http://us2.php.net/mktime ... should be what you need for the date conversion from readable to timestamp.

 

Now the DOB will probably have to be done by using a LEFT JOIN to select the values than concatenate them into one string and insert them.

[Mchl]

Also strtotime for textual date from the first example.

[ukscotth]

Thanks alot for your quick replies, had a look at both of those but im a bit of a newbie and it looks a bit complicated and chance of an example for my situation plzzzz ?

[.josh]

do you know how to do basic database interaction with php? Like, pulling data from the old table?

[ukscotth]

do you know how to do basic database interaction with php? Like, pulling data from the old table?

 

yep but thats about all i know, this is what ive been doing :

 

 

<?php 
include("include/configvb.inc.php");

$users = mysql_query("SELECT * FROM joovili_users");
						  

			while ($got_users = mysql_fetch_array($users)){

mysql_query("INSERT INTO user (`userid`, `password`, `username`, `email`) 
VALUES ('".$got_users['id']."', '".$got_users['password']."', 
'".$got_users['username']."','".$got_users['email']."')");

}

[phporcaffeine]

$dateValueFromOldDatabase = '24th Dec 2009 at 13.19';

 

$dateAndTime = explode(' at ', $dateValueFromOldDatabase);

 

$newDatabaseDateFormat = mktime(15, 19, 0, 12, 24, 2009);

[ukscotth]

I know this isnt a freelance forum but I dont suppose any of you would like to help me out with the whole migration if i paid ? im starting to think its all gonna be a bit to hard for me

[ukscotth]

thnx mate, il give that a go

[.josh]

$dateValueFromOldDatabase = '24th Dec 2009 at 13.19';

 

$dateAndTime = explode(' at ', $dateValueFromOldDatabase);

 

$newDatabaseDateFormat = mktime(15, 19, 0, 12, 24, 2009);

 

If you remove the 'at' strtotime will convert it, so you can do

 

$string = "24th Dec 2009 at 13.19";
$string = str_replace('at','',$string);
$timestamp =  strtotime($string);

[phporcaffeine]

If you're unable or not inclined however, I do private consulting. You can reach me through this forum or at http://rthconsultants.com

[.josh]

As far as your Birth fields.  Separated by year, month day in old, but a single string in new; Just select them from the old, and concat them together for the new

 

$newBDay = $birth_month . '-' . $birth_day . '-' . $birth_year;

[phporcaffeine]

CV,

 

I think the 'at' is part of his database value which is why I went the way I did, so you could explode it prior to fleshing out the timestamp.

[ukscotth]

Thanks peeps, so for the first part id do something like this ??

 

<?php 
include("include/configvb.inc.php");

$users = mysql_query("SELECT * FROM joovili_users");
						  

			while ($got_users = mysql_fetch_array($users)){


$string = $got_users['account_created'];
$string = str_replace('at','',$string);
$timestamp =  strtotime($string);


mysql_query("INSERT INTO user (`userid`, `password`, `username`, `email`, `joindate`) 
VALUES ('".$got_users['id']."', '".$got_users['password']."', 
'".$got_users['username']."','".$got_users['email']."','".$timestamp."')");

}
?>

[phporcaffeine]

You would appear to have it with that.

 

[DEBUG]

I would add:

 

OR die(mysql_error()); on the end of your query statement if it doesn't work.

[.josh]

CV,

 

I think the 'at' is part of his database value which is why I went the way I did, so you could explode it prior to fleshing out the timestamp.

 

Right. But when you explode at the "at" you are left with a 2 element array. One with '24th Dec 2009' and one with '13.19' well you turn around and use mktime with those individual number separated (and even converted, like Dec->12) well you sort of skipped the steps involved in having to go from your explode to your mktime... with what I posted, I start out with the string from the old table just the same.  I then remove the 'at' and strtotime it, which does all that stuff you skipped in your code, plus what mktime does.

 

[ukscotth]

ok cool thnx, what does the die part do, i keep seeing it on coding ? sorry for being stoopid, lol

 

i sent you a pm by the way

[phporcaffeine]

Ha ... I sure did!

[.josh]

OR die(mysql_error());

 

will spit out the error mysql generates if there is a problem with executing the query.  Use it for debugging.  Remove it when done.  Although this sounds like a one-time-use script anyways, so whatever.

[ukscotth]

OR die(mysql_error());

 

will spit out the error mysql generates if there is a problem with executing the query.  Use it for debugging.  Remove it when done.  Although this sounds like a one-time-use script anyways, so whatever.

 

damn i wish i knew that before lol, cheers

[ukscotth]

I just wanted to say thanks for your help with this, tested it now and it works perfect.

 

A mod can mark this as solved now