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?
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
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
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)
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.
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))
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…
