Solved

change date if date is in a different format to correct mysql format

Posted on 2014-02-13
4
510 Views
Last Modified: 2014-02-17
discard column fields with 'ago'

I think an idea was to create a new column 'modified_date' (varchar or datetime)

CREATE TABLE `a_messages2` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `conversation_id` bigint(20) default NULL,
  `profile_id` varchar(20) default NULL,
  `sender` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `message_text` varchar(1000) default NULL,
  `dateAgo` varchar(20) default NULL,
  `message_read` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9969 DEFAULT CHARSET=utf8;

Open in new window



http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28319753.html#a39756115

if date is a date and date is different that correct mysql time format then change to mysql time format

instead of coming from a file
date comes from the table column
0
Comment
Question by:rgb192
  • 2
  • 2
4 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Please read this article about handling DATETIME values.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

SQL table columns that would hold DATE or DATETIME values should be defined as DATETIME columns.

If you have dates in your SQL table and you've got them in VARCHAR columns, you can use ALTER TABLE to add a column of the DATETIME type with DEFAULT '0000-00-00 00:00:00'.  Then you can run a script to SELECT the VARCHAR column, use strtotime() and date('c') to convert the value to the ISO-8601 DATETIME string, and UPDATE the new column with the value.  If strtotime() returns FALSE, do not update the new column, but instead flag the row for manual intervention.
0
 

Author Comment

by:rgb192
Comment Utility
what is a way to implement this function if the values are already in the table
maybe save converted time to another column on the table

	/*
	this function converts date time string such as 1 minute ago, 3 hours ago, 9/17 4:13, just now to mysql time Created by Ray Passeur
	*/
	public function ConvertDateTime($date_time_str = 'now')
	{
		// remove unwanted characters
		$date_time_str = str_replace('(', NULL, $date_time_str);
		$date_time_str = str_replace(')', NULL, $date_time_str);

		// translate to US English date format like 'hour', 'now', etc.
		$date_time_str = str_replace('hr', 'hour', $date_time_str);
		$date_time_str = str_replace('just now', 'now', $date_time_str);

		$date_format = NULL;
		try
		{
			$datetime = date_create($date_time_str);
			$date_format = date_format($datetime, 'Y-m-d H:i:s');
		}
		catch (Exception $e)
		{
			$this->writeLog($e->getMessage(), TRUE);
		}

		return $date_format;
	}

Open in new window

0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
Right, that is what I was suggesting here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28364626.html#a39858578

Set up some test tables and experiment with it a little bit.  You will find the way, I promise!
0
 

Author Closing Comment

by:rgb192
Comment Utility
Thanks.
There is knowledge in the article.  I can not create a function with output in another column without spending hours in procedural code.  So I have a related question.

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28366944.html
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now