如何从另一个表中更新mysql pdo中的字段
问题描述:
这不是重复的问题。前面的问题与PDO无关。我在mysql的两个表:如何从另一个表中更新mysql pdo中的字段
USERS
-------------------------------------
employeeid | name | saving | salary
-------------------------------------
12 | Bob | 100 | 1000
23 | Joe | 50 | 800
USERS table
employeeid
name
saving
salary
和:
EMPLOYEE
-----------------------------------
id | managerid | workerid
-----------------------------------
1 | 12 | 23
EMPLOYEE table
id
managerid FOREIGN KEY
workerid FOREIGN KEY
1-(既经理和工人都是员工),以更新保存领域的workerid(以+ 10 $说)字段薪水需要由被更新 - 10 $
2-输入变量来自PHP形式name
,所以逻辑流程是:
name > find employeeid (id) from USERS > find managerid (id2) from EMPLOYEE > find employeeid (id3) from USERS > update saving and salary
所以单独的SQL语句可以写成:
id = SELECT employeeid FROM USERS WHERE name = $name; //find id of employee in USERS
id2 = SELECT managerid FROM EMPLOYEE WHERE workerid = id; //find id of worker in EMPLOYEE
UPDATE USERS SET saving = saving + 10, salary = salary -10 WHERE employeeid = id2;
是否有可能在一个做这3个报表(以PDO格式)。以上(与PHP) mSQL的PDO格式:
$sql = "SELECT employeeid FROM USERS WHERE name=:namepara";
$sttm = prepare($sql);
$sttm->execute(array(":namepara"=>$name));
$row=$sttm->fetch(PDO::FETCH_ASSOC);
$sql2 = "SELECT managerid FROM EMPOYEE WHERE workerid=:idpara";
$sttm2 = prepare($sql2);
$sttm2->execute(array(":idpara"=>$row['employeeid']));
$row2=$sttm2->fetch(PDO::FETCH_ASSOC);
$sql3 = "UPDATE USERS SET saving = saving + 10, salary = salary - 10 WHERE
employeeid=:id2para";
$sttm3 = prepare($sql3);
$sttm3->execute(array(":id2para"=>$row2['managerid']));
$row3=$sttm3->fetch(PDO::FETCH_ASSOC);
任何帮助,将不胜感激!
答
$sql = "UPDATE USERS JOIN EMPLOYEE ON USERS.employeeid = EMPLOYEE.managerid
SET Saving = saving +10, salary = salary - 10
WHERE USERS.name = :namepara";
$sttm = prepare($sql);
$sttm->execute(array(":namepara"=>$name));
$row=$sttm->fetch(PDO::FETCH_ASSOC);
您的查询应该是这样的'UPDATE USERS INNER JOIN EMPLOYEE ON USERS.employeeid = EMPLOYEE.managerid SET 储蓄=节能+ 10 ,工资=工资 - 10 WHERE USERS.name ='name'' –
谢谢,您提供的解决方案不是PDO。我可以做多个INNER JOIN,但是使用PDO它有点棘手,或者我不太了解它。 – Tempo