www.webdeveloper.com
Results 1 to 14 of 14

Thread: [RESOLVED] UPDATE using Table Fields?

Hybrid View

  1. #1
    Join Date
    Mar 2007
    Location
    Cotswolds, England
    Posts
    105

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

    QUERY:
    UPDATE cookers SET PRICE=$newPrice, FACTORPRICE=$factorPrice

    $newPrice = ((WEIGHT * $factorPrice) + ($iAuxilaryCosts + (RINGQTY * 8))) * 2;
    $factorPrice = formValue;
    uppercase names = table fields

    Can this UPDATE operation be created without a need of a previous SELECT statement and looping through the entire resultset and updating row by row?

    Thanks,

    Barton.

  2. #2
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    You should be able run it almost exactly as you've posted it ...
    Code:
    UPDATE cookers SET PRICE ((WEIGHT * $factorPrice) + ($iAuxilaryCosts + (RINGQTY * 8))) * 2, FACTORPRICE=$factorPrice;
    Obviously, you want to sanitize and enquote your form values first (make sure they're numbers before interpolation).

  3. #3
    Join Date
    Mar 2007
    Location
    Cotswolds, England
    Posts
    105

    Exclamation

    Hi,
    thanks, a neat solution, it's amazing what you can learn

    I've just found the killer, $factorPrice is variable depending upon the SKU. Am I correct in that assumption that this elegant solution will not work?

    Sorry for not seeing that before, and thanks again,

    Barton.

  4. #4
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    I'm not sure. What does your SKU look like? And what is the algorithm that incorporates it into $factorPrice?

  5. #5
    Join Date
    Mar 2007
    Location
    Cotswolds, England
    Posts
    105

    Exclamation

    Hi again,
    SKU = ModelNumber-SKUID00-0000 (0000-nn00-0000)
    my SKUID can be any of '01','05','18','28' or '66', and the factorprice is an array with the key as the SKUID.

    $sSKUID = getIdFromSKU(SKU);
    $newPrice = ($_POST['factorprice'][$sSKUID ] * WEIGHT) + (RINGQTY * 8) + $iAuxCosts;

    Hope that makes sense, thanks,

    Barton.

  6. #6
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    Could I see the code you're currently using, starting from the capture of form information to the execution of your queries?

  7. #7
    Join Date
    Mar 2007
    Location
    Cotswolds, England
    Posts
    105
    Thank you once again...

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

    Could this be a solution?

    Sorry to be a pain,

    Barton.

  8. #8
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    Sure. Something like this?
    Code:
    UPDATE tablename set this=if(skuid=1,some_expression1,if(skuid=2,some_expression2,NULL)) where skuid in (1,2);
    I have to be honest, I'm a bit confused about what you're really trying to do here--so I'm having to tough time making the appropriate suggestions ...

  9. #9
    Join Date
    Mar 2007
    Location
    Cotswolds, England
    Posts
    105
    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 £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.

    Hope that clears up my self-inflicted mire,

    Barton.

  10. #10
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    So, would the following 5 queries do what you need?

    Code:
    UPDATE cookers SET PRICE=((WEIGHT * $factorPrice['01']) + ($iAuxilaryCosts + (RINGQTY * 8))) * 2, FACTORPRICE=$factorPrice['01'] WHERE SUBSTRING(SKU, S, 2) = '01';
    
    UPDATE cookers SET PRICE=((WEIGHT * $factorPrice['05']) + ($iAuxilaryCosts + (RINGQTY * 8))) * 2, FACTORPRICE=$factorPrice['05'] WHERE SUBSTRING(SKU, S, 2) = '05';
    
    UPDATE cookers SET PRICE=((WEIGHT * $factorPrice['18']) + ($iAuxilaryCosts + (RINGQTY * 8))) * 2, FACTORPRICE=$factorPrice['18'] WHERE SUBSTRING(SKU, S, 2) = '18';
    
    UPDATE cookers SET PRICE=((WEIGHT * $factorPrice['28']) + ($iAuxilaryCosts + (RINGQTY * 8))) * 2, FACTORPRICE=$factorPrice['28'] WHERE SUBSTRING(SKU, S, 2) = '28';
    
    UPDATE cookers SET PRICE=((WEIGHT * $factorPrice['66']) + ($iAuxilaryCosts + (RINGQTY * 8))) * 2, FACTORPRICE=$factorPrice['66'] WHERE SUBSTRING(SKU, S, 2) = '66';
    ... 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).

  11. #11
    Join Date
    Mar 2007
    Location
    Cotswolds, England
    Posts
    105

    Exclamation

    Thank you so much...

    Worked like a charm, and the operation completed in a fraction of the original query time.

    Thank you,

    Barton

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles