[SOLVED] Quantity is incorrect but database table ps_product is correct

[mcjames76]

[sOLVED] By rebuilding the ps_stock_available table in the database. Thank you vekia!

 

Please excuse me if this should be an easy fix, I am new to Prestashop. I tried searching for this issue and while I found many similar problems I was unable to find a solution for mine.

 

When I look at the products in the back office or front office all of the product quantities are incorrect. They are either showing -- or 0. When I look at the database table ps_product, the quantity field is correct. I'm not sure what has happened but I did upgrade to 1.5.5 with the 1 Click upgrade module. I'm just not sure if this problem occurred before or after the upgrade.

 

I have tried all of the cache clearing recommendations but to no avail. Any thoughts?

Edited August 29, 2013 by mcjames76 (see edit history)

[vekia]

and what about stock in ps_stock_available table, can you verify it?

[mcjames76]

Ah, so ps_stock_available looks like it has been truncated. It's only showing about 89 records and should have thousands of records I would assume.

[vekia]

if you will manage the products quantities, this table should be updated

this is correct table which handle product quantities

[mcjames76]

Is that table built using the advanced stock management feature?

[dformica]

I was going crazy with this for hours! Quantity not matching my database ps_product > quantity.

At some point I enabled advanced stock management, but disabled it the same day.  Is this what triggered my prestashop to stop using  ps_product > quantity and start storing product quantity in the ps_stock_available table?    Or is it normal functionality that quantity is stored in ps_stock_available?  I just want to be sure that all product quantities are stored there..  Please.

 

ver: 1.6.0.13

[Bill Dalton]

I do not have ASM enabled, and for me total stock is updated in table ps_stock_available.

Edited November 10, 2015 by Bill Dalton (see edit history)

[Isady]

if you will manage the products quantities, this table should be updated

this is correct table which handle product quantities

 

Hi everyone!

 

Vekia I need your help!!!

 

I have just had the same problem. I had anticipated to export ps_product and ps_product_attribute in order to import them again after creating the multishop. But I did not know about ps_stock_available.

 

I do not understand how to update this table in order for it to show the correct quantities from ps_product and ps_product_attribute.

 

Any help would be highly appreciated.

 

Thanks.   

[Bill Dalton]

ps_stock_available.id_product_attribute > ps_product_attribute.id_product_attribute

 

The above is basicly all you need to manually update.

 

If you want to match attribute, for example "Color" & "Size" you will need to also join 

 

ps_product_attribute_combination.id_product_attribute > ps_attribute_lang.id_attribute > ps_attribute.id_attribute

 

The Prestashop database is compatible with several older versions of Prestashop. As features such as Multi shop and Advanced inventory were added, additional tables were added.

[Isady]

Thank you Bill!

 

Nevertheless, what I meant was "how can do it with a SQL request?

 

I have over 5000 items (with attributes) in a running shop since already 10 years. I cannot do it manually. 

 

I have found this (which worked):

 

UPDATE `ps_stock_available` SET `ps_stock_available`.`quantity`=(SELECT `ps_product`.`quantity` FROM `ps_product` WHERE `ps_product`.`id_product`=`ps_stock_available`.`id_product`)

 

That is working well for products without attributes.

But I cannot figure out how to "complete" the quantity column. I am considering adding an "If" condition to this request. But I know nothing about SQL and I am trying everything I find on forums. I am already very excited because I managed to fix some of the quantities.

 

Now I need a request which says "update" IF quantity = 0. Anybody knows how to do that?   

Edited March 2, 2017 by Isady (see edit history)

[Bill Dalton]

Ok what I posted about was updating only the quantity of an existing item. You mention that you turned on Multi shop and now are trying to update the store products manually. I'm not going to be much help to you. I don't import products, I add each product one by one. I do use queries to update available quantities and available dates for sold out items.

 

Hopefully an SQL maven will post and help us out.

[Isady]

You are so nice to take the time to answer. I do hope someone will have a solution. In the meantime, I will update everything bulk with my PrestaPricing module which is a savior !!!

[Isady]

Well, guess what???? My eyes are red. My head is exploding but I found how to solve my problem.

 

Let me share it with you so I can also contribute to help somebody one day!

Bare with me as I tried to explain it fully so newbies like me could understand the whole process!

 

1/ My problem was:

I had a long time running shop with over 5000 items with a domain name isady.fr . I wanted to use multishop in order to add new domain names depending on the language such as isady.de, isady.es, isady.it and italy.com . I did not want the Prestashop way to deal with languages isady.fr/de . For Google, I needed to have a complete country URL.

 

When I created the multi store, it, of course, put all product quantities on 0. I couldn't find a way to avoid having to manually insert all quantities again!

 

2/ The reason for stock "0" is:

In the database, only ps_stock_available showed quantity wrong. But that was because the shop id and shop_group id had changed!

 

This website saved me: http://blog.irrelevant.com/2013/08/prestashop-switching-to-shared-stock.html

 

3/ The solution is:

Checking the code, we see that yes indeed, when changing this setting, sure enough, it deletes everything from the "stock_available" table for the affected shops.
The stock_available table has two relevant fields in it - id_shop and id_shop_group.

• When stock is held for an individual shop (not multishop), the id_shop is filled in, and the id_shop_group is zero.
• When it's shared between a group of shops (multishop), it's the other way around.

Since the only table affected when you do the switch is the ps_stock_available one, it seems logical therefore that this is the only source of data that we need to preserve.
 

So I run this SQL request:

update ps_stock_available set id_shop_group=1, id_shop = 0;

Yep!  All we need to do is update the group field, and clear the shop field!

 

Of course, if you have more shops that you don't want affected, you should include a "where" clause to restrict whose products this affects.

You need to do this before you change the shared stock setting.  Also, it will fail if you have any products with stock held individually.

 

PLEASE READ CAREFULLY the website link I joined. I only copied some of the text which applied to me specifically. But there is more info over there!

 

Also, always remember to BACK UP your Database !!!

 

4/ Another issue came up!

But of course, everything would have been too easy. So the SQL request gave me an error message #1062 whatever bla bla bla... No idea what it was. By then smoke had been running out of my nose and I could feel my little brain cells dying.

 

5/ Last solution!

• I export ps_stock_available as a CSV for Excel file (not a regular SQL).
• I modified bulk "search & replace" the column for id_shop which had not worked and put every line on "0".
• I reimported the CSV file (use ; as separator).

 

Ta daaaa!!!!

 

Can you tell how happy I am that I have found the solution myself??? :lol:

Edited March 2, 2017 by Isady (see edit history)

[Bill Dalton]

There you have it! Thank you for sharing.