Jump to content


Photo

Incorrect datetime value:??


  • Please log in to reply
8 replies to this topic

#1 icehawk012

icehawk012
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 07 September 2006 - 01:49 AM

Alright, for some reason this script I wrote is working with mySQL 5 but not 4. I've been running through it trying to figure out what's going on but I keep getting the same error :

News could not be added because: Incorrect datetime value: '9.7.2006 1:41am' for column 'date' at row 1


So here's what I've got as far as adding the "News":

} else if (isset ($_POST['submit'])) {
		include("db2.php");
		$realdate = date ( 'n.j.Y g:ia' );
		$writenews = "INSERT INTO park_news (title, content, date) VALUES ('{$_POST['posttitle']}', '{$_POST['postcontent']}', '{$realdate}')";
		if (@mysql_query($writenews)) {
			print ("<p>News successfully added.</p>");
		} else {
			print ("<p>News could not be added because: <b>" . mysql_error() . "</b></p>");
		}
		print ("<img src=\"spacer.gif\" width=\"75%\" height=\"1\" />");

Should I be using the time() function to get my date and then reformatting it when it spits it back out?


#2 pocobueno1388

pocobueno1388
  • Members
  • PipPipPip
  • Advanced Member
  • 3,369 posts
  • LocationPalm Beach, Florida

Posted 07 September 2006 - 02:35 AM

I just use the NOW() function.

$realdate = NOW();

Although I am not sure how to or even if you can format that to how you want it. It works fine for me for datetime feilds.

#3 icehawk012

icehawk012
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 07 September 2006 - 02:54 PM

The now() function won't work. I get the following error when I try to use it:

Fatal error: Call to undefined function:


Any other ideas?

#4 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 07 September 2006 - 02:59 PM

now is a mysql function. It is not a PHP function. That is why you get an undefined function.

YOu use NOW() in your MySQL Query

#5 designationlocutus

designationlocutus
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts

Posted 07 September 2006 - 03:01 PM

What is the datatype in your database for the date?

#6 icehawk012

icehawk012
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 07 September 2006 - 03:03 PM

DATETIME

#7 icehawk012

icehawk012
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 07 September 2006 - 03:07 PM

Ok thanks for clearing that up. I used the NOW() function in my MySQL query and it works now, but how would I go about making it my time and not the server time? Or is that even possible?

#8 designationlocutus

designationlocutus
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts

Posted 07 September 2006 - 03:18 PM

Yeah its possible. PHP5 introduced some date/time contants to use. You will probably have to use php date function in order to get them to work properly and then add to the database afterward.

http://uk.php.net/ma...etime.constants

#9 icehawk012

icehawk012
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 07 September 2006 - 03:47 PM

Ok, I've got it all figured out so I figured I would update everyone on how it's done. I'm using the MySQL NOW() function, but combining it with the MySQL CONVERT_TZ() function which takes three variables: DATETIME, Timezone1, and Timezone2. Timezone1 being the starting timezone and Timezone2 being the timezone to be converted to. The code ends up looking like so:

} else if (isset ($_POST['submit'])) {
		include("db2.php");
		$writenews = "INSERT INTO park_news (title, content, date) VALUES ('{$_POST['posttitle']}', '{$_POST['postcontent']}', CONVERT_TZ(NOW(),'+00:00','-07:00'))";
		if (@mysql_query($writenews)) {
			print ("<p>News successfully added.</p>");
		} else {
			print ("<p>News could not be added because: <b>" . mysql_error() . "</b></p>");
		}
		print ("<img src=\"spacer.gif\" width=\"75%\" height=\"1\" />");

Works flawlessly.  ;D




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users