Can you bind parameters before preparing the SQL? PHP PDO-CodePudding

My normal sequence of interacting with my database is something like this:

$sql = "select count(*) from users where username = :newusername";
$statement = $databaseConnection->prepare($sql);
$statement->bindParam(":newusername", $newUsername, PDO::PARAM_STR);
$statement->execute();

...prepare is called before bindParam. Can I prepare the SQL after my bindParams or wouldn't that work? This would be handy for this logic:

$sql = "update users set suspended = :newsuspensionsetting";
$statement->bindParam(":newsuspensionsetting", $newSuspensionSetting, PDO::PARAM_INT);

if ($newUsernameHasBeenSet) {
    $sql .= ", username = :newusername";
    $statement->bindParam(":newusername", $newUsername, PDO::PARAM_STR);
}

if ($newPasswordHasBeenSet) {
    $newPassword = password_hash($newPassword, PASSWORD_DEFAULT);
    $sql .= ", password = :newpassword";
    $statement->bindParam(":newpassword", $newPassword, PDO::PARAM_STR);
}

$sql .= "where permanent_id = :permanentidofusertochange";
$statement->bindParam(":permanentidofusertochange", $permanentIDOfUserToChange, PDO::PARAM_STR);

$statement = $databaseConnection->prepare($sql);
$statement->execute();

thanks

CodePudding user response：

You sould create an associative array and add values in it, if the condition is satisfied : you should have something like this inside of your if:

$arr = ["keytobind"=>"value"]

When all of your "ifs" are passed, then, you use the prepare statement with your prepare method.

Finally, use a : foreach($arr as $key=>$value){$statement->bindParam(":".$key,$value); }