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 🙂