SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: update query

  1. #1
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    update query

    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:
    Code:
     
    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.
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  2. #2
    SitePoint Enthusiast Grubilo's Avatar
    Join Date
    Oct 2002
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can do it with two queries:

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

    Code:
     
    $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!
    Web tutorials
    http://webclass.ru

  3. #3
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •