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

OrangeGrover · 04-16-2011, 03:01 AM

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

smoker · 04-16-2011, 03:35 AM

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.

OrangeGrover · 04-16-2011, 12:04 PM

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();

    }

        

        

        

        

        

        

        

}

smoker · 04-16-2011, 01:29 PM

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

OrangeGrover · 04-16-2011, 02:29 PM

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

smoker · 04-16-2011, 02:59 PM

Pesky syntax errors ;-)