Solved

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

Posted on 2014-02-13
4
515 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39858578
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
ID: 39860716
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 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39860727
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
ID: 39865486
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

730 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