MySQLi update prepared statement not updating data

问题:

So I have this update statement which when I dump the $_POST variables., I get the outputs I want.

 $stmt = $dbConnectionW->prepare("UPDATE members SET 
                          fname='". mysqli_real_escape_string($dbConnectionW, $_POST['fname']) ."',
                          sname='". mysqli_real_escape_string($dbConnectionW, $_POST['sname']) ."',
                          gender='". mysqli_real_escape_string($dbConnectionW, $_POST['gender']) ."',
                          nationality='". mysqli_real_escape_string($dbConnectionW, $_POST['nation']) ."',
                          year='". mysqli_real_escape_string($dbConnectionW, $_POST['year']) ."',
                          dep1='". mysqli_real_escape_string($dbConnectionW, $_POST['dep1']) ."',
                          dep2='". mysqli_real_escape_string($dbConnectionW, $_POST['dep2']) ."',
                          f_pos='". mysqli_real_escape_string($dbConnectionW, $_POST['f_pos']) ."',
                          f_region='". mysqli_real_escape_string($dbConnectionW, $_POST['f_region']) ."',
                          exp_comp='".$comp."',
                          exp_dep='".$comp_dep."',
                          shareinfo='".$shareinfo."',
                          interest='".$interest."',
                          userconfirm = '1'
                              WHERE confirmcode = '".$passkey."';");
              $stmt->execute(); 
              if (!$stmt)
              {
              die('Error: ' . mysqli_error($dbConnectionW));
              }
              $smst-> close(); }}} mysqli_close($dbConnectionW);
      }

Basically the issue is that it won't update the database! It works with no errors, but the database does not get updated after this sql/php attempt.

Can anyone see anything wrong with my code? What are some possible causes for why my would my database not be updated? I've been starting at this for the past hour.

回答1:

You don't need to escape your variables in a prepared statement, instead you should bind your variables before executing the statement. Also the column names should be inside ` marks.

$stmt = $dbConnectionW->prepare("UPDATE members SET 
                      `fname`=?,
                      `sname`=?,
                      `gender`=?,
                      `nationality`=?,
                      `year`=?,
                      `dep1`=?,
                      `dep2`=?,
                      `f_pos`=?,
                      `f_region`=?,
                      `exp_comp`=?,
                      `exp_dep`=?,
                      `shareinfo`=?,
                      `interest`=?,
                      `userconfirm`=?
                          WHERE `confirmcode`=?");
$stmt->bind_param('ssssissssssssis',$_POST['fname'],$_POST['sname'],$_POST['gender'],...);          
$stmt->execute();

I haven't included all the bound parameters for brevity.

Hope this helps.