I’m trying to get my MySql database to automatically enter in a timestamp when I insert data. Based on the structure/code below, I just get 0’s in my dbTime column. Any thoughts would be appreciated…
Fields: log_id, tax_id, email, dbTime
$log_query=mysql_query("INSERT INTO log VALUES ('','".$txn_id."','".$payer_email."','')");
i just tested it and your query gets an error –
SQL Error (1292): Incorrect datetime value: ‘’
always test your queries outside of php first
First, here’s the SHOW CREATE TABLE:
Generation Time: Jun 25, 2011 at 10:40 AM
Generated by: phpMyAdmin 126.96.36.199 / MySQL 5.1.56-log
SQL query: SHOW CREATE TABLE log;
Table Create Table
log CREATE TABLE
log_id int(11) NOT NULL AUTO_INCREMENT,
txn_id varchar(30) NOT NULL,
payer_email varchar(255) NOT NULL,
dbTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
Second, regarding the PHP — unless I’m being batty, it actually does enter info into my database — just not the date. And regarding the error, since I tell it to put in the timestamp (at least I think I do) automatically, shouldn’t I not have to put anything in the column?
Thanks for looking into this.
your CREATE TABLE looks okay (i just wanted to reassure myself that the timestamp column was declared properly)
when i tested it, the column accepted a NULL and converted it to the default value, CURRENT_TIMESTAMP
however, when i attempted to insert row with a zero-length string as the value for the timestamp column, i got that error message and it definitely did not insert a row
you have several options: provide a list of column names for the INSERT and omit both the auto_increment column and the timestamp column (thus allowing their default values to be used), or use either NULL or CURRENT_TIMESTAMP as the value to be inserted into the timestamp column instead of the zero-length string
a string, whether zero-length or whatever, is, you gotta admit, a pretty questionable value for a column that isn’t a TEXT or VARCHAR
maybe it is version dependent, and in your case it converted the zero-length string into a 0 value, but in my case it produced the error message
best to tidy up your syntax and provide only valid values
Thanks for your solutions! I thought that it was a bit weird that I input a 0 length string for the id and it entered something (this was my index), but didn’t work for the timestamp.
Enjoy the rest of your weekend.