[RESOLVED] UPDATE using Table Fields?

[Barton68]

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.

[svidgen]

You should be able run it almost exactly as you've posted it ...

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

[Barton68]

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.

[svidgen]

I'm not sure. What does your SKU look like? And what is the algorithm that incorporates it into $factorPrice?

[Barton68]

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.

[svidgen]

Could I see the code you're currently using, starting from the capture of form information to the execution of your queries?

[svidgen]

... encased in the appropriate CODE tags, please.

[Barton68]

Hello again,

code as requested sir:
FactorPrice is now jcprice

<div id="wrapper">
  <h2>Reprice Items based on JCPrice</h2>
  <!-- Reprice Form -->
  <form action="" id="frmReprice" onsubmit="return validateForm(this);" method="post">
  <fieldset>
    <legend>Enter Repricing Information</legend><br />
  	<ol class="forms">
      <li><label for="txtProduct">Product Prices to Update:</label>
        <!-- Decides which table name to UPDATE -->
  			<select name="txtProduct" id="txtProduct">
          <option value="-1" selected="selected">Please Select...</option>
  			<?php $selected = (isset($_POST['txtProduct'])) ? $_POST['txtProduct'] : '-1';
  				print createOptionFromArray($arrProducts,$selected); ?>
  			</select>
      </li>

      <li><label for="jcprice1">JCPrice - 1<?php echo (isset($currency))? ' ('.$currency.')':'';?></label>
        <input type="text" name="jcprice[01]" id="jcprice[01]" value="40.90" />
      </li>

      <li><label for="jcprice2">JCPrice - 28<?php echo (isset($currency))? ' ('.$currency.')':'';?></label>
        <input type="text" name="jcprice[28]" id="jcprice[28]" value="27.21" />
        <input type="hidden" name="jcprice[18]" id="jcprice[18]" value="27.21" />
        <input type="hidden" name="jcprice[05]" id="jcprice[05]" value="27.21" />
      </li>

      <li><label for="jcprice3">JCPrice - 66<?php echo (isset($currency))? ' ('.$currency.')':'';?></label>
        <input type="text" name="jcprice[66]" id="jcprice[66]" value="10.54" />
      </li>

      <li><label>&nbsp;</label>
        <input type="hidden" name="submitted" id="submitted" value="true"/>
        <input class="button" type="submit" name="butReprice" id="butReprice" value="Reprice"/>
      </li>

      <li><label name="txtStatusText" id="txtStatusText" style="width:90&#37;; color:navy; text-align:center; padding-bottom:10px"><?php echo (isset($_GET['result']))? $_GET['result'] : $result;?></label></li>
    </ol>
  </fieldset>
  </form>
<div style="clear:both"></div></div>

$arrProducts = array(
    'Table01'=>'Table 01',
    'Table28'=>'Table 28',
    'Table66'=>'Table 66'
);

/* process for posted data */
if(isset($_POST['submitted'])){
  $sFilename="pricing.inc";if(file_exists($sFilename)){include_once($sFilename);}

  // MySQL Server Connection Details
  //$dbTestDBNameExtension = "MTEST";
  $dbDatabase = "MyDB"; // The master database being connected to
  $dbTable = $_POST['txtProduct']; // Table to update pricing in this Table

  // Load the database connection information
  connection.inc";if(file_exists($sFilename)){require($sFilename);}

  // UPDATE row data - was in a SELECT * from Table and iterate through...
  // New PRICE = ((WEIGHT * JCPRICE) + ($iAuxilaryCosts + (RINGQTY * 8))) * 2;
  $sMaterialID = getMetalIdFromSKU($tableRow['SKU']);
  $newPrice = round((($_POST['jcprice'][$sMaterialID] * $tableRow['WEIGHT']) + getAuxilaryCosts($tableRow['RINGQTY']))*2);
} 

The above is the meat of the code

[svidgen]

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.

update prices p left join temp_table tt on (p.some_id=tt.pre_extracted_id) set p.price=...;

[Barton68]

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.

[svidgen]

Sure. Something like this?

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

[Barton68]

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.

[svidgen]

So, would the following 5 queries do what you need?

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

[Barton68]

Thank you so much...

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

Thank you,

Barton