Update from a select

[Bob Bedford]

PHP + MYSQL.

I've a table of articles in my database.
in this table they are 2 fields: CounterLogResult and CounterClick

Any user may search articles with some fields (a common search form). I
would like that every time the article is in the result of the query, the
CounterLogResult value is incremented. If the user click on the article (for
seeing the details of a result), the counterClick is incremented. I know how
to increment CounterClick (this is very easy), but how to increment
CounterLogResult ???

Something like:
Update Announce SET CounterLogResult = CounterLogResult+1 Where
Announce.IDArticle in (Select IDArticle FROM Article WHERE Article.price >
30 LIMIT 0,10);

That's because the select query for my page returns only 10 results at one
time. (in fact the Select is the same I use for showing the result of the
form fields).

BoB

[Andy Hassall]

On Thu, 15 Jan 2004 18:46:00 +0100, "Bob Bedford"
<bedford1@YouKnowWhatToDohotmail.com> wrote:

>PHP + MYSQL.
>
>I've a table of articles in my database.
>in this table they are 2 fields: CounterLogResult and CounterClick
>
>Any user may search articles with some fields (a common search form). I
>would like that every time the article is in the result of the query, the
>CounterLogResult value is incremented. If the user click on the article (for
>seeing the details of a result), the counterClick is incremented. I know how
>to increment CounterClick (this is very easy), but how to increment
>CounterLogResult ???
>
>Something like:
>Update Announce SET CounterLogResult = CounterLogResult+1 Where
>Announce.IDArticle in (Select IDArticle FROM Article WHERE Article.price >
>30 LIMIT 0,10);
>
>That's because the select query for my page returns only 10 results at one
>time. (in fact the Select is the same I use for showing the result of the
>form fields).

Production versions of MySQL don't yet support subqueries, only the beta
versions.

Execute the subquery separately then do an update per row returned.

--
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>