exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number

Question

This look a lot simple but for some reasons i can not get it to work... I need all the help i can get

 function register_user($email, $password,  $gender, $phone_number){
    $this->sql = "insert into user
                  (
                  email_addr, phone_number, password,
                  gender, activation_code, isMobileVerified,
                  last_login_date, unix_sign_up_time, sign_up_date
                  ) VALUES
                  (
                  :email, :phone_number, :password,
                  :gender, :activation_code, :isMoobileVerified,
                  :last_login, :time_unix, NOW()
                  )";
    $this->prepare($this->sql);
    $this->bind(':email', $email);
    $this->bind(':phone_number', $phone_number);
    $this->bind(':password', $password);
    $this->bind(':gender', $gender);
    $this->bind(':activation_code', sha1($password));
    $this->bind(':isMobileVerified', 0);
    $this->bind(':last_login', time());
    $this->bind(':time_unix', time());

    $this->execute();

    return $this->lastInsertedId();
}

when i run this function like this

try{
    echo "<br>" . $i->register_user('[email protected]', 'password', 'male', '2348020000007');
}catch (PDOException $e) {
    //todo: logging function or mail to dev goes here
    echo $e ."<br>". $e->getMessage();
}

i get this error

exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined' in C:\wamp\www\ecommerce\system\model\class.ecommerce.php:215 Stack trace: #0 C:\wamp\www\ecommerce\system\model\class.ecommerce.php(215): PDOStatement->bindValue(':isMobileVerifi...', true, 5) #1 C:\wamp\www\ecommerce\system\model\class.ecommerce.php(281): ukorJidechi\db_handler->bind(':isMobileVerifi...', true) #2 C:\wamp\www\ecommerce\namespace_test.php(51): ukorJidechi\ecommerce_user->register_user('myMail@register...', 'password', 'male', '2348020000007') #3 {main} SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

here is my custom bind method

function bind($placeholder, $value, $type = null){
    if (is_null($type)) {
        switch (true) {
            case is_int($value):
                $type = \PDO::PARAM_INT;
                break;
            case is_bool($value):
                $type = \PDO::PARAM_BOOL;
                break;
            case is_null($value):
                $type = \PDO::PARAM_NULL;
                break;
            default:
                $type = \PDO::PARAM_STR;
        }
    }
    $this->stmt->bindValue($placeholder, $value, $type);
}

Can someone show me what I am doing wrong? Please.

Rajdeep Paul · Accepted Answer · 2016-09-18 11:16:25Z

1

See this statement here,

$this->bind(':unix_sign_up_time', time());

And compare the above statement with $this->sql

$this->sql = "insert into user
              (
              ...
              :last_login, :time_unix, NOW()
                            ^^^^^^^^^
              )";

So your bind() statement should be,

$this->bind(':time_unix', time());

Edited:

See this line here in $this->sql,

$this->sql = "insert into user
              (
              ...
              :gender, :activation_code, :isMoobileVerified,
                                          ^^^^^^^^^^^^^^^^^^
              ...
              )";

It should be :isMobileVerified

edited Sep 18, 2016 at 11:16

answered Sep 18, 2016 at 10:32

Rajdeep Paul

16.9k3 gold badges21 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ukor Over a year ago

Thank you for spotting that, i have corrected my code and edited my question, but i am still getting same error message.

Rajdeep Paul Over a year ago

@Ukor Is bind() your custom class method? Because if it's not, then it should be bindParam(), not bind().

Ukor Over a year ago

it is my custom class method,... I will update my question to show my custom bind method. i have updated my question to show my custom bind method. Thank you for your time.

Rajdeep Paul Over a year ago

@Ukor By looking at your error message, it seems the error lies in this statement, $this->bind(':isMobileVerified', 0);. Pass a different value than 0 to this method, and see if it works or not. It will narrow down your issue further.

Rajdeep Paul Over a year ago

@Ukor I've updated my answer. Please see the Edited section of my answer.

|

Collectives™ on Stack Overflow

exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related