problems with timestamp datatype

[sambib]

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...?

[Cook]

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.

[sambib]

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....?

 

[ryanlwh]

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

[sambib]

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

 

[Cook]

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.