On post edit table A, update value(s) in table B with a calculated value

crush123

I have a post edit server side event in which i need to set the values in a different table by calling a simple function

what i want to do is update the 'sold' field value to its current value minus the returned value, and correspondingly add this returned value to the stock level

here is what I have, but the 'Sold' and 'ItemQty' fields are not correct...

    function editSoldStatus ( $db, $id, $values ) {

    //we need to update tblstock with the returned quantities
    $returneditemid = $values['tblorderdetails']['DetailItemID'];
    $returnedqty = $values['unbound'];
    $db->push( 'tblstock', 
        array(
            'Sold' => 'Sold' - $returnedqty,
            'ItemQty' => 'ItemQty' + $returnedqty
            ),
        array(
            'ItemID' => $returneditemid
            )
    );
}

allan

'Sold' - $returnedqty

That is attempting to take a value (presumably a number) away from a string. I presume that isn't what you intend? What do you want Sold to actually be?

Allan

crush123

sorry for not being clearer

'Sold' is the name of the field I am updating

i guess the full sql statement would look like

UPDATE tblstock SET Sold = Sold - ".$returnedqty.", ItemQty = ItemQty + ".$returnedqty." WHERE ItemID = ".$returneditemid

allan

Oh I see. The problem is that this:

'Sold' - $returnedqty

is being executed in PHP space - so it should really be giving a syntax error I think. Likewise with the + statement.

There isn't actually way to have the database libraries execute a statement quite like that at the moment - at least not without a bit of messing around. I think your best best would be to simply execute a raw update statement using $db->sql( 'UPDATE ...' );.

The other option would be to get the current value, calculate the required value in PHP and then update it on the database, but that's two SQL statements rather than one.

Allan

crush123

Thanks Allan.

I went the first route with a raw update