UPDATE is updating something i didn't tell it to!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

UPDATE is updating something i didn't tell it to!

Post by Sevengraff »

Ok, im using MySQL. I've been working on a sorta light-weight CMS that lets me post articles and such. users can post comments on the articles, and it logs the date&time of it. but, when it adds a comment, it changes the time that the article was created, when it should only add to the number of comments.

here are my queries:

Code: Select all

// add comment to db

INSERT INTO `comments` ( `id` , `parent` , `author` , `email` , `comment` , `created` )
VALUES (
'', '$id', '$author', '$email', '$comment', NOW( )
);

SELECT * FROM articles WHERE id='$id'  // to get the count of comments on this perticuar article

UPDATE articles SET comments='$newcount' WHERE id='$id'  // and to update that count.
As you can see, the only UPDATE query affects the comments row, not the row where i keep the date & time. I dont understand why it changes it.
Any idea on why it affects the article?
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

try

Post by AVATAr »

try putting the date in a variable before you make the insert, and then insert using that variable (dont use NOW())... im guessing here.
Last edited by AVATAr on Sun Feb 02, 2003 11:35 am, edited 1 time in total.
kcomer
Forum Contributor
Posts: 108
Joined: Tue Aug 27, 2002 8:50 am

Post by kcomer »

I'm geusing you are using the filed type of timestamp for the date. Try useing the datetime field type. I belive the timstamp filed will get updated with the newest timestamp every time the record is modified, no matter what field. This should fix it for you.

Keith
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Post by Sevengraff »

ok, thanks kcomer, that was my problem. didn't realize that it would auto-change. i'll remember that from now on. thanks!
Post Reply