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.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
