Table : farmers
表:农民
Id | name | gender | created
14224 | nadra | Male | 2018-02-28 08:47:47
14225 | david | Male | 2018-02-28 08:47:47
14226 | doreen| Female | 2018-02-28 08:47:47
14227 | lydia | Female | 2018-02-28 08:47:47
Table : employees
表:员工
Id | name | title | gender
1 | Amos | Manager | Male
2 | Nelson | Field Worker| Male
Table : employees_farmers
表:employees_farmers
employee_id | farmer_id
1 | 14224
1 | 14225
2 | 14226
2 | 14227
Okay so assuming we want to transfer farmers of employee Amos to employee Nelson since Amos is no long willing to supervisor those farmers... and we know the names of the farmers Amos was supervisings..so using a sql script to transfer the farmers of Amos to Nelson.....Amos is employee with id = 1 and Nelson id = 2 , and Amos farmers are nadra,david
好的,假设我们想将员工Amos的农民转移到员工Nelson,因为Amos不再愿意监督那些农民......我们知道Amos农民的名字是监督。所以使用sql脚本转移农民的Amos to Nelson ..... Amos是id = 1且Nelson id = 2的员工,Amos农民是nadra,大卫
<?php
$employee_name = “Nelson”;
$farmersArray = array(‘nadra’,’david’);
$arrLength = count($farmersArray);
//find the employee id
$employee_id = "Select id from employees where name = ‘$employee_name’";
//loop throught the array of the farmers to find the id of each farmers
for ($x = 0; $X < $arrlength; $x++){
$farmer_id = "Select id from farmers where name = ‘$farmer_name’";
}
$Query = "INSERT INTO employees_farmers (employee_id, farmer_id)
values ($employee_id, $farmer_id) ";
?>
The sql version ..
sql版本..
farmer_transfer_prep.sql
-- Clear all previously defined prepared statements (in this session)
DEALLOCATE ALL;
-- update the id of the old employee to the new employee on table employees_farmers
PREPARE updateoldemployeeidtonew (int, int) As
UPDATE
employees_farmers
SET employee_id = $1
WHERE employee_id = $2;
farmer_transfer_exec.sql
-- update the id of the old employee to the new employee on table employees_farmers -param 1 is for new and param 2 is for old
- 将旧员工的ID更新为表employee_farmers上的新员工-param 1表示新的,param 2表示旧表
EXECUTE updateoldemployeeidtonew (6 , 3);
Still writing
1 个解决方案
#1
0
To transfer farmers from one employee to another, you don't need to loop through them and insert new ones, you can update the employees_farmers table after getting the old_employee_id and new employee_id values (by selecting via name, as you did):
要将农民从一个员工转移到另一个员工,您不需要循环访问它们并插入新的员工,您可以在获取old_employee_id和新的employee_id值后更新employees_farmers表(通过选择名称,如您所做的那样):
UPDATE employees_farmers SET employee_id=$new_employee_id WHERE employee_id=$old_employee_id
#1
0
To transfer farmers from one employee to another, you don't need to loop through them and insert new ones, you can update the employees_farmers table after getting the old_employee_id and new employee_id values (by selecting via name, as you did):
要将农民从一个员工转移到另一个员工,您不需要循环访问它们并插入新的员工,您可以在获取old_employee_id和新的employee_id值后更新employees_farmers表(通过选择名称,如您所做的那样):
UPDATE employees_farmers SET employee_id=$new_employee_id WHERE employee_id=$old_employee_id