mysql - 使用另一表记录更新一个表

Question

我有两个表，如下所示，由budget_id连接。

finance_budget 表

+-----------+--------+------------+-----------------+---------------+
| budget_id | amount |    date    | transfer_status | budget_status |
+-----------+--------+------------+-----------------+---------------+
|         1 | 135000 | 2019-10-01 | Pending         | issue         |
|         2 |  25000 | 2019-10-02 | Pending         | issue         |
|         3 | 234000 | 2019-10-03 | Pending         | issue         |
|         4 | 175000 | 2019-10-03 | Pending         | issue         |
+-----------+--------+------------+-----------------+---------------+

finance_budget_issue 表

+----+-----------+-----------+--------+-----------------+---------------+
| id | budget_id | office_id | amount | transfer_status | budget_status |
+----+-----------+-----------+--------+-----------------+---------------+
|  1 |         1 |       100 | 135000 | Pending         | issue         |
|  2 |         2 |       101 |  12500 | Pending         | issue         |
|  3 |         2 |       102 |  12500 | Pending         | issue         |
|  4 |         3 |       100 | 100000 | Pending         | issue         |
|  5 |         3 |       105 |  75000 | Pending         | issue         |
|  6 |         3 |       104 |  59000 | Pending         | issue         |
|  7 |         4 |       102 | 125000 | Pending         | issue         |
|  8 |         4 |       110 |  50000 | Pending         | issue         |
+----+-----------+-----------+--------+-----------------+---------------+

我尝试使用我的模型将上述两个表的“transfer_status”从“Pending”更改为“Approved”

Controller

public function approveIssues($id){
        $this->checkPermissions('index', 'pendingIssues');
        if(empty($id)){
            redirect('budget/pendingIssue');
        }

        if($this->Budget_model->approveIssues($id)){
            $this->session->set_flashdata('message', 'Allocation Approved successfully ..!!');
            redirect('budget/pendingIssue');
        }else{
            $this->session->set_flashdata('error', 'Error ...!!!');
            redirect('budget/pendingIssue');
        }
    }

型号

function approveIssues($id)
    {

        $this->db->update('finance_budget_issue', array('transfer_status'=>'Approved'), array('id' => $id));
        $this->db->update('finance_budget', array('transfer_status'=>'Approved'), array('budget_id' => $id));

        if ($this->db->affected_rows()) {

            $activity=FZ_Controller::activity('approve','finance_budget_issues',$id,NULL);
            $this->db->insert('finance_user_activity',$activity);


            return true;
        }
        return false;
    }

当我按下 View 中的“已批准”按钮时，系统显示“错误”。如果我删除该行， $this->db->update('finance_budget', array('transfer_status'=>'Approved'), array('budget_id' => $id));在我的模型中，操作正在成功执行，并显示“分配已成功批准..!!”消息。

期望的输出

我还需要finance_budget表的transfer_status应该变成“Approved”。

Answer 1

您可以尝试这个模型代码

function approveIssues($id) {

    $this->db->set('finance_budget.transfer_status','Approved');
    $this->db->set('finance_budget_issue.transfer_status','Approved');

    $this->db->where('finance_budget.budget_id', $id);
    $this->db->where('finance_budget_issue.id', $id);

    $this->db->update('finance_budget JOIN finance_budget_issue ON finance_budget.budget_id = finance_budget_issue.id');    

    if ($this->db->affected_rows()) {

        $activity=FZ_Controller::activity('approve','finance_budget_issues',$id,NULL);
        $this->db->insert('finance_user_activity',$activity);


        return true;
    }

    return false;
}

我认为这对你很有帮助。