CodeIgniter表单验证通过但数据无法插入数据库问题

我正在使用CodeIgniter开发一款产品库存管理系统，已创建用于收集品牌名称、通用名称、类别及定价详情等产品信息的表单。目前遇到的问题是，表单验证已成功，但数据似乎并未插入到数据库中。

控制器 InventoryController.php #

以下是处理表单提交、验证并将数据传递至模型的addProduct()函数：

public function addProduct() {
    $this->load->model('Inventory_model');
    $data = $this->input->post();

    $this->load->library('form_validation');
    $this->form_validation->set_rules('brand_name', 'Brand Name', 'required');
    $this->form_validation->set_rules('generic_name', 'Generic Name', 'required');
    $this->form_validation->set_rules('category', 'Category', 'required');

    if ($this->form_validation->run() == FALSE) {
        // validation failed
        $error = array(
            "success" => false,
            "message" => "Validation failed"
        );
        echo json_encode($error);
    } else {
        // validation passed
        $data = array(
            'brand_name' => $this->security->xss_clean($this->input->post('brand_name')),
            'generic_name' => $this->security->xss_clean($this->input->post('generic_name')),
            'category' => $this->security->xss_clean($this->input->post('category'))
        );

        $result = $this->Inventory_model->insertProduct($data);
        echo json_encode($result);
    }
}

模型 InventoryModel.php #

以下是模型中应将数据插入products表的insertProduct()函数：

public function insertProduct($data) {
    $this->db->trans_start();

    $this->db->insert('products', array(
        'brand_name' => $this->security->xss_clean($data['brand_name']),
        'generic_name' => $this->security->xss_clean($data['generic_name']),
        'category' => $this->security->xss_clean($data['category'])
    ));

    $this->db->trans_complete();

    if ($this->db->trans_status() === FALSE) {
        $error = array(
            "success" => false,
            "message" => "Error adding product"
        );
        return $error;
    } else {
        try {
            $query = "SELECT *, selling_price * quantity as total FROM products ORDER BY product_id DESC";
            $result = $this->db->query($query, $product_id);
            $row = $result->row_array();

            $total = $row['total'];
            $badge_class = $row['qty_sold'] < 10 ? 'badge-danger' : 'badge-success';

            $response = array(
                "success" => true,
                "data" => array(
                    "brand_name" => $row['brand_name'],
                    "generic_name" => $row['generic_name'],
                    "category" => $row['category']
                )
            );
            return $response;
        } catch (Exception $e) {
            $error = array(
                "success" => false,
                "message" => "Error retrieving product data"
            );
            return $error;
        }
    }
}

问题原因与修复方案 #

1. 未定义变量导致查询报错
   模型中执行查询时使用了未定义的$product_id变量，这会触发异常捕获，返回"Error retrieving product data"的错误响应，让你误以为数据未插入——实际上数据大概率已经成功写入数据库，只是后续查询环节出错。

2. 冗余的XSS过滤
   控制器中已经对POST数据做了xss_clean处理，模型里再次过滤属于重复操作，可直接移除。

3. 低效的查询方式
   不需要通过全表排序获取最新插入的数据，用CodeIgniter内置的$this->db->insert_id()获取刚插入的ID，再精准查询单条数据更高效。

修复后的模型代码 #

public function insertProduct($data) {
    $this->db->trans_start();

    $this->db->insert('products', $data);
    $insert_id = $this->db->insert_id(); // 获取刚插入的产品ID

    $this->db->trans_complete();

    if ($this->db->trans_status() === FALSE) {
        return array(
            "success" => false,
            "message" => "Error adding product"
        );
    } else {
        try {
            // 根据插入ID精准查询
            $query = $this->db->get_where('products', array('product_id' => $insert_id));
            $row = $query->row_array();

            if (!$row) {
                throw new Exception("Product not found after insertion");
            }

            $total = $row['selling_price'] * $row['quantity'];
            $badge_class = $row['qty_sold'] < 10 ? 'badge-danger' : 'badge-success';

            return array(
                "success" => true,
                "data" => array(
                    "brand_name" => $row['brand_name'],
                    "generic_name" => $row['generic_name'],
                    "category" => $row['category']
                )
            );
        } catch (Exception $e) {
            return array(
                "success" => false,
                "message" => "Error retrieving product data: " . $e->getMessage()
            );
        }
    }
}

优化后的控制器代码（可选） #

public function addProduct() {
    $this->load->model('Inventory_model');
    $this->load->library('form_validation');

    $this->form_validation->set_rules('brand_name', 'Brand Name', 'required');
    $this->form_validation->set_rules('generic_name', 'Generic Name', 'required');
    $this->form_validation->set_rules('category', 'Category', 'required');

    if ($this->form_validation->run() == FALSE) {
        echo json_encode(array(
            "success" => false,
            "message" => "Validation failed: " . validation_errors()
        ));
        return;
    }

    // 利用input->post第二个参数自动过滤XSS（需确保全局XSS过滤开启）
    $data = array(
        'brand_name' => $this->input->post('brand_name', TRUE),
        'generic_name' => $this->input->post('generic_name', TRUE),
        'category' => $this->input->post('category', TRUE)
    );

    $result = $this->Inventory_model->insertProduct($data);
    echo json_encode($result);
}

内容的提问来源于stack exchange，提问作者Denver Leetrich