Jump to content

problems with timestamp datatype


sambib

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

Link to comment
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.

Link to comment
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....?

 

Link to comment
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

 

Link to comment
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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.