CODEWITHSUNDEEP
pdophp
syrkull
syrkull

Reputation: 2344

Insert statement not working using execute(array()) of PDO Extension

 $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

Upvotes: 3

Views: 28230

Answers (3)

D-R-M
D-R-M

Reputation: 1

Corrected prepared query:

$stmt = $conn->prepare("INSERT INTO user VALUES ('',:username,md5(:password),'',1,'','',:email,'',0,0,'',:cover,:dateofbirthYear,:dateofbirthMonth:,dateofbirthDay,NOW(),:sex,:country)");
//:dateofbirthYear,:dateofbirthMonth:,dateofbirthDay place holders are seprated 

$stmt->execute(array(
 ':username'   => $username,
  ':password' => $password,
  ':email'   => $email,
  ':cover' => $cover,
  ':dateofbirthYear'   => $dateofbirthYear,
  ':dateofbirthMonth' => $dateofbirthMonth,
  ':dateofbirthDay'   => $dateofbirthDay,
  ':sex' => $sex,
  ':country'   => $country 
));

Upvotes: -2

hakre
hakre

Reputation: 197775

The exact error message you have is:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

This means that the number/names of parameters you have passed (the array() in execute) does not match with the number/names of parameters you have in the prepare() SQL-query.

If you compare that with the other questions that contain SQLSTATE[HY093] you will see that it is often related to code that is large and bad formatted which is hard to read. That makes it hard to count. And then you have an oversight of something and then the error happened.

Just fix it and done, for example you can not make one parameter out of three names:

,:dateofbirthYear:dateofbirthMonth:dateofbirthDay,

Instead just pass one parameter for the birthday:

, :dateofbirth,

You can also make your code a bit more readable:

$stmt = $conn->prepare(
    "INSERT INTO user
     VALUES (
        '', :username, md5(:password), '', 1, '', '', :email, '', 0, 0, '',
        :cover, :dateofbirth, NOW(), :sex, :country
     )"
);
$stmt->execute(array(
    ':username'    => $username,
    ':password'    => $password,
    ':email'       => $email,
    ':cover'       => $cover,
    ':dateofbirth' => $dateofbirthYear . $dateofbirthMonth . $dateofbirthDay,
    ':sex'         => $sex,
    ':country'     => $country
));

And then you have a security problem with the password hash:

md5(:password)

Instead do proper password hashing, see the PHP FAQ about Safe Password Hashing.

Upvotes: 2

Starx
Starx

Reputation: 78991

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

Upvotes: 7

Related Questions