0

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!

  • You probably need a WHERE condition in that UPDATE statement. Anyway, what is the error / issue? – Valentin Despa Jul 1 '14 at 10:54
0

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']));
  • Thank you, moving forward here i suppose, but still no cigar. With loadRowList() goes thru the first row ok and then stops, maybe with some counting or $i, dunno throwing punches in the dark here – fresh1 Jul 1 '14 at 8:49
  • Can you var_dump() your $results, ie print the result of var_dump($results);? – fruppel Jul 1 '14 at 9:14
  • Updated, the $weight = $newresults['product_weight']; was giving the "Notice: Unidentifyed product_weight on.." so i put in the ['3'] – fresh1 Jul 1 '14 at 11:36
  • Your WHERE clause is still wrong. It should be something like ->where($db->quoteName('virtuemart_product_id') . ' = ' . $db->quote($newresults[2])); because $newresults[2] is the ID of your virtuemart product I guess – fruppel Jul 1 '14 at 11:59
  • YES! All in all that did the trick, with the right WHERE and a nice structured query. Thanks again, i must have made all the way around old SQL and then again Joomla SQLi to come back to this. – fresh1 Jul 11 '14 at 21:45
0

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();
  }
}
0

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.

  • thx, loadRowList() seems to work better, still playing with the where part.. – fresh1 Jul 1 '14 at 8:40
  • I m busy to study your exact requirements and to give you the right update query, but someone else might do this, especially if you post back any issues you might have during its implementation. – FFrewin Jul 1 '14 at 9:51
  • Sorry if im not clear from begining. I want to update the VM2 prices with new prices based on weight with some live prices for precious metals(gold/silver). Im doing this with a parser and a cron job thet gets the $pricesg with price per gram and than it should multiply it with each weight(in grams). But then again i have silver products with their respective cron job + parser + script, so all this again but with another price and with another 'tax_id'(3), thats why i keep using it, but any other reference could do, as long as they're updated apart. – fresh1 Jul 1 '14 at 11:01
  • WHERE was where the problem was, or the most part of it, the other one being my lack of syntax. Anyway appreciate the answer – fresh1 Jul 11 '14 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.