Need help with SQL error

[webguync]

Hi, I am trying to set a timestamp when info is submitted into the table I have set up in the MySQL database. I am not sure what to add to the INSERT statement to do this. The timestamp field is set up IN mySQL like so:

]code]

`submit_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

[/code]

 

and my SQL is currently:

 

$sql="INSERT INTO Responses (`editor_name`,`Answer1`,`Answer2`,`Answer3`,`Answer4`,`Answer5`,`Answer6`,`Answer7`,`Answer8`,`Answer9`) VALUES ('$editor_name','$A1','$A2','$A3','$A4','$A5','$A6','$A7','$A8','$A9')"; 

[Mchl]

`submit_timestamp` datetime NOT NULL default CURRENT_TIMESTAMP,

 

Note that it has to be the first timestamp column in the table for this to work.

[webguync]

I think I might be misunderstanding what I need to do, I tried this but get an error.

 

$sql="INSERT INTO Responses (`editor_name`,`Answer1`,`Answer2`,`Answer3`,`Answer4`,`Answer5`,`Answer6`,`Answer7`,`Answer8`,`Answer9``submit_timestamp` datetime NOT NULL default CURRENT_TIMESTAMP, VALUES $sql="INSERT INTO Responses (`editor_name`,`Answer1`,`Answer2`,`Answer3`,`Answer4`,`Answer5`,`Answer6`,`Answer7`,`Answer8`,`Answer9`) VALUES ('$editor_name','$A1','$A2','$A3','$A4','$A5','$A6','$A7','$A8','$A9')"; 

[fesan]

I have left the timestamp the whole timestamp column out in the insert line.

 

$sql = "INSERT INTO $dbtable (user_name, password, name, user_level, settings_day_start, settings_day_stop) VALUES ('$user_name', '$md5_pasword', '$name', '$user_level', '-1', '7')";

 

My table looks like this:

ID | NAME | USER_NAME | PASSWORD | USER_LEVEL | MADE | SETTINGS_DAY_START | SETTINGS_DAY_STOP

 

Where MADE is my timestamp column. So MySQL generates the timestamp for me.

[Mchl]

What I mean is you need to change default value for this column in your table. Not drop this into arbitrary place in your query, because it will obviously NOT work.

[Maq]

Hi webguync,

You need to add the 'submit_timestamp' entry just like the rest of your values in the INSERT statement, or you can take Mchl's approach and add it automatically.

[webguync]

@mchl using php myadmin, I don't see an option to change the default to CURRENT_TIMESTAMP. There is an option to change the function to CURTIME, would this do the same thing?

 

@fesan in your VALUES what does listing '-1', '7' do?

 

thanks for the responses!

[Mchl]

@mchl using php myadmin, I don't see an option to change the default to CURRENT_TIMESTAMP. There is an option to change the function to CURTIME, would this do the same thing?

 

Click 'Structure' tab not 'Insert' tab. Click the pencil icon next to your timestamp column and selecr CURRENT_TIMESTAMP in 'Default' field.

[webguync]

that option isn't available to me using phpmyadmin 2.8.2.4. The default field doesn't have a pull-down option for me.

[Mchl]

Is your column type actually a TIMESTAMP? If it is, just type in CURRENT_TIMESTAMP into default field.

[webguync]

OK, I see now. I didn't have set to TIMESTAMP, which I do now, and also to CURRENT_TIME, however when a record is inserted into the table, the timestamp field doesn't update. My structure is now:

 

`submit_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,

[Mchl]

Please post the entire table structure using SQL below:

SHOW CREATE TABLE tableName

[webguync]

output below

 

CREATE TABLE `Responses` (\n  `editor_name` text,\n  `Answer1` text,\n  `Answer2` text,\n  `Answer3` text,\n  `Answer4` text,\n  `Answer5` text,\n  `Answer6` text,\n  `Answer7` text,\n  `Answer8` text,\n  `Answer9` text,\n  `submit_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,\n  `user_id` int(11) NOT NULL auto_increment,\n  PRIMARY KEY  (`user_id`)\n) ENGINE=MyISAM DEFAULT CHARSET=utf8

[Mchl]

And your insert statement?

[webguync]

I had forgotten to take out the value I was trying in SQL, so now it works now. One for small thing though. The value displayed on the server is three hours behind, what I want it to display (west coast time vs. east coast time). Anyway to have it set (+3 hours)?

[Mchl]

You should apply any timezone offsets when displaying the time (not when inserting).

Use date_default_timezone_set

[webguync]

got it, thanks!