LinuxQuestions.org - PHP + MYSQL: Invalid parameter number: number of bound variables does not match...

- Linux - Software (https://www.linuxquestions.org/questions/linux-software-2/)

- - PHP + MYSQL: Invalid parameter number: number of bound variables does not match... (https://www.linuxquestions.org/questions/linux-software-2/php-mysql-invalid-parameter-number-number-of-bound-variables-does-not-match-875290/)

PHP + MYSQL: Invalid parameter number: number of bound variables does not match...

Hello Everybody,
I am attempting to Insert some data into my mySQL table but it's not working; instead it throws the following error:

Quote:

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

My table was made from the following script:

Code:

CREATE TABLE users (

  userID        INT                    NOT NULL          AUTO_INCREMENT,

  f_name                VARCHAR(30)                NOT NULL,        

  l_name                VARCHAR(30)                NOT NULL,        

  m_name                VARCHAR(30)                                DEFAULT NULL,

  email                          VARCHAR(255)          NOT NULL,

  password                VARCHAR(60)            NOT NULL,

  bday                        CHAR(10),

  city                        varchar(200),        

  zipCode                INT,

  relationship        varchar(30)                                        DEFAULT NULL,

  profilePic        varchar(255)        NOT NULL,

  sex                        char(1)                        NOT NULL,

  valid                        TINYINT(1)                NOT NULL        DEFAULT 0,

  confirm                varchar(12)                NOT NULL,

  PRIMARY KEY (userID),

  UNIQUE INDEX email (email)

);

And the code that I am attempting to run in order to insert the values is:

PHP Code:




    require_once 'database_connect.php'; 
//Functions that will change data on the Users tables. 
 
//Initial user add; Only used after the user registers. 
function add_user($f_name, $l_name, $email, $password, $bday, $zipCode ,$sex, $confirm, $m_name)  
{ 
    global $db; 
     
//Prepare data to be inserted 
    $password = md5($email . $password_1); 
     
    $city = 'Highland'; 
    $relationship = 'Single'; 
    $valid = 0; 
     
    $query = ' 
        INSERT INTO users (    f_name, l_name, m_name, email, password, bday, city, zipCode,  
                            ,relationship, profilePic, sex, valid, confirm) 
        VALUES (:f_name, :l_name, :m_name, :email, :password, :bday, :city, :zipCode, :relationship, :profilePic, :sex, :valid, confirm)'; 
    try{ 
        $statement = $db->prepare($query); 
        $statement->bindValue(':f_name', $f_name); 
        $statement->bindValue(':m_name', $m_name);; 
        $statement->bindValue(':l_name', $l_name); 
        $statement->bindValue(':email', $email); 
        $statement->bindValue(':password', $password); 
        $statement->bindValue(':bday', $bday); 
        $statement->bindValue(':zipCode', $zipCode); 
        $statement->bindValue(':profilePic', $profilePic); 
        $statement->bindValue(':sex', $sex); 
        $statement->bindValue(':confirm', $confirm); 
         
 
        //Hard Coded in For testing.. Normally I would leave these Null for the moment. 
        $statement->bindValue(':city', $city); 
        $statement->bindValue(':relationship', $relationship); 
        $statement->bindValue(':valid', $valid); 
         
        $statement->execute(); 
        $user_id = $db->lastInsertId(); 
        $statement->closeCursor(); 
        return $user_id; 
    } 
    catch (PDOexception $e)  
    { 
        $error_message = $e->getMessage(); 
        echo "<p>Database Error: $error_message </p>"; 
        exit(); 
    } 
         
         
}

I have been wrestling with this problem for a good 4.5 hours and I haven't figured it out yet.
Does anybody know what the problem is, or can at least point me in the right direction?

Thanks for reading,
-Chris

I would start by doing what the error complains about. You have 14 fields in your table but you are only binding 13 of them to values. Just because you have a field that is auto incremented, does not mean you can omit a value for it in your insert statement. A simple zero will suffice.

Hmm, I tried adding all the values, but I still got the same error. Then I found that I had accidentally used the wrong variable to bind the values, so just in case, I tried inserting constant data into the database, but it's still not working- I get the same error as before.

Any ideas?

PHP Code:




    require_once 'database_connect.php'; 
//Functions that will change data on the Users tables. 
 
//Initial user add; Only used after the user registers. 
function add_user($f_name, $l_name, $email, $password, $bday, $zipCode ,$sex, $confirm, $m_name)  
{ 
    global $db; 
     
//Prepare data to be inserted 
  //  $password = md5($email . $password_1); 
     
    $userID = 0; 
    $f_name= 'TestName'; 
    $l_name = 'TestLNAME';     
    $m_name = NULL; 
    $email = 'example@test.com'; 
    $password = 'ah77yheb2h'; 
    $bday = '08-02-1430'; 
    $zipCode = 12345; 
    $profilePic = 'something.jpeg'; 
    $sex = 'M'; 
    $confirm = '123123'; 
    $city = 'Highland'; 
    $relationship = 'Single'; 
    $valid = 0; 
     
     
    $query = ' 
        INSERT INTO users (    userID, f_name, l_name, m_name, email, password, bday, city, zipCode,  
                            ,relationship, profilePic, sex, valid, confirm) 
        VALUES (:userID, :f_name, :l_name, :m_name, :email, :password, :bday, :city, :zipCode, :relationship, :profilePic, :sex, :valid, confirm)'; 
    try{ 
        $statement = $db->prepare($query); 
        $statement->bindValue(':f_name', $f_name); 
        $statement->bindValue(':m_name', $m_name); 
        $statement->bindValue(':l_name', $l_name); 
        $statement->bindValue(':email', $email); 
        $statement->bindValue(':password', $password); 
        $statement->bindValue(':bday', $bday); 
        $statement->bindValue(':zipCode', $zipCode); 
        $statement->bindValue(':profilePic', $profilePic); 
        $statement->bindValue(':sex', $sex); 
        $statement->bindValue(':confirm', $confirm); 
         
         
 
        //Hard Coded in For testing.. Normally I would leave these Null for the moment. 
        $statement->bindValue(':city', $city); 
        $statement->bindValue(':relationship', $relationship); 
        $statement->bindValue(':valid', $valid); 
        $statement->bindValue(':userID', $userID);     
         
        $statement->execute(); 
        $user_id = $db->lastInsertId(); 
        $statement->closeCursor(); 
        return $user_id; 
    } 
    catch (PDOexception $e)  
    { 
        $error_message = $e->getMessage(); 
        echo "<p>Database Error: $error_message </p>"; 
        exit(); 
    } 
         
         
         
         
         
         
         
}

Check your sql statement INSERT INTO users ...
What is between zipCode and relationship ?

Thank you,
That was one of the problems. The other one that was causing an issue was the fact that I forgot to add a colon to my values, so there was no binding for the confirm parameter.

Thanks again.
-Chris

Pesky syntax errors ;-)

I came across this and I do not like reopening very old posts however the statement made by smoker

Quote:

Just because you have a field that is auto incremented, does not mean you can omit a value for it in your insert statement. A simple zero will suffice.

This statement is inacurate as MySQL and SQL both dictate that if a field is set to auto incriment then you do not nor should you need to use it in your query. It is a AUTO INCRIMENTED value(which is usually used as a primary key) and by its very nature needs no interaction via the insert statement. Further more by passing a 0 to that field would result in an error as I am sure the key 0 will have already been used and you will get duplicated key error.

Like I said above I do not usually ressurect old posts however I found this individuals statement VERY INCORRECT and felt that I should post so if noobies come along in the future they do not take smoker's statement as fact when in fact it is incorrect. This type of misinformation leads to new coders becomming sloppy coders very quickly!

Just trying to be helpful, thanks!