Out of time

Databases
1

Hello,

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…

-Eric

Fields: log_id, tax_id, email, dbTime
Structure
Type: timestamp
Default: Current_Timestamp
Null: No

	$log_query=mysql_query("INSERT INTO log VALUES ('','".$txn_id."','".$payer_email."','')");
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 :slight_smile:

3

Hello!

First, here’s the SHOW CREATE TABLE:

SQL result

Host: localhost
Database: myaglebr__kreut2
Generation Time: Jun 25, 2011 at 10:40 AM
Generated by: phpMyAdmin 3.3.10.1 / MySQL 5.1.56-log
SQL query: SHOW CREATE TABLE log;
Rows: 1

Table Create Table
log CREATE TABLE log (
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 (log_id)
) 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.

-Eric

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

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.

Eric