CODEWITHSUNDEEP
phpmysqli
Diogo Carvalho
Diogo Carvalho

Reputation: 73

PHP SQL bind_param with STR_TO_Date error

I'm doing an update into the database but its not working using bind param but if I do it in a sql file and replace the ? to the actual values it works.

$sqlUpdate3 = $connection->prepare("UPDATE Users SET DateOfBirth = DATE(STR_TO_DATE(' ?, ?, ? ', '%d, %m, %Y')) WHERE UserName=?");
$sqlUpdate3->bind_param("ssss", $_POST["daySelect"], $_POST["monthSelect"], $_POST["yearSelect"], $_SESSION["username"]);
$sqlUpdate3->execute();

Upvotes: 1

Views: 186

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562240

Question marks inside a string literal in SQL are not parameter placeholders, they're just literal question mark characters.

To make them parameters, you must put the question marks outside string delimiters:

$sqlUpdate3 = $connection->prepare("
  UPDATE Users SET DateOfBirth = 
  DATE(STR_TO_DATE(CONCAT(?, ', ', ?, ', ', ?), '%d, %m, %Y')) 
  WHERE UserName=?");
$sqlUpdate3->bind_param("ssss", $_POST["daySelect"], 
  $_POST["monthSelect"], $_POST["yearSelect"], $_SESSION["username"]);

However, I recommend to format the date in a MySQL-compatible YYYY-MM-DD format in PHP, then bind that as a single parameter.

$date = new DateTime();
$date->setDate($_POST["year"], $_POST["month"], $_POST["day"]);
$dateString = $date->format('Y-m-d');

$sqlUpdate3 = $connection->prepare("
  UPDATE Users SET DateOfBirth = ?
  WHERE UserName=?");
$sqlUpdate3->bind_param("ss", $dateString, $_SESSION["username"]);

Upvotes: 1

Related Questions