MySQL field type datetime stores value as 0000-00-00 00:00:00 for some reason

[kee2ka4]

Hi everyone,

 

I have a field called created_at of type datetime in mysql, and I am passing in a variable $time that holds the time in format 2008-07-09 18:14:54 but for some strange reason when I insert the $time value into create_at field it saves it as 0000-00-00 00:00:00

 

Here is my php code:

date_default_timezone_set('Asia/Calcutta');
function uk_time()
{
$time = time();

return date('Y-m-d G:i:s', $time);
}  

$time = uk_time();
echo $time;

$post_validations = array('body'  => '/^[[:alnum:][:punct:][:space:]]{1,2000}$/');

/**
 * creates a post
 * @param array $params
 * @return bool
 */
function create_post($params)
{
  $connection = db_connect();

  $query = sprintf("insert into posts 
                       set 
										 body = '%s',
										 user_id = '%s',
										 created_at ='%s'
                     ", 
										 safe_output(mysql_real_escape_string($params['body'])),
										 safe_output(mysql_real_escape_string($params['user_id'])),
										 $time
										 );

	$result = mysql_query($query);

 

I am using the uk_time() function and saving the value to variable $time which is then saved to created_at field in the database. The reason I use uk_time function is because I need the indian timezone and my hosting company has a different timezone settings, hence I can't use the mysql now() function.

 

Can someone please tell me why the correct value is not sored in the database.

 

Thanks,

Ket

[rhodesa]

the function doesn't have access to that variable...replace $time with your function call:

 

date_default_timezone_set('Asia/Calcutta');
function uk_time()
{
$time = time();

return date('Y-m-d G:i:s', $time);
}  

$time = uk_time();
echo $time;

$post_validations = array('body'  => '/^[[:alnum:][:punct:][:space:]]{1,2000}$/');

/**
 * creates a post
 * @param array $params
 * @return bool
 */
function create_post($params)
{
  $connection = db_connect();

  $query = sprintf("insert into posts 
                       set 
										 body = '%s',
										 user_id = '%s',
										 created_at ='%s'
                     ", 
										 safe_output(mysql_real_escape_string($params['body'])),
										 safe_output(mysql_real_escape_string($params['user_id'])),
										 uk_time() //I changed this here
										 );

	$result = mysql_query($query);

[kee2ka4]

aah perfect, it works. I am new to php. thanks to make it clear.

[kee2ka4]

Hi everyone,

 

I have a field called created_at of type datetime in mysql, and I am passing in a variable $time that holds the time in format 2008-07-09 18:14:54 but for some strange reason when I insert the $time value into create_at field it saves it as 0000-00-00 00:00:00

 

Here is my php code:

date_default_timezone_set('Asia/Calcutta');
function uk_time()
{
$time = time();

return date('Y-m-d G:i:s', $time);
}  

$time = uk_time();
echo $time;

$post_validations = array('body'  => '/^[[:alnum:][:punct:][:space:]]{1,2000}$/');

/**
 * creates a post
 * @param array $params
 * @return bool
 */
function create_post($params)
{
  $connection = db_connect();

  $query = sprintf("insert into posts 
                       set 
										 body = '%s',
										 user_id = '%s',
										 created_at ='%s'
                     ", 
										 safe_output(mysql_real_escape_string($params['body'])),
										 safe_output(mysql_real_escape_string($params['user_id'])),
										 $time
										 );

	$result = mysql_query($query);

 

I am using the uk_time() function and saving the value to variable $time which is then saved to created_at field in the database. The reason I use uk_time function is because I need the indian timezone and my hosting company has a different timezone settings, hence I can't use the mysql now() function.

 

Can someone please tell me why the correct value is not sored in the database.

 

Thanks,

Ket

[rhodesa]

read up on variable scope here: http://us2.php.net/variables.scope

 

...and next time, if your MySQL Query isn't doing what you expect, try echoing it. usually it's something simple you are missing and you'll spot it that way.