querying the result set

[frezno]

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) :

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:

// 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 avail, 1);
$clause_no_avail = substr($no avail, 1);

// 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?

[guido2004]

A better way is

UPDATE table            
SET field3 = '1'          
WHERE field1 = '1'
OR    field2 = '1'

[r937]

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

[guido2004]

guido, you forgot to update field3 to 0

No I didn't. I left something to do

[frezno]

A better way is

holy moly, can it be that simple?

[r937]

holy moly, can it be that simple?

almost

there is still something else to do

[frezno]

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

[r937]

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...

[frezno]

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)

[r937]

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

[frezno]

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.

[guido2004]

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:

UPDATE table            
SET field3 = 
         CASE 
            WHEN field1 = '1' OR field2 = '1' THEN '1'          
            ELSE '0'
         END

[frezno]

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.

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($data, 1);

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.

[r937]

guido's UPDATE statement only executes once

and without [shudder] comma lists

[StarLion]

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))

[guido2004]

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

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))

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.

[frezno]

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...

[StarLion]

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

[r937]

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

[StarLion]

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.

[StarLion]

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.

SELECT productid,quantity,available FROM table;

while($row = mysql_fetch_array($res)) {
  if(($row['available'] = 0 && ($row['quantity'] >= 1 || $list[$row['productid']] >= 1)) || ($row['available'] = 1 && $row['quantity'] == 0 && $list[$row['productid']] == 0)) {
    $out[] = $row['productid'];
  }
}
$string = implode(',',$out); 

UPDATE table SET available = available + 1 MOD 2 WHERE productid IN ( $string )

[oddz]

Create a temporary landing table for the vendor data than a query like the below can be used.

(proof of concept)

UPDATE
     products
 INNER
  JOIN
     tmp_stock
    ON
     products.partnumber = tmp_stock.partnumber
   SET
    products.availability = tmp_stock.availability