[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."'";

That’s it 🙂

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: Logo

You are commenting using your 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: