If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

[RESOLVED] UPDATE using Table Fields?

Hi,

I have an UPDATE question regarding MySQL and PHP.

I have a field that needs to be updated in all rows, excess of 900 rows, called PRICE. I calculate the new price value and want to update the PRICE field, but also the FACTORPRICE field with a value passed in from a FORM.

It looks like each row in the database is affected by a distinct POST variable. So, if you're looking at updating less than 100 or so records, you may as well run perform the series of little updates. However, if you're looking at a significantly greater number of rows, use the POST data to populate a temporary table to JOIN to/from in a single update statement.

Just having another after you said about series of UPDATEs. Could the UPDATE have a WHERE to find the records with the same SKUID? As this is the only variable, and there is only 5 possible SKUIDs. But is the WHERE clause flexiable enough?
SKU = ModelNumber-SKUID00-0000 (0000-nn00-0000)
SKUID can be any of '01','05','18','28' or '66'.

Not surprising lol
SPEC:
I need to reprice every item in the database Table, and the price is based on the following:
$newPrice = ((WEIGHT * $factorPrice) + ($iAuxilaryCosts + (RINGQTY * 8))) * 2;
$iAuxilaryCosts is a constant value of &#163;92.
$factorPrice is an array of ratio's, indexed by the SKU-ID, which is part of the item SKU:
SKU is defined as ModelNumber-SKUID00-0000 (0000-nn00-0000), and is already stored in the record. I just need derive the SKU ID from the SKU vlaue in the field.
SKU ID can be any of '01','05','18','28' or '66'.

I hope that helps? So it would be nice to create 5 UPDATE statements based on the SKU ID, as that is the only variable.

... where S is the start location of the SKUID in the string, which will need to be derived using something like LOCATE if the model number is variable length.

Also, bear in mind, of course, that matching on a substring like this will not be able to take advantage of any indexes--each query will cause a table-scan (which is probably OK if you're dealing with a few thousand rows or less).