List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 7 2002 5:16pm
Subject:Re: timestamp updated on select
View as plain text  
At 11:41 +0000 11/7/02, Nikolas Galanis wrote:
>Ok, here we are, in much detail. There is a table called 
>translations and another one called poems. Their structure is given 
>in the end. The query (given by a php script by the way) is:
>
>select poems.poem_id, language, translation_title,made_by_id from 
>poems,translations where poems.poem_id=translations.poem_id order by 
>date_added desc limit 0,10
>
>and with this I intend to retrieve the 10 latest additions of 
>translations. However, when running the script, I noticed that 
>always, on the top of the results was the translation link I had 
>clicked last. And this happens all the time. Now that I think of it 
>again, the clicked link contains a mysql query which increments the 
>translation views by one. Hmmmm.....maybe this does the unwanted 
>update....

Exactly.  To prevent this, you can modify your UPDATE query to
set the TIMESTAMP column to its current value.  That will prevent
it from being updated to the current date and time automatically.

I don't know what your query looks like exactly, but you can write
it something like this:

UPDATE translations SET date_added = date_added,
translation_views = translation_views + 1 ...

>
>CREATE TABLE `poems` (
>  `poem_id` smallint(4) unsigned NOT NULL auto_increment,
>  `poem_title` varchar(38) default NULL,
>  `poet_id` smallint(4) unsigned default NULL,
>  `comments` varchar(255) default NULL,
>  `poem_views` smallint(5) unsigned NOT NULL default '0',
>  `poem` text,
>  `poem_comment` text NOT NULL,
>  PRIMARY KEY  (`poem_id`)
>) TYPE=MyISAM
>
>and
>
>CREATE TABLE `translations` (
>  `poem_id` smallint(4) unsigned NOT NULL default '0',
>  `language` varchar(14) NOT NULL default 'english',
>  `translation_title` varchar(54) default NULL,
>  `translation_text` text,
>  `footnotes` text,
>  `made_by_id` smallint(4) unsigned NOT NULL default '0',
>  `contributed` varchar(70) default NULL,
>  `translation_views` smallint(5) unsigned NOT NULL default '0',
>  `date_added` timestamp(14) NOT NULL,
>  PRIMARY KEY  (`poem_id`,`language`,`made_by_id`),
>  KEY `language` (`language`)
>) TYPE=MyISAM
>
>Paul DuBois wrote:
>
>>At 2:09 +0200 11/7/02, Galanis Nikolas wrote:
>>
>>>Yes, that is exactly what happens. It is updated with a simple select
>>>statement. It is a select from a table which contains only one timestamp
>>>column and the order by is made by this timstamp column. And every time
>>>the value if the timestamp is updated.
>>
>>
>>That seems fairly strange.  Let's see an example.
>>
>>>
>>>
>>>On Wed, 6 Nov 2002, Paul DuBois wrote:
>>>
>>>>  At 23:47 +0000 11/6/02, Nikolas Galanis wrote:
>>>>  >Hello
>>>>  >
>>>>  >I have a column of type timestamp(14) and I thought it would not be
>>>>  >updated on a query with simple select statements, though it does! I
>>>>  >read in the manual that it
>>>>  >shouldn't, what could be wrong? Thanks.
>>>>
>>>>  You're saying that performing a SELECT on the table causes the
>>>
>>>  > TIMESTAMP column to *change*?  That shouldn't happen.

Thread
Re: timestamp updated on selectNikolas Galanis7 Nov
  • Re: timestamp updated on selectPaul DuBois7 Nov
    • Re: timestamp updated on selectGalanis Nikolas7 Nov
      • Re: timestamp updated on selectNikolas Galanis7 Nov
        • Re: timestamp updated on selectPaul DuBois7 Nov
      • Re: timestamp updated on selectMichael T. Babcock7 Nov
Re: timestamp updated on selectPaul DuBois7 Nov