我有以下插入查询:

$salarystuff = array('salary' => $salary, 'from_date' => $salary_from_date, 'to_date' => $salary_to_date);

        $this->db->insert('salaries', $salarystuff);


薪水表具有以下列:emp_no |工资| from_date | to_date(我正在使用来自dev.mysql.com的数据库。

但这给了我一个错误1452,说外键约束。如何引用另一个表中的键值以便能够插入该表?

这是错误消息:


  无法添加或更新子行:外键约束失败
  (employeessalaries,CONSTRAINT salaries_ibfk_1外键
  (emp_no)参考(employeesemp_no)删除级联)
  
  插入salariessalaryfrom_dateto_date)值
  (“ 1000000”,“ 2012-12-27”,“ 2013-01-16”)


谢谢

编辑:我正在尝试以下

首先,我使用此功能在employees表中创建记录:

function add_emp($firstname,$lastname,$gender,$date_of_birth,$jobtitle,$dept,$hiredate)
    {

        $data = array(  'first_name' => $firstname,
                        'last_name' => $lastname,
                        'gender' => $gender,
                        'birth_date' => $date_of_birth,
                        'hire_date' => $hiredate);

        $this->db->trans_start();
        $this->db->insert('employees', $data);
        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE)
        {
            $msg = "Adding the new employee failed.";
            return $msg;
        }
        else
        {
            $msg = "Successfully Added Employee.";
            return $msg;
        }
    }


然后在另一个函数中添加薪水:

function add_salary($firstname,$lastname,$gender,$date_of_birth,$jobtitle,$dept,$hiredate,$salary, $salary_from_date,$salary_to_date)
    {
        $this->db->select('emp_no');
        $this->db->from('employees');
        $this->db->where('first_name', $firstname);
        $this->db->where('last_name', $lastname);
        $this->db->where('gender', $gender);
        $this->db->where('hire_date', $hiredate);
        $this->db->where('birth_date', $date_of_birth);
        $this->db->limit(1);
        $selected_employee = $this->db->get();

        $salarystuff = array('emp_no' => $selected_employee, 'salary' => $salary, 'from_date' => $salary_from_date, 'to_date' => $salary_to_date);
        $this->db->insert('salaries', $salarystuff);
    }


两者都在模型中。然后在控制器中,我调用了两个函数:

$ employee_insert = $ this-> user-> add_emp($ firstname,$ lastname,$ gender,$ date_of_birth,

$jobtitle,$dept, $hiredate);

        $salarythings = $this->user->add_salary($firstname,$lastname,$gender,$date_of_birth,$jobtitle,$dept,$hiredate,$salary, $salary_from_date,$salary_to_date);


但是我收到一个错误,因为emp_no字段中没有值...我认为可能是这样:

$selected_employee = $this->db->get();

            $salarystuff = array('emp_no' => $selected_employee,


我的错误消息是:


  CI_DB_mysql_result类的对象无法转换为字符串

最佳答案

问题是您试图插入薪水表,但未指定薪水记录所针对的员工。您需要为emp_no表中的employees列提供一个值。

预计到达时间:

很高兴您发布了更多代码-您应该做的就是修改您的add_emp函数,以便它返回创建的员工的ID(我在这里假设emp_no是并且数据库中的auto-increment列)。像这样:

function add_emp($firstname,$lastname,$gender,$date_of_birth,$jobtitle,$dept,$hiredate)
{

    $data = array(  'first_name' => $firstname,
                    'last_name' => $lastname,
                    'gender' => $gender,
                    'birth_date' => $date_of_birth,
                    'hire_date' => $hiredate);

    $this->db->trans_start();
    $this->db->insert('employees', $data);
    $this->db->trans_complete();

    if ($this->db->trans_status() === FALSE)
    {
        //$msg = "Adding the new employee failed.";
        //return $msg;
        return -1; // indicates failure
    }
    else
    {
        //$msg = "Successfully Added Employee.";
        //return $msg;
        return $this->db->insert_id();
    }
}


请注意,调用add_emp的代码现在需要稍作更改才能将返回值视为整数而不是消息,但是现在您有了员工的ID,可以在add_salary函数中使用它,而不是必须传递所有其他数据,例如:

function add_salary($emp_no,$salary, $salary_from_date,$salary_to_date)
{
    $salarystuff = array('emp_no' => $emp_no, 'salary' => $salary, 'from_date' => $salary_from_date, 'to_date' => $salary_to_date);
    $this->db->insert('salaries', $salarystuff);
}


因此,添加员工及其薪水的代码将如下所示:

$emp_no = add_emp([Your parameters]);
if ( $emp_no > 0 ) {
   add_salary($emp_no, [other parameters]);
} else {
   // Show some error message that the employee creation failed.
}


再次编辑:

如果您忽略以上我的建议,那么为什么出现错误的简单答案是因为$selected_employee是结果集而不是实际的员工编号。您将需要更改该代码,如下所示:

$selected_employee = $this->db->get();
$result = $selected_employee->result();
$emp_no = $result[0]->emp_no;

    $salarystuff = array('emp_no' => $emp_no, 'salary' => $salary, 'from_date' => $salary_from_date, 'to_date' => $salary_to_date);
    $this->db->insert('salaries', $salarystuff);

08-18 10:47
查看更多