我有以下插入查询:
$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,说外键约束。如何引用另一个表中的键值以便能够插入该表?
这是错误消息:
无法添加或更新子行:外键约束失败
(
employees
。salaries
,CONSTRAINT salaries_ibfk_1
外键(
emp_no
)参考(employees
(emp_no
)删除级联)插入
salaries
(salary
,from_date
,to_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);