Time Stamp - Left Blank - Help

[AdWarm]

Hi Guys,

I need some help please, for some reason when a new record is added the field called TimeStamp is left blank. Here is how the field is setup:

Column	Type	                 Attributes                           	Null	Default	                Extra

TimeStamp	timestamp		on update CURRENT_TIMESTAMP	No	CURRENT_TIMESTAMP	ON UPDATE CURRENT_TIMESTAMP

Can anyone see anything as to why on a new record its left as 0000-00-00 00:00:00 ?

Below is the PHP code i am using to insert a new record:

$query6 = sprintf("INSERT INTO logs VALUES ('',%s,%s,'')",
            quote_smart($FullName),
            quote_smart($log6)); 

The last part thats blank '' is for the TimeStamp as iv left it blank thinking when a new record is added MySQL will add the current time/date automatically, any help would be great!

Thank you..!

[r937]

could you do a SHOW CREATE TABLE for that table please

[AdWarm]

Hi,

Thanks for the reply, I've never used that function before so not sure what im suppose to do with it, when i enter it in the SQL for PHPMYADMIN its coming up with an error. Can you give me some guidance please.

Thank you.

[cpradio]

Hi,

Thanks for the reply, I've never used that function before so not sure what im suppose to do with it, when i enter it in the SQL for PHPMYADMIN its coming up with an error. Can you give me some guidance please.

Thank you.

SHOW CREATE TABLE logs

Via http://dev.mysql.com/doc/refman/5.0/...ate-table.html

[r937]

INSERT
  INTO logs
     ( EditedBy , Log )
VALUES
     ( %s , %s )

do not mention the columns that have a default behaviour

two of yours do, two don't

supply values only for the columns that don't

[K. Wolfe]

The last part thats blank '' is for the TimeStamp as iv left it blank thinking when a new record is added MySQL will add the current time/date automatically, any help would be great!

Nope.

Do not include the timestamp in your INSERT statement. Your telling it to add a null time stamp rather than letting it do its thing.

[AdWarm]

Hi,

I've change it to this but its still not adding the time stamp:

$log = "added a new contact - $postfullname from $CompanyName";
$query4 = sprintf("INSERT INTO logs VALUES ('',%s,%s)",
            quote_smart($FullName),
            quote_smart($log)); 

[AdWarm]

Thanks, below is the information:

CREATE TABLE `logs` (
`LogID` int(11) NOT NULL AUTO_INCREMENT,
`EditedBy` text NOT NULL,
`Log` text NOT NULL,
`TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`LogID`)
) ENGINE=MyISAM AUTO_INCREMENT=174 DEFAULT CHARSET=latin1

[rcashell]

The issue here is that you are looking for the default behavior for timestamp but you are attempting to register an empty string as a timestamp value. This is not permitted. As r937 described above you should really name the columns you do not want default behavior for. Another way of doing this is passing a NULL value when you want the default behavior to be initiated.

For example:

$query6 = sprintf("INSERT INTO logs VALUES (null,%s,%s,null)",
            quote_smart($FullName),
            quote_smart($log6));

Would have worked.

[K. Wolfe]

The issue here is that you are looking for the default behavior for timestamp but you are attempting to register an empty string as a timestamp value. This is not permitted. As r937 described above you should really name the columns you do not want default behavior for. Another way of doing this is passing a NULL value when you want the default behavior to be initiated.

For example:

$query6 = sprintf("INSERT INTO logs VALUES (null,%s,%s,null)",
            quote_smart($FullName),
            quote_smart($log6));

Would have worked.

You should not be passing the value at all, not even using NULL, as r937 and I stated.