transact in cakephp

Question

how can i do these on cakephp?

is it possible to copy data from a table to another table?.

here is my example. i have two tables, warehouse and showroom

my warehouse table has (product_id and stock_quantity) and my showroom table has (product_id(fk from warehouse), stock_transferred )..

for example in warehouse table

product_id     stock_quantity
1              10
2              20

how can I transfer product_id(1)with stock_quantity(5) in showroom table and still retain the data in warehouse table?

so after transferring data my warehouse table becomes these:

product_id     stock_quantity
1              5
2              20

and my showroom table becomes these:

product_id     stock_transferred
1              5

how can i do these in a cakephp?for example i have a text input in which the user can specify how many stocks he will transfer to showroom table.

sorry I cant explain well Im not good in english.

BEGIN TRAN 

INSERT INTO showroom
SELECT product_id, @ValueToBeReduced FROM warehouse 
WHERE product_id = 1

-- error handling

UPDATE warehouse 
SET stock_quantity = stock_quantity - @ValueToBeReduced
INNER JOIN showroom
ON warehouse.product_id = showroom.product_id
AND showroom.product_id = 1

-- error handling

COMMIT TRAN

Answer 1

Use begin() and commit()/rollback() methods of DataSource class, or transactional property of Model class. You could also provide raw SQL queries using query() method.

Answer 2

This is a basic template to do transactions in cakephp:

$this->Model->begin();
$returnQuery = $this->Model->query($query); // or $returnQuery = $this->Model->save($data);
if($returnQuery !== false){
    $this->Model->commit();
}else{
    $this->Model->rollback();
}