Time Stamp - Left Blank - Help

Databases
1

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…!

2

could you do a SHOW CREATE TABLE for that table please

3

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.

4

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.

5

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));

:frowning:

6 
SHOW CREATE TABLE logs

Via http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html

7

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

8 
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

9

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.

10

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