RSS

[php][tip] How to map date field across different tables when one is of date format and the other of unixtimestamp?

10 Dec

While merging record of multiple tables into a single table, I need to merge the date field as well and the issue with merging date field is that the field in which I have to merge it have unixtimestamp and the source filed have the date format yyyy-mm-dd hh:ii:ss.

After searching alot on any mysql function that can convert date format, found nothing. Rather find strtotime php function to meet the purpose. Thus tried a bit lengthy way to merge two tables by first merging all the required data except date field and merge the date field afterwards by using following update query.

$query = "SELECT * FROM `src_table` ;
$result = mysql_query($query);
$dts = array();
while($row = mysql_fetch_array($result))
{
   $id = $row[‘id’];
   $dts[$id] = strtotime($row['date']);
}
foreach($dts as $id => $date)
{
   $query = "UPDATE dest_table set date = '".$date."'".
   " WHERE id = '".$id."'";
   mysql_query($query);
}

That’s it 🙂

Advertisements
 
Leave a comment

Posted by on December 10, 2011 in php

 

Tags: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: