Update quantity in SQL

[Chachits]

Hy all,

 

News in the Prestashop community, I am meeting a problem wich a specific development for my new online shop.

I would develop a function to update quantity stock of my website with my shop ERP.

When I execute directly the request in the database the quantity are not change.

I have read several posts over the net and I have read the solution is use the class StockAvailable with the updateQuantity function.

 

But I didn't make run this function.

 

Can someone help me please?

 

Regards

 

PS : I use the 1.5.6.2 version of Prestashop.

[bellini13]

When I execute directly the request in the database the quantity are not change.

 

Can you show us what SQL command you used to update the quantity?

[Chachits]

Can you show us what SQL command you used to update the quantity?

 

To update the X_product_attribute table I use UPDATE X_product_attribute SET quantity = quantity + [number] WHERE ean13 = "myProductEAN";

And I update the X_stock_available table with UPDATE X_stock_available SET quantity = quantity + [number] WHERE id_product = myProductID;

 

The values are OK in the database but the modification doesn't appear in the shop.

Edited July 16, 2014 by Chachits (see edit history)

[bellini13]

have you updated the x_product_attribute_shop table?  With ps v1.5.6.2 stock is most likely taken from the the 'shop' table

[Chachits]

have you updated the x_product_attribute_shop table?  With ps v1.5.6.2 stock is most likely taken from the the 'shop' table

 

The X_product_attribute_shop doesn't contain field for quantity.

 

This is the structure of the table :

 

id_product_attribute id_shop wholesale_price price ecotax weight unit_price_impact default_on minimal_quantity available_dat

 

Anyone have an idea to use this method, it seem the solution is here but I don't make run this function :

 

<?php
     require_once('../classes/stock/StockAvailable.php');
     StockAvailable::setQuantity((int)16, 0, (int)50);
?>

[bellini13]

yup my bad, i thought it had quantity

 

to use StockAvailable::setQuantity, you would pass the following

id_product: the product id you wish to update

id_product_attribute: zero if there are no combinations, other pass the combination attribute id

quantity: The total available stock

id_shop: null if you do not use multi-store, otherwise the shop id

[Chachits]

yup my bad, i thought it had quantity

 

to use StockAvailable::setQuantity, you would pass the following

id_product: the product id you wish to update

id_product_attribute: zero if there are no combinations, other pass the combination attribute id

quantity: The total available stock

id_shop: null if you do not use multi-store, otherwise the shop id

 

Yes I know this parameters but when I'm create a test.php in a folder placed in the root of my directory I've got an error

 

<?php
     require_once('../classes/stock/StockAvailable.php');
     StockAvailable::setQuantity((int)16, 0, (int)50);
?>

The error is :

 

Fatal error: Class 'ObjectModel' not found in /homepages/16/d395607993/htdocs/temp/classes/stock/StockAvailable.php on line 33

 

And when I'm require_once('../classes/ObjectModel.php'); I've got an error too.

 

Any one to help me ?

[Prestashopconector]

Hi,

 

To update your stock using sql, just run 2 queries updating the value in the column quantity from the ps_product table and quantity column from the ps_stock_available table with a query like this:

 

update ps_product set quantity = YOUR_STOCK_VALUE

where id_product = X (you mention the ean13 field, so in that case:

where ean13 = XXXXXXXXXXXXX

;

update ps_stock_available set quantity = YOUR_STOCK_VALUE

where id_product = X

 

As you are using the ean13 value to update this tables, i figure up that you don't know your product_id taht is required to update the ps_stock_available table, son in that case join this table in the update statement with ps_product in this way:

 

update ps_stock_available join ps_product 

on ps_stock_available.id_product = ps_product.id_product

set ps_stock_available.quantity = YOUR_STOCK_VALUE

where ps_product.ean13 = XXXXXXXXXXXXX

 

Hope it helps you ! :-)

[Chachits]

Hi,

 

To update your stock using sql, just run 2 queries updating the value in the column quantity from the ps_product table and quantity column from the ps_stock_available table with a query like this:

 

update ps_product set quantity = YOUR_STOCK_VALUE

where id_product = X (you mention the ean13 field, so in that case:

where ean13 = XXXXXXXXXXXXX

;

update ps_stock_available set quantity = YOUR_STOCK_VALUE

where id_product = X

 

As you are using the ean13 value to update this tables, i figure up that you don't know your product_id taht is required to update the ps_stock_available table, son in that case join this table in the update statement with ps_product in this way:

 

update ps_stock_available join ps_product 

on ps_stock_available.id_product = ps_product.id_product

set ps_stock_available.quantity = YOUR_STOCK_VALUE

where ps_product.ean13 = XXXXXXXXXXXXX

 

Hope it helps you ! :-)

 

Hi Prestashopconnector,

 

Thanks for your answer.

Your query it's great and run but the problem isn't solve.

 

For example I have a product which ID 1. This product have 3 colors different with 3 EAN distinct. So in the product_attribute table i have 3 ID 11, 12, 13 which represent the 3 product different.

 

This is this quantity in product_attribute table that I can't modify.

 

Have you any ideas? I have read i must use the fonction that I speak in my previous post but it is not possible to run them.

 

If anyone have an idea or a solution

[Prestashopconector]

Hi Chachits,

 

 

Can you check what is the value on the advanced_stock_management column from the ps_product table for those products ? :-)
in case this is enabled ps_product_atribute should be the responsible for showing the quantities....

 

For double check this, also you need to jion with the ps_product_attribute_combination table.

product id from ps_product joined with ps_product_attribute and them joined with the  ps_product_attribute_combination.

 

Check this and if it make sence i will help you with the query to jion the 3 tables :-)

[Chachits]

Hi Chachits,

 

 

Can you check what is the value on the advanced_stock_management column from the ps_product table for those products ? :-)

in case this is enabled ps_product_atribute should be the responsible for showing the quantities....

 

For double check this, also you need to jion with the ps_product_attribute_combination table.

product id from ps_product joined with ps_product_attribute and them joined with the  ps_product_attribute_combination.

 

Check this and if it make sence i will help you with the query to jion the 3 tables :-)

 

Take my exemple :

ps_product table :

The product A have 16 for ID and have 0 for advanced_stock_management.

 

ps_product_attribute table.

The product A have 6 different combination . A1, A2, ..., A6. The id_product_attribute of item is 12, 13, 14, 15, 16, 17.

 

When I update the quantity field in ps_product_attribute table the quantity never update in my website.

 

I hope find a solution with you

[bellini13]

Yes I know this parameters but when I'm create a test.php in a folder placed in the root of my directory I've got an error

 

<?php
     require_once('../classes/stock/StockAvailable.php');
     StockAvailable::setQuantity((int)16, 0, (int)50);
?>

The error is :

 

Fatal error: Class 'ObjectModel' not found in /homepages/16/d395607993/htdocs/temp/classes/stock/StockAvailable.php on line 33

 

And when I'm require_once('../classes/ObjectModel.php'); I've got an error too.

 

Any one to help me ?

 

 

just add the following to your test.php

require(dirname(__FILE__).'/config/config.inc.php');

 

[Prestashopconector]

Have you also tried deleting the cache ?? 

[vekia]

im affraid that it's not related to caching

caching its a case of template files, not php core

and in this case we dealing with php / sql queries without caching functions

[Chachits]

 

just add the following to your test.php

require(dirname(__FILE__).'/config/config.inc.php');

 

Hi all,

 

Thank for all your answers...

 

This line solve on problem, the class ObjectModel was found now.

 

But at the execution of the script the function doesn't work, this is my code for test :

<?php
	require_once('../config/config.inc.php');
	require_once('../classes/stock/StockAvailable.php');
	echo'OK1';
	StockAvailable::updateQuantity((int)16,(int)51,(int)50,null);
	//StockAvailable::updateQuantity(16,51,50,null);
	echo'OK2';
	StockAvailable::update();
	echo'OK3';
?> 

The script doesn't pass the function StockAvailable::updateQuantity(), "OK2" is never executed... I have tried this anyone show an error in the script ?

[bellini13]

this line is not necessary, remove it

require_once('../classes/stock/StockAvailable.php');

I would suggest adding a try catch block around the function call and see if an Exception is being thrown

try 
{
    StockAvailable::updateQuantity((int)16,(int)51,(int)50,null);
}
catch (Exception $e)
{
    echo $e->getMessage();
}

[Chachits]

 

this line is not necessary, remove it

require_once('../classes/stock/StockAvailable.php');

I would suggest adding a try catch block around the function call and see if an Exception is being thrown

try 
{
    StockAvailable::updateQuantity((int)16,(int)51,(int)50,null);
}
catch (Exception $e)
{
    echo $e->getMessage();
}

 

Hi bellini13,

 

How are you? Thanks for this code I made all tests I need yesterday and all is done.

 

Thanks for all.

 

I have just one questions about 

require(dirname(__FILE__).'/config/config.inc.php');

This code include all classes of Prestashop ?

[bellini13]

correct, the config file loads all other files, including what is necessary to connect to the database and autoload all Prestashop classes

[Chachits]

Thanks for all bellini13, I mark solved this topic.

See you soon on the board.

[Totti]

i use StockAvailable::setQuantity, update the quantity is ok but i see the error Fatal error

[Totti]

The first time it works if I raise him from fatal error and script updates only the first product found