March 9, 2013
Insert statement not working using execute(array()) of PDO Extension
Question by shnisaka
$stmt = $conn->prepare("INSERT INTO user VALUES ('',:username,md5(:password),'',1,'','',:email,'',0,0,'',:cover,:dateofbirthYear:dateofbirthMonth:dateofbirthDay,NOW(),:sex,:country)");
$stmt->execute(array(
':username' => $username,
':password' => $password,
':email' => $email,
':cover' => $cover,
':dateofbirthYear' => $dateofbirthYear,
':dateofbirthMonth' => $dateofbirthMonth,
':dateofbirthDay' => $dateofbirthDay,
':sex' => $sex,
':country' => $country
));
For some reason this insert statement is not working. I am very new in PDO so I do not know much about it. What am I doing wrong?
this statment gives me this error :
Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens’ in /home/manga/public_html/new/register.php:80 Stack trace:
#0 /home/manga/public_html/new/register.php(80): PDOStatement->execute(Array)
#1 {main} thrown in /home/manga/public_html/new/register.php on line 80
Answer by Starx
You have prepared your query in the wrong way
INSERT INTO user VALUES ('',:username,md5(:password),'',1,'','',:email,'',0,0,'',
:cover,:dateofbirthYear:dateofbirthMonth:dateofbirthDay,NOW(),:sex,:country
// ^ These need to either single or separated
For what you are trying, you can do it this way
//Prepare the date of birth earlier
$dob = $dateofbirthYear.$dateofbirthMonth.$dateofbirthDay;
//Then pass it as a single $variable
$stmt = $conn->prepare("INSERT INTO user VALUES ('',:username,md5(:password),'',1,'','',:email,'',0,0,'',:cover,:dob,NOW(),:sex,:country)");
$stmt->execute(array(
':username' => $username,
':password' => $password,
':email' => $email,
':cover' => $cover,
':dob' => $dob, // <-- Problem solved
':sex' => $sex,
':country' => $country
));
// Then it will execute