Updating Product Quantities

[rseigel]

So, it seems that the quantity field is no longer used in the product table and is instead pulled from the stock_available table. Thanks for the hour of total confusion Presta developers.

 

I'm trying to update the quantities using a script outside of Presta (dropshipping - I pull the stock availability from them daily). It was working fine until the quantity field moved.

 

Here's the SQL I'm trying to run:

 

UPDATE stock_available
SET stock_available.quantity = 1
FROM stock_available
INNER JOIN product
ON stock_available.id_product = product.id_product
WHERE product.supplier_reference = 'BKFLAGPL';

 

The real script uses variables for stock_available.quantity and product.supplier_reference. I'm just trying to get the basic SQL to work.

 

I get the following error:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM stock_available INNER JOIN product ON stock_available.id_product = product.' at line 3

 

Any ideas on where I'm going wrong?

[rseigel]

I found this in controllers/admin/AdminProductsController.php

 

StockAvailable::setQuantity($product->id, (int)Tools::getValue('id_product_attribute'), (int)Tools::getValue('value'));

 

Does anyone know if this is on the right track?

[rseigel]

Here's the answer if anyone is interested.

 

 

UPDATE

stock_available, product

SET

stock_available.quantity = 1

WHERE

stock_available.id_product = product.id_product

AND

product.supplier_reference = 'BKFLAGPL';

 

So the ultimate code is:

 

mysql_query("UPDATE stock_available, product SET stock_available.quantity = '$quantity' WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'")
  or die(mysql_error());

[yourplaything]

Maybe you can help me? i use a drop shipping system from my supplier. Am looking for a way to update product quantities easily on a daily or weekly basis? is this what you have managed to do?

[muhsinap]

I need to get available quantity of a product

[Terragg]

muhsinap,

 

Try looking at:

StockAvailable::getQuantityAvailableByProduct()

 

The classes that handle all stock-related functions are in [your_store]/classes/stock/ . Know them, love them.

 

HTH,

[muhsinap]

Thanks Terragg,

[muhsinap]

Hai,

How can i change product description size in back office

[timwelch]

Very happy to have found this old post as I am updating my quantities from an external DB and was pulling my hair out.

 

If you have combinations of products you need to update them using this...

UPDATE `stock_available` sa, `product_attribute` pa
SET sa.`quantity` = 1
WHERE sa.`id_product_attribute` = pa.`id_product_attribute`
AND pa.`reference` = 'MyProductReferenceXX'

[Totti]

 

 

you can search for a product with or without combination with sku and then update the quantity ?

[vekia]

 

Very happy to have found this old post as I am updating my quantities from an external DB and was pulling my hair out.

 

If you have combinations of products you need to update them using this...

UPDATE `stock_available` sa, `product_attribute` pa
SET sa.`quantity` = 1
WHERE sa.`id_product_attribute` = pa.`id_product_attribute`
AND pa.`reference` = 'MyProductReferenceXX'

 

thank you for sharing this sql snippet

may i know what ps verison you have ?

[timwelch]

I'm running 1.6.1.2 and this code works very well.

[Totti]

StockAvailable::setQuantity(($id_prodotto,(int)$id_prodotto_attributo,(int)$riga['Quantita'],1);

 

Fatal error update product but after not go along with the script.

[Rissinko]

...

Edited March 4, 2016 by Rissinko (see edit history)

[ietax]

Hi, can anyone find solution for external update by quantity Exec('update product')?

 

i explain the situation better:
i update via DB prestashop my stock, with automatic updates from Massive update Inventory.
At the same time, on my prestashop i have a module that connects prestashop stock with my ebay and amazon seller stock. This one is updated every time there is an "hook" updating the products qty (for example, a product is sold). Massive update Inventory doesn't use hooks or scripts but for what i know changes the value of the stock directly. So everytime Massive update Inventory does its update it changes in prestashop but not in ebay and amazon. 

Thanks in advance
Best Regards

[zklid]

Hi, could you help me figure out which query I need to use to do this:

assign quantity 0 to all products that have inactive status in the prestashop catalog.

I use prestashop 1.6.
Thanks to those who will help me

[Butch0071]

On 1/4/2016 at 9:59 AM, timwelch said:

Very happy to have found this old post as I am updating my quantities from an external DB and was pulling my hair out.

 

If you have combinations of products you need to update them using this...

UPDATE `stock_available` sa, `product_attribute` pa
SET sa.`quantity` = 1
WHERE sa.`id_product_attribute` = pa.`id_product_attribute`
AND pa.`reference` = 'MyProductReferenceXX'

So to update you need to use reference column in query?

Query:

$query = "UPDATE `"._DB_PREFIX_."stock_available` SET quantity='66' WHERE (id_product_attribute = '".$ID_product_attribute."' AND id_product = '".$ID_product."' )";
    

will not work?