Jump to content

Archived

This topic is now archived and is closed to further replies.

sambib

problems with timestamp datatype

Recommended Posts

I've set up a database table (mysql 4.1) as such:

 

create table fixtures (

id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,

date TIMESTAMP,

heading TEXT,

details TEXT,

notes TEXT,

PRIMARY KEY(id)

);

 

now when I enter new data (a new set of fixtures) the date returned on the page is fine (the time that i entered the data)

 

code on page to return date:

 

<p><?php echo $fixtures['date']; ?></p> or I've used

 

<p><?php echo date(strtotime($fixtures['date'])); ?></p>

 

however when I go to edit or update that data the date gets messed up and a random date is returned on the page even if my update query doesn't include and reference to the date field. Also if I view the data using MySql Query Browser it shows up as a row of zeros.

 

any clues...?

Share this post


Link to post
Share on other sites

You need to change your date column type to DATETIME. Reason is MySQL automatically updates the first TIMESTAMP column it encounters in a table with the date and time of the last operation performed on the row. Check out the manual under TIMESTAMP for more.

Share this post


Link to post
Share on other sites
You need to change your date column type to DATETIME. Reason is MySQL automatically updates the first TIMESTAMP column it encounters in a table with the date and time of the last operation performed on the row. Check out the manual under TIMESTAMP for more.

313874[/snapback]

 

but that's what i want it to do.....?

 

ie: if i update a page i want to diplay the date when the changes occured.

 

i've tried using datetime as shown and i get similar results as before.

 

create table fixtures (

id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,

date DATETIME,

heading TEXT,

details TEXT,

notes TEXT,

PRIMARY KEY(id)

);

 

any other clues as to why this occurs....?

 

Share this post


Link to post
Share on other sites

it's probably your version of mysql doesn't have the functionality to auto update TIMESTAMP. so just pass NOW() when you update.

Share this post


Link to post
Share on other sites
it's probably your version of mysql doesn't have the functionality to auto update TIMESTAMP. so just pass NOW() when you update.

314205[/snapback]

 

so how would the update query look(in php):

 

$query = "update fixtures set

heading = '$heading',

details = '$details',

notes = '$notes',

date = 'NOW()' I know it's not like this as I've tried it.....:)

where id = '$id'";

 

i'm using mysql 4.1.9

 

cheers

 

Share this post


Link to post
Share on other sites

Just drop the single quotes around NOW(). This is a MySQL function call, not a string. What you're instructing MySQL to do here is to store the string NOW() into your timestamp column, which does not work, because the string NOW() is not a value MySQL can interpret as a date/time value.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.