(Solved - Sort Of) Mysql Problem - Need To Sync Two Tables With Triggers

[Dahl99]

******* I have managed to get around this by not sync'ing the price which I will amend/adjust manually on both databases.********

 

Hi,

 

not sure if this is in the correct forum but here goes.

 

I'm looking at upgrading my EPOS till software which has a bolt on module that connects to your on-line stores database so if you have the same products in your bricks and mortar shop as your on-line shop it keeps your inventory updated. It automatically synchronises stock data between your EPOS database and online store system that uses a MySQL backend database.

 

I've spoken to the EPOS software developer who tells me that Prestashop is not compatible due to the fact that a product's price and quantity are stored in separate database tables. However he did say that one of his customers got around this by using triggers.

 

I have no idea about databases and triggers so I could do with some help please.

 

1. Where in the database tables are the products and quantity stored?

2. What triggers would I need and where/how do I put them.

 

Thanks in advance.

 

Cheers,

 

Mark

 

Edited November 8, 2013 by Dahl99 (see edit history)

[Dahl99]

I've worked out that the product price and quantity are stored in the "ps_products" table but to actually change the quantity this is changed in the "ps_stock_available" table.

 

I need some help with trying sync the 2 tables, with I'm told, a trigger but I have no idea how to do this.

 

 

[Dahl99]

Is this the correct forum for this issue? Can someone help?

[PascalVG]

Hi Dahl,

Do I understand you well that you need to use both price and quantity from within only one table (ps_product) to make it work with EPOS?

 

If so, I think this trigger will work:

 

Make trigger in ps_stock_available with:

 

Trigger name: sync ps_product and ps_stock_avaiable quantity

table: ps_stock_available

Time: After

Event: Update

Definition:

BEGIN
UPDATE ps_product 
    SET `quantity` = 
            (SELECT SUM(`quantity`)    
                FROM ps_stock_available    
                WHERE `id_product` = NEW.id_product   
            )
    WHERE ps_product.`id_product` = NEW.`id_product`;
END 

 

Then make the same trigger for when creating new products:

Make trigger in ps_stock_available with:

 

Trigger name: sync ps_product and ps_stock_avaiable quantity

table: ps_stock_available

Time: After

Event: insert

Definition: Same as above.

 

 

 

What it does:

- it updates the value of ps_product.quantity with:

- the sum of all quantity fields that have an id_product equal to  changed row's id_product

- Do this only for the rows in ps_product that have an id_product equal to the changed row's id_product (of ps_stock_available)

 

I hereby assume (Anyone, correct me if I'm wrong) that this way, I'm calculating the sum of all combinations of a product and so get the total amount of a product.

 

 

Please backup your database before adding the triggers, and give it a try. If I misunderstood the question, please elaborate, so we can change it where needed.

 

Hope this helps,

Pascal

[PascalVG]

N.B. How to add a trigger:

 

Go to your cPanel

Start the phpMyAdmin app

go to your PrestaShop database

go to table ps_stock_available

click the trigger the on the right

click add trigger

 

That should do it...

 

pascal

[Dahl99]

Thank you Pascal for the info.

 

I have spoken to the EPOS Excel developer and he has given me this bit of info which may help clarify what is needed.

 

 

1. The stock ID used in EPOS Excel must match the stock ID used in the web store database table.
2. The web store table must hold primary key field, stock id code, current quantity value and price in the same table.

Note that the "primary key" and "stock code" may actually be the same column in your web database (some may use a separate PK column).

When EPOS Excel adjust the EPOS stock level it sends an update query to the web store database, something along the lines of...

"UPDATE ps_product SET ps_quantity = 10 WHERE ps_stock_id = XYZ123".

10 was the new quantity in this case and XYZ123 was the stock code in question.

 

He also provided this link to a help file http://www.oneapplications.com/epos-excel/help/1.4.0.0/help_12.html

 

Best wishes,

 

Dahl

[Dahl99]

I could not find where to add the trigger in my database but that's because I have found out from my host they don't allow triggers unless I upgrade to a VPS server. So are any other ways to resolve this?

[psyho997]

This is a very old post but I am dealing with the same issue.

Our stock keeping software integration is not really working . As Dahl99 stated "product price and quantity are stored in the "ps_products" table but to actually change the quantity this is changed in the "ps_stock_available" table."

 

I do need a trigger that will update ps_products after my stock keeping software will update the ps_stock_avaible table.

 

Is there anyone that could help me with that ?

Regards

Rob