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:
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_idCode:UPDATE products SET p_counter = p_counter+1 WHERE p.p_id = pc.product_id AND pc.category_id = $category_id
Of course, above code cannot work. But how can i achieve to get it working?
any help is appreciated.