SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  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)

    querying the result set

    just wondering whether there is any best practice or or the like for following:

    I select a set data (couple thousend of rows) and depending on the values of two fields i have to update a third field (or not).

    two ways to achieve this are imho (pseudo code) :
    PHP Code:
    SELECT id
         
    field1
         
    field2
      FROM table

    foreach ($result as $r)
    {
        if (
    $r->field1 == '1' || $r->field2 == '1')
        {
             
    UPDATE table
                SET field3 
    '1'
              
    WHERE id $r->id
        
    }
        else
        {
             
    UPDATE table
                SET field3 
    '0'
              
    WHERE id $r->id
        
    }

    or, as alternative:
    PHP Code:
    // same select query

    $is_avail '';
    $no_avail '';

    // create comma-separated lists
    foreach ($result as $r)
    {
        if (
    $r->field1 == '1' || $r->field2 == '1')
        {
             
    $is_avail .= ","$r->id;
        }
        else
        {
              
    $no_avail .= ","$r->id;
        }
    }

    // removing the first comma
    $clause_is_avail substr($is avail1);
    $clause_no_avail substr($no avail1);

    // updating the fields
    UPDATE table
       SET field3 
    '1'
     
    WHERE id
        IN 
    ($clause_is_avail)

    UPDATE table
       SET field3 
    '0'
     
    WHERE id
        IN 
    ($clause_no_avail
    for sure, the first example is more readable but what's the 'better' way to handle this?
    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
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    A better way is
    Code:
    UPDATE table            
    SET field3 = '1'          
    WHERE field1 = '1'
    OR    field2 = '1'

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    guido, you forgot to update field3 to 0

    frezno, what is the purpose of retrieving thousands of rows? just to interrogate each row to find out how to update it? you definitely don't want to do that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    guido, you forgot to update field3 to 0
    No I didn't. I left something to do

  5. #5
    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)
    Quote Originally Posted by guido2004 View Post
    A better way is
    holy moly, can it be that simple?
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by frezno View Post
    holy moly, can it be that simple?
    almost

    there is still something else to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    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)
    Quote Originally Posted by r937 View Post
    frezno, what is the purpose of retrieving thousands of rows?
    ok, that's the story behind the story:

    i'm getting product lists of several manufacturers on a daily basis.
    now i have to compare the availabilities of the products with the availability of the products im my stock. ie

    the table has (among others) three fields (int, char, char) :
    qty in house (f1), available from manufacturer (f2), display as available (3).

    Now, if a product is either in my stock (field set to 1 or actual qty) or available from the manufacturer (field set to 1) it can be sold (field set to 1)
    if it's neither in stock or available from the manufacturer (both fields set to 0) it cannot be sold and thus the 3rd field is set to 0 as well.

    That said, combinations can be
    f1 - f2 - f3
    1 - 1 - 1
    1 - 0 - 1
    0 - 1 - 1
    0 - 0 - 0

    and now what i'm trying to do is finding a convenient way to update the third field depending on the values of field1 and field2


    btw, good to see you still around here, Rudy, after all those years
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i'm not sure you even need field3 in the table

    and it would be nice if fields 2 and 3 were TINYINT instead of CHAR

    but in any case, you still want to update field3 to 0 for some rows, right? unless, of course, it starts out with 0 as the default...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    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)
    you're right, i don't necessarily need the third field.
    it's left over from an previous db design where i wanted not to display certain items although available.
    perhaps i should ditch it.

    the 'char' setting is something you mentioned a couple of years ago in a thread when it came to setting an entry to either 1 or 0 (ie using it as flag)
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    CHAR is fine for 1/0, and TINYINT is too, but with TINYINT you can do a couple of extra things

    but if field3 is in doubt, i wouldn't pursue this further
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    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)
    ok, third field is eliminated - updates have been modified - good job, guys

    but the basic question as such would still be valid - just to sitisfy my curiosity.
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by frezno View Post
    ok, third field is eliminated - updates have been modified - good job, guys

    but the basic question as such would still be valid - just to sitisfy my curiosity.
    Just to satisfy your curiosity:
    Code:
    UPDATE table            
    SET field3 = 
             CASE 
                WHEN field1 = '1' OR field2 = '1' THEN '1'          
                ELSE '0'
             END

  13. #13
    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)
    thanks Guido, but that wasn't what i was talking of

    let me summarize it:
    i have a result set and process it with foreach()
    depending on the value a have to do an UPDATE.
    Now the question was:
    Is it better to do the UPDATE within the foreach or store the data eg as a commaseparated list and when foreach() finished with all data i call my UPDATE and change the fields with using an in-clause.

    PHP Code:
    foreach ($result as $r)
    {
        if (
    $r->qty == 1)
            
    UPDATE table SET qty 1 WHERE id $r->id
    }

    // or
    foreach ($result as $r)
    {
        if (
    $r->qty == 1)
            
    $data .= ','$r->id;
    }

    $d substr($data1);

    UPDATE table SET field '1' WHERE id IN ($d
    ie what puzzling me is what's better:
    calling UPDATE a bazillion of times withing foreach() or just once after all data has been collected.
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    guido's UPDATE statement only executes once

    and without [shudder] comma lists
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    guido's statement also needs to be f1 >= 1 (since the poster said 'Now, if a product is either in my stock (field set to 1 or actual qty) or available from the manufacturer (field set to 1) it can be sold (field set to 1))

  16. #16
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by frezno
    Is it better to do the UPDATE within the foreach or store the data eg as a commaseparated list and when foreach() finished with all data i call my UPDATE and change the fields with using an in-clause.
    It is better to do it my way

    Quote Originally Posted by StarLion View Post
    guido's statement also needs to be f1 >= 1 (since the poster said 'Now, if a product is either in my stock (field set to 1 or actual qty) or available from the manufacturer (field set to 1) it can be sold (field set to 1))
    Quote Originally Posted by frezno View Post
    That said, combinations can be
    f1 - f2 - f3
    1 - 1 - 1
    1 - 0 - 1
    0 - 1 - 1
    0 - 0 - 0
    I based myself on this info from the OP.

  17. #17
    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)
    Gentlemen, thank you. I really appreciate your efforts on this.

    Unfortunately i wasn't very clear with my question.
    This third field thingy wasn't in my mind anymore, since Rudy realized correctly that's better to purge it.
    Has been done and the UPDATE as Guido suggested is in use now.

    So the pseudo code of post #13 was more a thing to satisfy my curiosity and wasn't meant to anything specific.

    But you asked for it , so let's do a realistic example (with the same problem for me) as in #13:

    I have an ASCII list of data
    each row consists of 2 entries: partnumber and availability (set to 0 or 1)
    Now i have to compare these entries with those in my database
    If the availability of the product (partnumber) is 1 in the DB and 1 in the list = no change
    If the availability of the product (partnumber) is 1 in the DB and 0 in the list = set to 0
    If the availability of the product (partnumber) is 0 in the DB and 1 in the list = set to 1
    If the availability of the product (partnumber) is 0 in the DB and 0 in the list = no change

    ok, how do i handle this?
    I read the ASCII file line by line (fgets) and explode the line to break it down into partnumber and availability.
    then i check whether this item has a 0 or a 1 as availability.
    now i have the choice of either update the according entry in my db immediately within the loop
    or store the data in a list, array, ... to update all of them later
    When storing the data i actually would get two lists,
    one with the partnumbers of all item with availability 0 and
    another one with the partnumbers of all item with availability 1
    which would result in two UPDATEs, according to my understanding.

    Or is there a better/different way to handle this?

    The more i think about this, the more my brain gets into a quirks mode...
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  18. #18
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    1 SELECT, 2 UPDATEs, and you should only UPDATE those rows that have a change.

    EDIT: NO! 1 Update! Muahaha. You can do it in 1 update. update the field with field = field + 1 MOD 2

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    Muahaha. You can do it in 1 update. update the field with field = field + 1 MOD 2
    um... no

    there are 2 fields involved

    see post #12

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    um... no

    there are 2 fields involved

    see post #12

    Uhm, no.

    There is 1 field and 1 Intermediate (PHP, i'm assuming) bound list of data.

    See post #17, which is what i was responding to.

  21. #21
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    And I'm gonna tick Rudy off more and suggest a long arsed query; one long arsed in execution, and one long arsed in length.

    Code MySQL:
    SELECT productid,quantity,available FROM table;

    PHP Code:
    while($row mysql_fetch_array($res)) {
      if((
    $row['available'] = && ($row['quantity'] >= || $list[$row['productid']] >= 1)) || ($row['available'] = && $row['quantity'] == && $list[$row['productid']] == 0)) {
        
    $out[] = $row['productid'];
      }
    }
    $string implode(',',$out); 
    Code MySQL:
    UPDATE table SET available = available + 1 MOD 2 WHERE productid IN ( $string )

  22. #22
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Create a temporary landing table for the vendor data than a query like the below can be used.

    (proof of concept)
    Code SQL:
    UPDATE
         products
     INNER
      JOIN
         tmp_stock
        ON
         products.partnumber = tmp_stock.partnumber
       SET
        products.availability = tmp_stock.availability
    The only code I hate more than my own is everyone else's.


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
  •