I am confused with the php time() and date() function and mysql now()? Need some

[kee2ka4]

Hi,

 

I have a site that stores user comments and the date/time of when the comments was placed. At the moment I insert comments and store date/time using the Now() as shown below:

sprintf("INSERT INTO comments 
                  SET
		  body = '%s',
		  created_at = now(),
		  user_id = '%s'
                  ", 
		  safe_output(mysql_real_escape_string($params['body'])),
		  safe_output(mysql_real_escape_string($params['user_id'])));

The problem with the above is that the now() stores the date/time according to the hosting server location. I want the site to display and store date/time set to the timezone in London. Hence I have used this php function: date_default_timezone_set('Europe/London'); and used the time() in the insert query rather then the mysql now(), as shown below:

sprintf("INSERT INTO comments 
             SET 
	     body = '%s',
	     created_at =" .time(). ",
	     user_id = '%s'
             ", 
	     safe_output(mysql_real_escape_string($params['body'])),
	     safe_output(mysql_real_escape_string($params['user_id'])));

However, I get 00 for the date value and 00:00 for time value. I think I get these null values because I have the created_at field as type "datetime" in mysql and also may be because time() function is a timestamp? Is that correct!

 

If I want to use the time() and date() php functions do I have to change the create_at field as type "string" rather then "datetime"? and if I save the timestamp using time() as a string, can I then display the date the comment was left using the date() and passing the timestamp as a parameter?

I am new to php and need some guidance in correct way to using the php functions time() and date() rather then using the mysql now(). Is there any guide's or tutorial that can make things clear for me.

 

Thanks,

Ket

[kee2ka4]

Can I save the timestamp value in the database using the temp() function and then convert it in the date when pulling out the timestamp info from the database?

[Wolphie]

You can create a timestamp field in the database, and when a new record is inserted, it will insert the current time that corresponding record was added. All done automatically. It's in the form of a unix timestamp though, I.e. 01-01-2008 12:45

[kee2ka4]

Hi, thanks for your reply. Will that store the timestamp according to the timezone specified in the php function: date_default_timezone_set('Europe/London'), hence in this case Euprope/london?