kreut — 2011-06-25T10:05:26-04:00 — #1
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."','')");
r937 — 2011-06-25T10:32:10-04:00 — #2
i just tested it and your query gets an error --
SQL Error (1292): Incorrect datetime value: ''
always test your queries outside of php first
kreut — 2011-06-25T10:45:17-04:00 — #3
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.
r937 — 2011-06-25T11:46:26-04:00 — #4
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
kreut — 2011-06-25T22:38:07-04:00 — #5
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.