Update virtuemart_product_prices based on weight

Question

I want to update VM2 prices based on weight.

The thing is we have field product_weightin table #__virtuemart_products and field product_prices in table #__virtuemart_product_prices, and I want to update only the ones who have the 'tax_id'=4 in those.

I got my $priceg (that is a price / per gram of gold/silver) that I got from pulling prices of precious metals with a parser.

And the query as follows:

    if($priceg > 0 )
{
$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query
    ->select($db->quoteName(array('a.product_price', 'a.product_tax_id', 'b.virtuemart_product_id', 'b.product_weight')))
    ->from($db->quoteName('#__virtuemart_product_prices', 'a'))
    ->join('INNER', $db->quoteName('#__virtuemart_products', 'b') . ' ON (' . $db->quoteName('a.virtuemart_product_id') . ' = ' . $db->quoteName('b.virtuemart_product_id') . ')')
    ->where($db->quoteName('a.product_tax_id') . ' = 4')
    ->order($db->quoteName('a.virtuemart_product_id') . ' DESC');

$db->setQuery($query);

// Load the results as a list of objects/array or something(not sure which one fits better).
$results = $db->loadResultArray();
$updquery = $db->getQuery(true);

 foreach($results as $newresults){  //i'm trying to update each price with mine
    $weight = $results->product_weight; //here lies my problem, its ok for 1 record, doesnt go thru all
    $pricenew = "$priceg * $weight";
    $nuquery = ("UPDATE `#__virtuemart_product_prices` SET `product_price`=".$pricenew);
    $db->setQuery($nuquery);
    $updresult = $db->query();
    }
  }
}

Any help and directions much appreciated!

EDIT: preformatted var_dump($results) gives this:

array(260) {
  [0]=>
  array(4) {
    [0]=>
    string(8) "31.16577"
    [1]=>
    string(1) "4"
    [2]=>
    string(3) "286"
    [3]=>
    string(6) "7.3200"
}
.
.   //i have 259 products with ID=4
.
  [259]=>
  array(4) {
    [0]=>
    string(8) "31.16577"
    [1]=>
    string(1) "4"
    [2]=>
    string(2) "17"
    [3]=>
    string(8) "100.0000"
  }
}

As for now there are no errors, just doesnt go thru all $results and takes just the last one $newresult. The code being(updated):

    if($price > 0 )
{
$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query
    ->select($db->quoteName(array('a.product_price', 'a.product_tax_id', 'b.virtuemart_product_id', 'b.product_weight')))
    ->from($db->quoteName('#__virtuemart_product_prices', 'a'))
    ->join('INNER', $db->quoteName('#__virtuemart_products', 'b') . ' ON (' . $db->quoteName('a.virtuemart_product_id') . ' = ' . $db->quoteName('b.virtuemart_product_id') . ')')
    ->where($db->quoteName('a.product_tax_id') . ' = 4')
    ->order($db->quoteName('a.virtuemart_product_id') . ' DESC');

$db->setQuery($query);

// Load the results as a list of objects/array or something(not sure which one fits better).
$results = $db->loadRowList();
$updquery = $db->getQuery(true);

    foreach($results as $newresults){
    $weight = $newresults['3'];
    $pricenew = "$price * $weight";
    $nuquery = $db->getQuery(true)
    ->update($db->quoteName('#__virtuemart_product_prices'))
    ->set($db->quoteName('product_price') . ' = ' . $db->quote($pricenew))
    ->where($db->quoteName('virtuemart_product_id') . ' = ' . $db->quote('virtuemart_product_id'));
    $db->setQuery($nuquery);
    $updresult = $db->query();

  }
}

thx again for quick responses!

Answer 1

I think

 $weight = $results->product_weight; //here lies my problem, its ok for 1 record, doesnt go thru all

should be

$weight = $newresults['product_weight'];

$newresults because this is the value of your database row in each foreach loop and the []-brackets because you load the result as an array, not an object.

I'm not totally sure what you want to achieve but you may also be missing a WHERE-clause in your $nuquery - otherwise you're always updating all results in table #__virtuemart_product_prices, for example (not tested):

$nuquery = $db->getQuery(true)
    ->update($db->quoteName('#__virtuemart_product_prices'))
    ->set($db->quoteName('product_price') . ' = ' . $db->quote($pricenew))
    ->where($db->quoteName('virtuemart_product_id') . ' = ' . $db->quote($newresults['virtuemart_product_id']));

Answer 2

And here's the solution

I get $price from somewhere else.. then:

if($price > 0 )
{
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
    ->select($db->quoteName(array('a.product_price', 'a.product_tax_id', 'b.virtuemart_product_id', 'b.product_weight')))
    ->from($db->quoteName('#__virtuemart_product_prices', 'a'))
    ->join('INNER', $db->quoteName('#__virtuemart_products', 'b') . ' ON (' . $db->quoteName('a.virtuemart_product_id') . ' = ' . $db->quoteName('b.virtuemart_product_id') . ')')
    ->where($db->quoteName('a.product_tax_id') . ' = 4')
    ->order($db->quoteName('a.virtuemart_product_id') . ' DESC');

$db->setQuery($query);

$results = $db->loadAssocList();
foreach($results as $newresults){
    $weight = $newresults['product_weight'];
    $pricenew = $price * $weight;
$nuquery = $db->getQuery(true)
    ->update($db->quoteName('#__virtuemart_product_prices'))
    ->set($db->quoteName('product_price') . ' = ' . $db->quote($pricenew))
    ->where($db->quoteName('virtuemart_product_id') . ' = ' . $db->quote($newresults['virtuemart_product_id']));
    $db->setQuery($nuquery);
    $updresult = $db->query();
  }
}

Answer 3

To update specific rows in a MySQL table, you will need a where statement to define which records you want to update. Otherwise an update query like yours will update any/all possible records of that table.

The best way to ensure that you will update the desired rows is to use their pk_key (row id).

To be honest, I didn't study thoroughly your code and yet not that sure if you want to update 1 or more columns in the selected rows. But whatever the case, you will have to pass the rows id in a where statement within your update query.

Also, I am not sure that the loadResultArray() is still working in J2.5. You can use the loadRowList() instead.