Jump to content

Update quantity in SQL


Chachits

Recommended Posts

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.


Link to comment
Share on other sites

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 by Chachits (see edit history)
Link to comment
Share on other sites

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 :

 

 

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);
?>
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

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 ! :-)

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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 :-)

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

 

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 ?

Link to comment
Share on other sites

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();
}
  • Like 1
Link to comment
Share on other sites

 

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 ?

Link to comment
Share on other sites

  • 1 year later...
  • 4 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...