mysql - Cakephp sql 更新 : add up old field value with value from form

Question

所以我对 MVC 世界还比较陌生。我现在正在做的是在CakePHP2.0 中重写一个php 项目。目前我正在处理订单，更具体地说是产品/库存的交付。

这是之前一个商品发货的sql代码:

$sqlSelect =("SELECT current_stock FROM stocks WHERE id IN (");
$sql = $con->prepare("UPDATE orders SET order_status=1 WHERE order_number=:orderNumber");
$sql2 = $con->prepare("UPDATE stocks SET current_stock= :stock + :delivered  WHERE id =     :stockID");

try {

$con->beginTransaction();

for($count=0; $count<=$_POST['count']; $count++) {

$idQuery [] ="?,";
$idArray []=($_POST['stock_id' .$count]);

}

$sqlSelect .= implode($idQuery);
$sqlSelect =trim($sqlSelect, ",") . ")"; // removes last comma from string, then closes  the bracket //
$stmt =$con->prepare($sqlSelect);
$stmt->execute($idArray);

while ($row=$stmt->fetch()) {

$stock = ($row['current_stock']);

$sql2->bindParam(':stock', $stock);
}

for($count=0; $count<=$_POST['count']; $count++) {

$sql2->bindParam(':delivered', $_POST['delivery_amount' .$count]);
$sql2->bindParam(':stockID', $_POST['stock_id' .$count]);
$sql2->execute();

}

$sql->bindParam(':orderNumber', $_POST['order_num'] );


$sql->execute();
$con->commit();
$con=null;

 echo "<script language=javascript>
    alert('Delivery confirmed. Your stocks have been adjusted.')
location.replace('placeorder2.php')
</script>";


} catch (Exeception $e) {

$con->rollback();
echo "Update Failed: " . $e->getMessage();

}
?>

我想知道以“CakePHP”方式执行此操作的最简单方法是什么？

我的关联如下:Product hasOne Stock，Product hasMany Order。

Answer 1

好的，我设法让它工作了。

因此，在我的订单模型中，我创建了一个方法来循环遍历从 OrdersController 检索到的 $this->data 散列并应用我的必要逻辑，并将重建的散列返回给 Controller 。

class Order extends Model {

...

     public function finalizeOrder ($dataHash= array())
     {
         $stockHash = array();

         foreach ($dataHash['Stock'] as $key => $value) {

             $stockHash['Stock'][$key]['id'] = $value['id'];
             $stockHash['Stock'][$key]['current_stock'] = $value['current_stock'] + $value['delivered_units'];
         }

         foreach ($dataHash['Order'] as $key => $value) {

             $stockHash['Order'][$key]['delivered'] = $value['delivered'];
             $stockHash['Order'][$key]['id'] = $value['id'];
         }

         return $stockHash;
     }

}

我的 Controller 看起来像这样:

class OrdersController extends AppController {
...

public function acceptDelivery() {

    $this->loadModel('Stock');

    $this->data = $this->Order->finalizeOrder($this->data);

    if ($this->Stock->saveAll($this->data['Stock'])) 
            {
        if ($this->Order->saveAll($this->data['Order'])) {
        $this->Session->setFlash('Delivery has been confirm. Your stock has been updated.');
        $this->redirect(array('action' => 'index'));
        }
    } else {
        $this->Session->setFlash('There was an error');
         $this->redirect(array('action' => 'index'));
    }

  }

}

我的表格(我省略了一些代码):

<? echo $this->Form->create('Order', array('action' => 'acceptDelivery')); ?>
<table class="gnr">
    <tr>
        <th class="product">
           Product Name
        </th>
        <th class="product" style="width: 5%">
            Qty 
        </th>
        <th class="product" style="width: 5%">
            Received
        </th>
    </tr>
    <? foreach ($order as $key => $value): ?>
    <tr>
        <td class="product">
            <? echo $value['Product']['product_name']; ?>
        </td>
                <td class="product">
            <? echo $value['Order']['order_quantity'];?>
        </td>       
        <td class="product">
            <? echo $this->Form->number('Stock.'.$key.'.delivered_units', array(
                'min' => '0', 'class' => 'val1', 'value' => 0
            )); ?>
        </td>
    </tr>

    <? echo $this->Form->hidden('Stock.'.$key.'.id', array(
                'value' => $value['Stock']['id'] )); ?>

    <? echo $this->Form->hidden('Order.'.$key.'.delivered', array(
                'value' => 1 )); ?>

    <? echo $this->Form->hidden('Order.'.$key.'.id', array(
                'value' => $value['Order']['id'] )); ?>

    <? echo $this->Form->hidden('Stock.'.$key.'.current_stock', array(
                'value' => $value['Stock']['current_stock'] )); ?> 
    <? endforeach; ?>
</table>
<? echo $this->Form->end('Confirm Order'); ?>