php - PDO Transaction Update Error on a row using the value of another row from a different table -

updated current attempt code

hi_i’m trying pdo check row value, , update statement on row in table, using value row different table database, inside transaction, keep getting error.

i have 2 tables, table1 , bank.

table1 contains columns userid , ticketamount.

bank contains columns userid , goldbalance.

ticketamount , goldbalance ints. these 2 rows never null, when user account created, value of 0 gets entered these rows, there value in them.

userid unique column.

what want do, run pdo transaction update ticketamount adding on whatever value stored in goldbalance, i’m trying setting ticketamount + (some code fetch goldbalance amount here).

this code in full:

<?php $servername = "myservername"; $username = "myusername"; $password = "mypassword"; $dbname = "mydbname";  try {     $conn = new pdo("mysql:host=$servername;dbname=$dbname", $username, $password);     // set pdo error mode exception     $conn->setattribute(pdo::attr_errmode, pdo::errmode_exception);   // first, find out if bank.goldbalance > 0, if is, run update statements , echo "1", else echo "0":      $stmt = $conn->prepare("select 'goldbalance' 'bank' 'userid' = :id");     $stmt->bindparam(':id', $userid);     $userid = $_post['userid'];      $stmt->execute();   if (($result['goldbalance']) > 0){  // if bank.goldbalance greater 0, run transaction:     $conn->begintransaction();      // prepare update statement     $sql = 'update table1 t      left join bank b      on b.userid = t.userid      set t.ticketamount = t.ticketamount + b.goldbalance      t.userid = :id';      echo "sql=" . $sql ;      // prepare statement , bind parameters     $stmt = $conn->prepare($sql);      $stmt->bindparam(':id', $userid);     $userid = $_post['userid'];      $stmt->execute();      // 2nd statement...     $stmt = $conn->prepare("update 'bank' set 'goldbalance' = 0 'userid' = :id");     $stmt->bindparam(':id', $userid);     $userid = $_post['userid'];      $stmt->execute();      $conn->commit();      // echo "1" verify transaction completed     echo "1";  } else {     echo '0'; }         }     catch(pdoexception $e)         {         echo "error: " . $e->getmessage();         }     $conn = null;     ?> 

if bank.goldbalance = 0 there's no point in running transaction, want check first, if = 0 want echo "0", if = >0 want run transaction , echo "1" when completes.

i'm trying make transaction take value that's in bank.goldbalance , add on value that's in table1.ticketamount. then, second part of transaction should set bank.goldbalance "0".

the response i’m getting in console right above code this:

"error: sqlstate[42000]: syntax error or access violation: 1064 have error in sql syntax; check manual corresponds mysql server version right syntax use near ''bank' 'userid' = '113234523347'' @ line 1"

i can't see what's wrong particular piece of code.

any additional advice on problem great, in advance!

for debugging this, break two steps. first, generate text of sql statement string variable.

then, pass variable prepare.

between two, can echo or var_dump of string variable.

---

in case, simple php string literal/concatenation issue.

that second double quote ends string literal, , following select ... isn't recognized php.

$stmt = $conn->prepare("update 'table1'          set 'ticketamount' = 'ticketamount'+("select 'goldbalance'                                              ^                      

there's issues single quotes; mysql, should backticks around identifiers

---

since you're not including variables in string, it's static literal (which thing, in terms of sql), can use single quotes rather double quotes:

$sql = 'update `table1` t            set t.`ticketamount` = t.`ticketamount`                                  + ( select b.`goldbalance`                                       `bank` b                                      b.`userid` = :id1                                      limit 1                                   )           t.`userid` = :id2'; #echo "sql=" . $sql ; $stmt = $conn->prepare($sql); 

note there problems using same bind variable name more once in statement pdo (at least, there used be) because of way pdo converting positional notation named notation.

i added limit 1 clause subquery, because mysql throw error if return more 1 row. (if userid unique in bank table, won't make difference. if it's not unique, row want? newest, 1 greatest value goldbalance, etc. can add aggregate or order make more deterministic.)

there's issue if row isn't returned, we're going null value, , when add null null result. want handle missing row or null value zero?

if userid unique in bank table, i'd query join operation. emulate subquery, we'd need outer join, e.g.

and since none of identifiers reserved words, don't need backticks around identifiers.

  update table1 t     left     join bank b       on b.userid = t.userid      set t.ticketamount = t.ticketmamount + ifnull(b.goldbalance,0)    t.userid = :id 

if want emulate original query, we'd need remove ifnull wrapper around b.goldbalance return null, ticketamount set null.

if don't want update row if related row "missing" bank, inner join instead of outer join (i.e. remove left keyword.)

---