Jump to content

Updating Stock quantities for each product attributes


Recommended Posts

Hi ,

 

I am using PrestaShop 1.5.5.0 . In the database there are around 2000 products and each product is having multiple option values.

 

Now I have to enable stock and update quantity for each and every products.

 

The problem is that I am unable to write update query for each product options.

 

For example :

 

Product A has 4 options : 

  1. 18oz 
  2. 19oz
  3. 20oz
  4. 21oz

and I have to update each options quantity for a single Product based on Reference number (SKU).

 

Please suggest or Guide.

 

Thanks !

Edited by prestashop-new (see edit history)
Link to comment
Share on other sites

Thank you soo much for the reply ! Stock is enabled BUT Advance Stock is disabled.

 

In the admin side Under Preference ->Products -> Products Stock 

Allow ordering of out-of-stock products SET to YES

Enable stock management SET to YES

Enable advanced-stock management SET to NO

 

I have written update query to update the things from database for a single product. But unable to select the id_product_attribute because in the ps_stock_available table there are multiple quantities for a single product id and I have to update those multiple quantities for a single product.

 

Below is my SQL Query:

 

update ps_stock_available set quantity=11 where  id_product_attribute=(SELECT id_product_attribute FROM `ps_stock_available` WHERE id_product =(SELECT id_product from ps_product where reference in ('BLT02')) AND NOT (id_product_attribute=0));

 

This query will return MULTIPLE value for id_product_attribute because each product having multiple quantity (combination/attributes).

 

Please guide so that each quantity for a single product can be updated from phpmyadmin.

Edited by prestashop-new (see edit history)
Link to comment
Share on other sites

You seem to have over-complicated your query, there is no need for id_product_attribute in this query.

 

This does not take into account multishop (it will update all shops).

 

update ps_stock_available set quantity=11 where  id_product= (SELECT id_product from ps_product where reference in ('demo_1')) ;
Link to comment
Share on other sites

A way to identify the specific combination.

You say there are 4 combinations that match product id X (we match that product ID by the reference).

 

If you need to update only 1 of those 4 combinations, you will need some way to "narrow it down"

 

It can be by a combination specific reference, or by attribute name / id that is a part of a combination.

Link to comment
Share on other sites

  • 10 months later...

Hi Tomerg3,

 

Is there any way to use the query you wrote or something similar to update all a products attribute quantities to match the default attribute quantity via Back Office > Products > Productname > Quantities.

update ps_stock_available set quantity=11 where  id_product= (SELECT id_product from ps_product where reference in ('demo_1')) ;

e.g. I need to allow the client to update the default from the back office, and all other attributes to match default quantity. The quantity updates need to be synced.

 

I've started a topic here: (https://www.prestashop.com/forums/topic/452842-sync-attribute-quantities/)

 

Which explains in more detail.

 

Kind regards,

Tom

Link to comment
Share on other sites

update ps_stock_available set quantity=11 where id_product= (SELECT id_product from ps_product where reference in ('demo_1')) ;

 

There is one catch here: For products with attributes Prestashop makes also an entry with id_product_attribute=0. In that place it will store the total for all the varieties.

 

So if you would like to do it totally correct you would do something like the following pseudocode:

SELECT id_product from ps_product where reference in ('demo_1')

Foreach id_product
{ update ps_stock_available set quantity=11 where id_product=id_product and id_product_attribute !=0  ;
  ucount = mysqli_affected_rows()
  update ps_stock_available set quantity=(11*ucount) where id_product=id_product and id_product_attribute =0  ;
}

However, I haven't checked how PS will react when you didn't do this. It might somehow correct itself.

 

Of course things become more complicated once you start using  warehouses.

Edited by musicmaster (see edit history)
Link to comment
Share on other sites

  • 1 year later...

I know this thread seems a bit old, but to me it’s hot item.

First off, best wishes to you and the ones you love.

 

I’ve been working on this problem last year and now I’ve made a fresh start make it work.

 

I have the following code, which works fine for the combination quantities.

 

UPDATE `databasename`.`psh_product_attribute` ,

 

`databasename`.`StockJunck` SET `quantity` = `STOCK AMOUNT` WHERE `psh_product_attribute`.`reference` = `StockJunck`.`ARTICLE NR`;

 

 

 

UPDATE `databasename`.`psh_stock_available` ,

 

`databasename`.`psh_product_attribute` SET `psh_stock_available`.`quantity` = `psh_product_attribute`.`quantity` WHERE `psh_stock_available`.`id_product_attribute` = `psh_product_attribute`.`id_product_attribute`

 

 

 

(‘ StockJunck’  is a table I update with a downloadable csv-file)

 

However, as you stated, the total amount doesn’t seem to add up and PS isn’t repairing itself.

 

How can I integrate your solution into this code?

I would appreciate it very much.

 

 

Link to comment
Share on other sites

×
×
  • Create New...