Update product with combination quantity

[cikcak]

Hey,

 

im trying to update products with combinations quantity.

 

My database: http://prntscr.com/5fwtxq

mysql_query('UPDATE `'._DB_PREFIX_.'product_attribute` SET `quantity` = \''.$count.'\' WHERE `ean13` = \''.$code.'\' ');

mysql_query('UPDATE `'._DB_PREFIX_.'product` SET `quantity` = \''.$count.'\' WHERE `id_product` = \''.$row->id_product.'\' ');

$id_product_attribute = $row->id_product_attribute;
mysql_query('UPDATE `'._DB_PREFIX_.'stock_available` SET `quantity` = \''.$count.'\' WHERE `id_product_attribute` = \''.$id_product_attribute.'\' ');

These lines help me to update everything, but I found that in product list quantity is wrong. So I open stock_available table and see that product has one more row with total quantity. So I must to update also this.

-----------------------------------------------------------------------------------------------------------------------

How to write correct sentence to update SUM of quantity of product with attributes?

$sql = ('UPDATE `'._DB_PREFIX_.'stock_available` SET `quantity` = (SELECT SUM(quantity) FROM (SELECT quantity FROM ps_stock_available) WHERE `id_product_attribute` = 0 AND `id_product` = \''.$row->id_product.'\') ');
echo $sql;
mysql_query($sql);

Look at picture: id product -> 7 . Two quantities of product attribute and final one more with total quantity. But I can`t update it correct.

 

Thanks for help.

[Toeareg]

You need the SUM of all rows where `id_product_attribute` > 0 (not = 0)

[cikcak]

You need the SUM of all rows where `id_product_attribute` > 0 (not = 0)

Yap, I changed this mistake to != 0. But still dont update...

[Toeareg]

Look at the parentheses in your query:

$sql = ('UPD.....duct.'\') ');

[Toeareg]

Just looked at it again and i think you overdid it a bit in the nested selects.

Didn't test it, but I think this should do:

$sql = 'UPDATE `'._DB_PREFIX_.'stock_available`
        SET    `quantity` =
        (
                SELECT SUM(`quantity`)
                FROM   `'._DB_PREFIX_.'stock_available`
                WHERE  `id_product_attribute` > 0
                AND    `id_product` = \''.$row->id_product.'\'
         );';

Edited December 12, 2014 by Toeareg (see edit history)

[cikcak]

 

Just looked at it again and i think you overdid it a bit in the nested selects.

Didn't test it, but I think this should do:

$sql = 'UPDATE `'._DB_PREFIX_.'stock_available`
        SET    `quantity` =
        (
                SELECT SUM(`quantity`)
                FROM   `'._DB_PREFIX_.'stock_available`
                WHERE  `id_product_attribute` > 0
                AND    `id_product` = \''.$row->id_product.'\'
         );';

 

Tried your code - still nothing. Tried this:

$sql = 'UPDATE `'._DB_PREFIX_.'stock_available`
        SET    `quantity` =
        (
                SELECT SUM(`quantity`)
                FROM   `'._DB_PREFIX_.'stock_available`
                WHERE  `id_product_attribute` > 0
                AND    `id_product` = \''.$row->id_product.'\'
         ); WHERE `id_product` = \''.$row->id_product.'\. AND `id_product_attribute` = 0';

It gives me:

UPDATE `ps_stock_available` SET `quantity` = ( SELECT SUM(`quantity`) FROM `ps_stock_available` WHERE `id_product_attribute` > 0 AND `id_product` = '7' ); WHERE `id_product` = '7\. AND `id_product_attribute` = 0

To my mind I need add additional WHERE to update only products with same id and id attribute = 0. Uch... Help!

Edited December 12, 2014 by cikcak (see edit history)

[cikcak]

mysql_query('update '._DB_PREFIX_.'stock_available set quantity = 9999 WHERE id_product = '.$row->id_product.' AND id_product_attribute = 0');
 

Tried this so it seems its okey, find correct places and add 9999 qnt.. If I try with COUNT  - 0 effect...

[Toeareg]

You are correct about the additional WHERE clause, but remove the semicolon just before the WHERE:

$sql = 'UPDATE `'._DB_PREFIX_.'stock_available`
        SET    `quantity` =
        (
                SELECT SUM(`quantity`)
                FROM   `'._DB_PREFIX_.'stock_available`
                WHERE  `id_product_attribute` > 0
                AND    `id_product` = \''.$row->id_product.'\'
        ) 
        WHERE `id_product` = \''.$row->id_product.'\. 
        AND `id_product_attribute` = 0 ;';

[cikcak]

Okey, I removed but still nothing. WHY???

 

All my code to update.

mysql_query('UPDATE `'._DB_PREFIX_.'product_attribute` SET `quantity` = \''.$count.'\' WHERE `ean13` = \''.$code.'\' ');
 $id_product_attribute = $row->id_product_attribute;
mysql_query('UPDATE `'._DB_PREFIX_.'stock_available` SET `quantity` = \''.$count.'\' WHERE `id_product_attribute` = \''.$id_product_attribute.'\' ');

mysql_query('update '._DB_PREFIX_.'product set quantity = '.$count.' WHERE id_product = '.$row->id_product.'');

$sql = 'UPDATE `'._DB_PREFIX_.'stock_available`
        SET    `quantity` =
        (
                SELECT SUM(`quantity`)
                FROM   `'._DB_PREFIX_.'stock_available`
                WHERE  `id_product_attribute` > 0
                AND    `id_product` = \''.$row->id_product.'\'
        )
        WHERE `id_product` = \''.$row->id_product.'\.
        AND `id_product_attribute` = 0 ;';
        mysql_query($sql);

[Toeareg]

I noticed another typo:

WHERE `id_product` = \''.$row->id_product.'\.

should be

WHERE `id_product` = \''.$row->id_product.'\'

$sql = 'UPDATE `'._DB_PREFIX_.'stock_available`
        SET    `quantity` =
        (
                SELECT SUM(`quantity`)
                FROM   `'._DB_PREFIX_.'stock_available`
                WHERE  `id_product_attribute` > 0
                AND    `id_product` = \''.$row->id_product.'\'
        )
        WHERE `id_product` = \''.$row->id_product.'\'
        AND `id_product_attribute` = 0 ;';

[cikcak]

Toeareg , thanks for help. Tried your variant, but stilll nothing.

 

Finally, it helps me to solve the problem.

mysql_query('UPDATE ps_stock_available AS s, (SELECT SUM(Quantity) AS suma FROM ps_stock_available WHERE id_product = '.$row->id_product.' AND id_product_attribute > 0) AS p
SET s.quantity= p.suma , id_shop = 0 , id_shop_group = 1 WHERE id_product = '.$row->id_product.' AND id_product_attribute = 0');