如何使用表2中的检查字段将数据插入到表1中(Codeigniter) [英] How to insert data to table1 with checking field from table2 (Codeigniter)

问题描述

这是table1的结构:

1. id_data_inserted
2. 日期
3. id_data
4. id_room

table2的结构:

1. id_data
2. 数据名称
3. 规格
4. 金额
5. 价格

我正在尝试将数据插入到table1中，但是在将数据插入到table1中之前，我需要检查id_data table1和table2.如果table2中的id_data与id_data table1相同，则将数据插入到表中，否则，数据将不会插入到表中.

I'm trying to insert data into table1, but before inserting data to table1, I need to check the id_data table1 and table2. If id_data from table2 same with id_data table1 data will be inserted to the table, and if not, data will not inserted to the table.

例如:

• 表2中的数据

• 简单表单屏幕截图

我已经有了一个简单的表单，可以将数据输入数据库. 在table2中有一个包含id_data = AA001的数据，如果我们将数据插入到table1中，且其值来自简单表单中的id_data(如果我们在表单中插入AA001)，则将其插入，但是当我们输入的id_data不是AA001，它将失败. (未插入数据库).

I already have a simple form for input data to the database. in table2 there is a data that has id_data = AA001, and if we insert data to table1 with a value of id_data from the simple form(if we insert AA001 in the form), it will be inserted, but when we input id_data which are not AA001 it will fail. (not inserted to the database).

那我该怎么办?

编辑

这是我的模特

<?php
class My_Model extends CI_Model
{

    public function __construct()
    {
       parent::__construct();
    }

    public function input_data($data, $table)
    {
       $this->db->insert($table,$data);
    }

    public function view_data($table)
    {
       return $this->db->get($table);
    }

    public function checkIdData()
    {
       $query = $this->db->query("SELECT id_data FROM table2");
       return $query;
    }
}

这是我的控制器中的input_data_inserted函数:

And this is input_data_inserted function in my Controller:

public function input_data_inserted()
{
    $id_data_inserted = $this->input->post('id_data_inserted');
    $date = $this->input->post('date');
    $id_data = $this->input->post('id_data');
    $id_room = $this->input->post('id_room');

    $checkID_Data = $this->my_model->checkIdData()->result_array();

    if($id_data == $checkID_Data)
    {
        $data = array
        (
           'id_data_inserted' => $id_data_inserted,
           'date' => $date,
           'id_data' => $id_data,
           'id_room' => $id_room
        );

        $this->my_model->input_data($data, 'table1');
        $data['table1'] = $this->my_model->viewl_data('table1')->result();
        $this->load->view('admin/data/v_data', $data);
    }
    else
    {
       echo "Failed to input!";
    }
}

我正在尝试此代码，但是如果我以表单形式插入id_data = AA001，则执行总是转到else语句.但是，如果我使用字符串更改$ checkID_Data的值(现在像这样:$ checkID_Data ="AA001")，则执行将转到if语句，并成功插入数据.

I'm trying this code but if i insert id_data = AA001 in the form, the execution always go to the else statement. But if i change the value of $checkID_Data with string ( now like this: $checkID_Data = "AA001" ) the execution will go to the if statement and data succesfully inserted.

有解决方案吗?

推荐答案

您可以编写插入选择语句.

You can write an insert select statement.

这只是一个粗略的可视化效果，因为您尚未提供代码.让我知道您是否需要澄清.

This is just a rough visualization since you haven't provided your code. Let me know if you need clarification.

INSERT INTO table1 (id_data_inserted, date, id_data, id_room) 
    SELECT
        '$id_data_inserted' as id_data_inserted,
        '$date' as date,
        '$id_data' as id_data,
        '$id_room' as id_room
    FROM table2 WHERE id_data = $id_data

更新的答案:

这是我为您的控制器提供的最新答案.但是我仍然更喜欢我的第一个答案，因为它的速度要快得多.

Here's my updated answer for your controller. But I still prefer my first answer because that's much faster.

$checkID_Data = $this->my_model->checkIdData()->result_array();
$passed = FALSE;

foreach ($checkID_Data as $v)
{
    if($id_data == $v['id_data '])
    {
        $data = array
        (
           'id_data_inserted' => $v['id_data'],
           'date' => $date,
           'id_data' => $id_data,
           'id_room' => $id_room
        );

        $this->my_model->input_data($data, 'table1');
        $data['table1'] = $this->my_model->viewl_data('table1')->result();
        $this->load->view('admin/data/v_data', $data);
        $passed = TRUE;
        break;
    }
}

if (!$passed)
{
   echo "Failed to input!";
}

这篇关于如何使用表2中的检查字段将数据插入到表1中(Codeigniter)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！