Jump to content

Parsing a Date-time String to the Datetime Datatype


Jesdisciple

Recommended Posts

I have a strictly uniform (edited by script) date field and a user-entered time field. (For example of the strict date format, today is 11/12/2007.) I want to store both of them in a datetime column in MySQL, via PHP. I'm guessing the solution is a (My)SQL function, but http://dev.mysql.com/doc/refman/5.0/en/dat...-functions.html doesn't make that much sense to me. So, does anyone know what would serve this purpose best? (I could enforce a strict time format as well by alerting onblur of the text field, but I hope that isn't necessary.) Thanks.EDIT: Is http://dev.mysql.com/doc/refman/5.0/en/dat...ction_timestamp the right one?

Link to comment
Share on other sites

MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format
Why do you need to use a function? Just make sure the data you're trying to enter is in that format. If the time is being entered by the user then you'll need to validate it, and you'll also need to rearrange the date parts to be in the given format.
<?php$chunks = explode($date, "/");$datepart = $chunks[2] . "-" . $chunks[0] . "-" . $chunks[1];?>

Similar for the time. For the time it will be easiest to have 1 text field for hours, 1 for minutes, and another for seconds if necessary. You can either tell people to use 24-hour time or have a dropdown for am/pm. MySQL needs it in 24-hour time.

Link to comment
Share on other sites

As you've probably already inferred, this is a different aspect of the same project. I've added this line to Message.earlier():

		document.forms.message.time.onblur = function(){			if(!this.value.match(/^(\d{1,2}):(\d{2})( [ap]m)?$/i)){				alert('The time should be in the format "g:i a" (e.g., 12:03 am, 1:00 pm) or "G:i" (e.g., 0:03, 13:00).\nSee http://www.php.net/date.');				setTimeout('document.forms.message.time.focus()', 2000);			}		}

The call to setTimeout() is in there because the direct call to focus() wasn't doing it. Can you think of a better way? (This has the very annoying side-effect of not allowing the user to leave the window/tab until the field validates.)(In updating the online version, I have made main() undefined online but not at home.)

Link to comment
Share on other sites

[...] The call to setTimeout() is in there because the direct call to focus() wasn't doing it. Can you think of a better way? (This has the very annoying side-effect of not allowing the user to leave the window/tab until the field validates.) [...]
Using onblur for validation is always likely to produce this kind of annoying behaviour. It can outweigh any intended benefit such as "helpfully" setting focus to the errant field. I'd avoid it altogether myself--just do all validation on the form's onsubmit event handler:
<html><body><form onSubmit="return onformsubmit()"><input type="text"><input type="submit" value="Submit"></form><script type="text/javascript">function onformsubmit(){// do validation checks here// return true if all valid, otherwise falsereturn false;}</script></body></html>

Link to comment
Share on other sites

  • 2 weeks later...

OK, here's my submission PHP:

<html>	<head>		<title>Titled Document</title>	</head>	<body>		<pre><?php$numberCount = $_POST['numberCount'];$caller = $_POST['caller'];$recipient = $_POST['recipient'];$contents = $_POST['contents'];for($i0 = 0; $i0 < $numberCount; $i0++){	$numbers[$i0] = $_POST['number' . $i0];}$numbers = implode(', ', $numbers);$earlier = $_POST['earlier'];if($earlier == 'true'){	$time = strtolower(preg_match('/^(\d{1,2})\d{2})( [AaPp][Mm])?$/', $_POST['time'], $groups));	if($time){		if(isset($groups[3])){//12-hour clock			if($groups[1] > 12 || $groups[1] < 1){//invalid time							}else{				switch($groups[3]){					case ' am':						if($groups[1] == 12){							$groups[1] = '0';						}						break;					case ' pm':												break;				}			}		}//24-hour clock		if($groups[1] < 10){			$groups[1] = '0' . $groups[1];		}		$time = $groups[1] . ':' . $groups[2] . ':00';	}else{//invalid time format			}	$date = preg_replace('/^(\d{2})\/(\d{2})\/(\d{4})$/', '$3-$1-$2', $_POST['date']) . ' ' . $time;}echo "Caller: $caller\n" ."Recipient: $recipient\n" ."Contents: $contents\n" ."Numbers: $numbers\n" ."Date: $date\n";$con = mysql_connect('localhost', 'messages1', 'PASSWORD(\'test\')') or die('Could not connect to server');?>		</pre>	</body></html>

This gives

<html>	<head>		<title>Titled Document</title>	</head>	<body>		<pre>Caller: Jane DoeRecipient: John DoeContents: blah blah blahNumbers: (555) 555-5555, 555-555-5555Date: 2007-11-21 12:00:00<br /><b>Warning</b>:  mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Access denied for user 'messages1'@'localhost' (using password: YES) in <b>C:\wamp\www\library\projects\messages\messages.php</b> on line <b>50</b><br />Could not connect to server

I have ensured that user "messages1" has all data privileges and password "test" (before buttons "Generate" and "Copy" have been pressed in phpMyAdmin), and the connection works with no password. What's wrong with this?

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...