MySQL更新批处理删除一些记录

Question

我有一个程序可以使用批处理来存储一些财务值。主表是finance_budget 和finance_budget_issue。 Finance_budget.budget_id = Finance_budget_issue.budget_id(1对多加入)

我尝试使用以下代码编辑一些记录

Controller

public function editIssues($id)
    {
        $this->checkPermissions('edit', 'allocationIssues');
        $bc = array(array('link' => 'budget/regIssues', 'page' => 'Allocation Issues'), array('link' => '#', 'page' => 'Edit'));
        $meta = array('page_title' => 'Allocation Issues', 'bc' => $bc);

        $this->form_validation->set_rules('vote_id', 'Vote', 'required');
        $this->form_validation->set_rules('budget_date', 'Budget Date', 'required');

        if ($this->form_validation->run() == true) {

            $ustock = array(
                    'vote_id' => $this->input->post('vote_id'),
                    'budget_date' => $this->input->post('budget_date'),
                    'ref_no' => $this->input->post('ref_no'),                
                    'usr' => $this->session->userdata('id_user'),
                    'added_time' => date('Y-m-d h:i:s'),
                    'budget_status' => 'issues',
                    'transfer_status' => 'Pending'                   
            );

            $count = count($this->input->post('office'));           
            $offices = $this->input->post('office');            
            $amount = $this->input->post('amount');
            $notes = $this->input->post('notes');

            for ($x = 0; $x < $count; $x++) {
                $details[$x]['budget_id'] = $id;                            
                $details[$x]['office'] = $offices[$x];
                $details[$x]['amount'] = $amount[$x];
                $details[$x]['notes'] = $notes[$x];             
            }
        }

        if ($this->form_validation->run() == true && $this->Budget_model->editIssuedAllocations($ustock, $details, $id)) {          
            $this->session->set_flashdata('message', 'Successfully Updated ..!!');
            redirect('budget/regIssues');
        } else {
            $this->data['vote'] = $this->Budget_model->getVote(array('office_id'=>$this->session->userdata('office_id')));            
            $this->data['office'] = $this->Budget_model->getOffice();
            $this->data['issues'] = $this->Budget_model->budgetById($id);           
            $this->render('budget/editIssues', $meta, $this->data);
        }
    }

型号

function editIssuedAllocations($ustock,$details,$id)
    {
        if(!empty($id)){
            $this->db->trans_start();
            $this->db->update('finance_budget',$ustock,array('budget_id'=>$id));
            if($id!=1){
                $this->db->where('budget_id', $id);
                $this->db->delete('finance_budget_issue');

                $this->db->insert_batch('finance_budget_issue', $details);
            }

            $this->db->trans_complete();
            return $this->db->trans_status();
        }
        return false;  
    }

查看

<?php
if (!empty($purchase)) {
    $details = $purchase;
    $purchase = $purchase[0];
}
?>
<script type="text/javascript">
    $(document).ready(function () {
        var i = $('#last').val();

        $(".add-new").click(function () {
            i++;
//            $('.tab_logic').append();

            $('#addr' + i).html("<td><a href='javascript:void(0);' id='add-post' class='external add-new'><i style='color: #00C853 !important;' class='fa fa-2x fa-plus-circle'></i></a></td>" +
                "<td><select name='office[]' id='office" + i + "' class='select2 form-control' required>" +
                "<option value=''></option>" +
                <?php
                if (!empty($office)) {
                foreach ($office as $row) {

                ?>

                "<option value='<?= $row->office_id ?>'><?= addslashes($row->office_name) ?></option>" +
                <?php
                }
                }

                ?>
                "</select></td>" +
                "<td><input  name='amount[]' type='number'  step='any' placeholder='Amount' class='form-control input-md' required></td>" +
                "<td><input  name='notes[]' type='text'  placeholder='Notes' class='form-control input-md'></td>" +

                "<td><a href='javascript:void(0);' class='external remove'><i style='color: #dd4b39 !important;' class='fa fa-2x fa-minus-circle' id='addIcon'></i></a></td>");
            $('#tab_logic').append('<tr id="addr' + (i + 1) + '"></tr>');
            $('#i').val(i);
//            alert($('#i').val());
            $('#item' + i).select2();


        });


        $(document).on("click", ".remove", function () {
            var count = $('#tab_logic>tbody').children().length;
            if (count > 0) {
                $(this).closest('tr').remove();
            }
        });


    });
</script>
<div class="box box-info">
    <div class="box box-info collapsed-box">
        <div class="box-header with-border">
            <h3 class="box-title">Edit Issued Allocations</h3>

            <div class="box-tools pull-right">

                <button type="button" class="btn btn-box-tool" style="font-size: 16px;" data-widget="collapse"><i
                            class="fa fa-plus"></i>
                </button>

            </div>
            <!-- /.box-tools -->
        </div>
        <!-- /.box-header -->
        <div class="box-body" style="display: block">
            <form action="<?= site_url('budget/editPurchase/' . $purchase->budget_id) ?>" method="post">

                <div class="row">

                    <div class="col-md-2">
                        <div class="form-group"><label>Budget ID</label>
                            <input type="text" name="budget_id" id="budget_id" class="form-control" disabled
                                   value="<?= $purchase->budget_id ?> ">
                        </div>
                    </div>

                    <div class="col-md-4">
                        <div class="form-group"><label>වැය ශීර්ෂය</label>
                            <select name="vote_id" id="vote_id" class="form-control select2" required>
                                <option value="<?= $purchase->vote_id ?>"><?= $purchase->vote ?></option>
                                <?php
                                if (!empty($vote)) {
                                    foreach ($vote as $row) {

                                        ?>

                                        <option value="<?= $row->vote_id ?>"><?= $row->vote ?></option>
                                        <?php
                                    }
                                }

                                ?>
                            </select>

                        </div>
                    </div>

                   <div class="col-md-4">
                        <div class="form-group"><label>යොමු / ගොණු අංකය</label>
                            <input type="text" name="ref_no" id="ref_no" class="form-control"
                                   value="<?= $purchase->ref_no ?>">
                        </div>
                    </div>

                    <div class="col-md-2">
                        <div class="form-group"><label>දිනය</label>
                            <input type="date" name="budget_date" id="budget_date" class="form-control"
                                   value="<?= $purchase->budget_date ?>">
                        </div>
                    </div>

                </div>
                <div class="col-md-12 column">
                    <table class="table table-bordered table-hover" id="tab_logic">
                        <thead>
                        <tr>
                            <th class="text-center">

                            </th>
                            <th class="text-center">
                                Office
                            </th>
                            <th class="text-center">
                               Amount
                            </th>
                            <th class="text-center">
                                Notes
                            </th>

                            <th class="text-center">


                            </th>
                        </tr>
                        </thead>
                        <tbody>
                        <?php
                        //dd($details);
                        $x = 0;
                        foreach ($details as $i => $values) {
                            // foreach ($values as $key => $value) {
                            $x = $i;
                            ?>

                            <tr id='addr0'>
                                <td>
                                    <a href="javascript:void(0);" id="add-post" class="external add-new"><i
                                                style="color: #00C853 !important;"
                                                class="fa fa-2x fa-plus-circle"></i></a>
                                </td>
                                <td style="width: 50%">
                                    <input type="hidden" name="i" id="i" value="<?= $i ?>">
                                    <select name="office[]" id="<?= "office" . $i ?>" class="form-control select2" required>
                                        <option value="<?= $details[$i]->office_id ?>"><?= $details[$i]->office_name ?></option>
                                        <?php
                                        if (!empty($office)) {
                                            foreach ($office as $row) {

                                                ?>

                                                <option value="<?= $row->office_id ?>"><?= $row->office_name ?></option>
                                                <?php
                                            }
                                        }

                                        ?>
                                    </select>
                                </td>
                                <td>
                                    <input type="number" name='amount[]' placeholder='Amount' class="form-control"
                                           value="<?= (-1) * $details[$i]->amount ?>" required/>
                                </td>
                                <td>
                                    <input type="text" name="notes[]" placeholder='Notes'
                                           value="<?= $details[$i]->notes ?>" class="form-control"/>
                                    <input type="hidden" name="table_id"
                                           value="<?= $details[$i]->notes ?>">

                                </td>

                                <td><a href='javascript:void(0)' class='external remove'><i
                                                style='color: #dd4b39 !important;'
                                                class='fa fa-2x fa-minus-circle ' id='addIcon'></i></a>
                                </td>
                            </tr>
                            <tr id='addr1'></tr>


                            <?php
                            //print "    $key => $value\n";
                            //     }
                            //print "}\n";
                        }

                        ?>
                        </tbody>
                    </table>

                </div>
                <!--table-->

                <div class="row">
                    <div class="col-md-8"></div>
                    <div class="col-md-4">
                        <input type="hidden" value="<?= $x ?>" name="last" id="last">
                        <input type="submit" value="Update" class="btn btn-primary btn-block">
                    </div>
                </div>
            </form>

            <!--end of table-->
        </div>
    </div>

</div>

期望的输出

应编辑finance_budget和finance_budget_issue表中与相同budget_id相关的一些值(例如:-budget_id =2995)

错误

编辑一些在我看来正常工作并保存的值后。当我在 View 中按下“保存”按钮而不编辑任何值时，finance_budget_issue 表中通过budget_id 与finance_budget 表相关的记录将被删除。

我不明白我出了什么问题？谁能帮我吗？

Answer 1

您可以修改模型以检查 $details 是否为空，以使 finance_budget_issue 表保持不变:

function editIssuedAllocations($ustock,$details,$id)
{
    if(!empty($id)){
        $this->db->trans_start();
        $this->db->update('finance_budget',$ustock,array('budget_id'=>$id));
        if($id!=1 && !empty($details[0])){
            $this->db->where('budget_id', $id);
            $this->db->delete('finance_budget_issue');

            $this->db->insert_batch('finance_budget_issue', $details);
        }

        $this->db->trans_complete();
        return $this->db->trans_status();
    }
    return false;  
}