4

This is not a duplicate question. The previous question is nothing to do with PDO. I have two tables in mysql:

USERS
-------------------------------------
employeeid  | name | saving | salary
-------------------------------------
12           | Bob | 100     | 1000
23           | Joe | 50      | 800

USERS table
employeeid
name
saving
salary

and:

EMPLOYEE
-----------------------------------
id   |  managerid  | workerid
-----------------------------------
1    |    12       |  23

EMPLOYEE table
id
managerid FOREIGN KEY
workerid FOREIGN KEY

1- (both manager and worker are employee) in order to update the saving field for a workerid (say with +$10), field salary needs to be updated by -$10

2- input variable comes from PHP form as name , so the logical flow is:

name  > find employeeid (id) from USERS > find managerid (id2) from EMPLOYEE > find employeeid (id3) from USERS > update saving and salary

so the sql statement separately can be written as:

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;

is it possible to do these 3 statements in one (in PDO format). msql PDO format of above (with PHP):

$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);

Any help would be appreciated!

Tempo
  • 305
  • 2
  • 11
  • Your query should be something like `UPDATE USERS INNER JOIN EMPLOYEE ON USERS.employeeid = EMPLOYEE.managerid SET saving = saving + 10 , salary = salary - 10 WHERE USERS.name = 'name'` – Raymond Nijland Mar 01 '17 at 14:28
  • Thanks, your offered solution is not a PDO. I could do multiple INNER JOIN but with PDO it's bit tricky or I don't know it very well. – Tempo Mar 01 '17 at 14:54

1 Answers1

2
$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);
Naincy
  • 2,953
  • 1
  • 12
  • 21
  • Hi Naincy. Thank you very much for prompt response. I tried your code and I got this error: SQLSTATE[HY000]: General error: 1093 You can't specify target table 'USERS' for update in FROM clause – Tempo Mar 01 '17 at 14:49
  • I followed the post in http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause but still get the same above error. any suggestion? – Tempo Mar 02 '17 at 11:07
  • @Tempo Modified my query...can you check updated answer – Naincy Mar 02 '17 at 11:38
  • Thanks. it worked without giving any error. :) Thanks for your help. – Tempo Mar 03 '17 at 10:10