0

I am learning PHP PDO with MySQL and wanted to know how to update multiple tables with one Update query linked from a single row in Table 1. Example if I wanted to update a value in table 2 and/or 3 with one query knowing only table 1 record PK id. I thought perhaps I could use Left Join, but could not get it correct. I don't need to use Left Join, just want most optimized coding.

example code that does not result in any change. I believe it is not linking correctly.

**What I desire from code: to update values in specific rows in table 2,3,ect.. by knowing only the PK from table 1 which has the foreign keys of table 2,3.

$updt = $db->prepare("UPDATE t3 LEFT JOIN t1 ON t1.table3_fk =  
t3.table3_id SET t3.fooTable3=:VAR1, t3.barTable3=:VAR2 WHERE   
t1.table1_id = :VARID");
$updt->bindParam(':VAR1', $VAR1, PDO::PARAM_STR);
$updt->bindParam(':VAR2', $VAR2, PDO::PARAM_STR);
$updt->bindParam(':VARID', $VARID, PDO::PARAM_STR);     
$updt->execute();

Table structure:

t1              t2             t3     
table1_id(PK)   table2_id(PK)  table3_id(PK)
table2_fk       fooTable2      fooTable3
table3_fk                      barTable3

Example Record:

t1     t2            t3
22     10            14
10     someabcdata   somedefdata
14                   someefgdata

Any help would be great, thanks.

Edit1: I did try to post an image of table layout, but unfortunately I didn't have enough rep points according to this site. Thanks for the link though.

Edit2: Explained more detail

Edit3: Got rid of image for easier review

newpie
  • 77
  • 8
  • 1
    You should post the layout of your table(s) in your question as it is much easier for us to access this way, rather than going off site to see other information - something like this may help you format it correctly http://ozh.github.io/ascii-tables/ – James Nov 17 '15 at 02:14
  • 1
    Provide more details on "does not work"? What did you expect it to do? What did it do? – Sasha Pachev Nov 17 '15 at 02:14
  • Your tables are named different in your code as in your image. If `t1` is a short name the code would be `UPDATE table_long_name t1 ...`. – AbcAeffchen Nov 17 '15 at 03:11
  • I apologize for the confusion. That table was a quick example of what I had. When I tested the code the names did match with no success. For ease of review I typed out the tables in the post and got rid of image link. – newpie Nov 17 '15 at 03:49
  • To eliminate the possibility of bugs in the PHP code, try it with a manual query using MySQL command line client. Post the status report (how many rows updated) from the manual query. If the result is 0 rows updated, replace `UPDATE ` with `SELECT * FROM` and see if the `SELECT` returns any rows. If the manual UPDATE did update, debug the PHP code. – Sasha Pachev Nov 17 '15 at 18:16
  • Thank you Sasha I will try this method. – newpie Nov 26 '15 at 17:48

1 Answers1

0

Your code contains a typo. In the first statement your variable is called $updtt but the subsequent statements use $updt.

Sasha Pachev
  • 5,162
  • 3
  • 20
  • 20