Updating Stock quantities for each product attributes

[prestashop-new]

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 August 29, 2014 by prestashop-new (see edit history)

[prestashop-new]

Please help ! No One ???

Edited September 2, 2014 by prestashop-new (see edit history)

[prestashop-new]

No one is here to guide me..

[tomerg3]

What are your stock settings (advanced / warehouse)?

 

Where did you get stuck, did you write any queries? did you get any errors?

[prestashop-new]

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 September 3, 2014 by prestashop-new (see edit history)

[tomerg3]

Are you trying to update by the main product reference, or by specific combination reference?

[prestashop-new]

I have to update with main product reference because there is no combination reference 

[tomerg3]

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')) ;

[prestashop-new]

In ps_stock_available there are 4 similar id_product and against these id_product there are 4 different quantities. Then How to map all those? I have to update all quantities for a single product.? are you getting what I am trying to say?

[tomerg3]

The above query will set the quantity to 11 for all the combinations of the product that has the reference 'demo_1'

[prestashop-new]

Pls guide

Edited September 3, 2014 by prestashop-new (see edit history)

[prestashop-new]

No I have to set different quantity for each attribute of a product

[tomerg3]

The only information you are giving is the quantity and the reference, which is per product and not per combination.

 

You won't be able to "match" a single attribute or combination without additional information

[prestashop-new]

What additional information is required...Can you please provide us?

[tomerg3]

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.

[prestashop-new]

Yes..and that I am unable to match each attribute (id_product_atribute in ps_stock_available table) for a single product. There must be some table from which we can fetch this information..

 

I am completely going mad and my mind stucked.....

[prestashop-new]

So far havent found any solution on this update query. Please guide. 

[tommo3344]

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

[musicmaster]

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 July 11, 2015 by musicmaster (see edit history)

[thrillmetoo]

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.