update query

[frezno]

I've problems finding the right update query.

Here's what i'm trying to do:
there are table 'products' (p), 'categories' and 'products_categories' (pc).
Each product belongs to a category.
Each product has a field 'counter'. This field should be incremented by 1 each time this product is viewed by a user. If a single product is chosen, it works, but if i call a page where all products of a certain category are displayed i'm stuck.

theoretically the query should look like this:

 
UPDATE products
SET		p_counter = p_counter+1
WHERE  p.p_id = pc.product_id
AND	   pc.category_id = $category_id

eg i'm trying to update all products, where the product_id (table products = p.p_id) is equal the product_id in the poducts_categories table (pc.product_id) and the category (table poducts_categories) matches the called category_id

Of course, above code cannot work. But how can i achieve to get it working?

any help is appreciated.

[Grubilo]

You can do it with two queries:

select product_id from pc WHERE
pc.category_id = $category_id
...
now when you fetch them:

 
$tmp = '('; 
while($row=myusql_fetch.....) { 
$tmp .= $row[product_id]; 
} 
$tmp.=')';

and now you can execute another final query:

"update products set ... where p_id IN $tmp";

Hope this will help you!

[frezno]

sounds reasonable.
That's more or less what i'm doing for displaying the items. Additionally to displaying them i can take the result and increment the counter.

Sometimes the simpliest things cost so much headache.

Thanks a bunch.