Multiple UPDATE table pdo php mqsql

Question

I got the idea from answer of Ifran in this question. But i am getting this #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near Any suggestion is appreciated

$stmt9 = $dbh - > prepare("UPDATE student_info AS t1 ,
                                student_address AS t2 ,
                                student_course AS t3 ,
                                student_parentinfo AS t4,
                                student_references AS t5 ,
                                student_status AS t6 
                            SET t1.status = :status, 
                                t2.status = :status,
                                t3.status = :status,
                                t4.status = :status,
                                t5.status = :status,
                                t6.status = :status 
                            WHERE t1.pin = :pin,
                                t2.pin = :pin,
                                t3.pin = :pin,
                                t4.pin = :pin,
                                t5.pin = :pin,
                                t6.pin = :pin 
                            AND t1.status = :active,
                                t2.status = :active,
                                t3.status = :active,
                                t4.status = :active,
                                t5.status = :active,
                                t6.status = :active");

$stmt9 - > execute(array(':status' => $notactive, ':pin' => $pin, ':active' => $propertystatus));

Updated the code. Miss look some variables

You can't combine `WHERE` conditions with comma, only `AND` and `OR`. — Barmar, May 21 '15 at 03:57
i didnt take that into consideration i was focusing on the tables before `WHERE` let me check that for a minute — Pekka, May 21 '15 at 04:01

Barmar · Accepted Answer · 2015-05-21T04:32:41.047

Change all the commas in the WHERE clause to AND:

$stmt9 = $dbh - > prepare("UPDATE student_info AS t1 ,
                                student_address AS t2 ,
                                student_course AS t3 ,
                                student_parentinfo AS t4,
                                student_references AS t5 ,
                                student_status AS t6 
                            SET t1.status = :status, 
                                t2.status = :status,
                                t3.status = :status,
                                t4.status = :status,
                                t5.status = :status,
                                t6.status = :status 
                            WHERE t1.pin = :pin AND
                                t2.pin = :pin AND
                                t3.pin = :pin AND
                                t4.pin = :pin AND
                                t5.pin = :pin AND
                                t6.pin = :pin
                            AND t1.status = :active AND
                                t2.status = :active AND
                                t3.status = :active AND
                                t4.status = :active AND
                                t5.status = :active AND
                                t6.status = :active");

Also, in your execute call you have :id, which isn't used in the query, but no :pin, which is needed.

Multiple UPDATE table pdo php mqsql

1 Answers1