LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

Reply
 
Search this Thread
Old 04-16-2011, 03:01 AM   #1
OrangeGrover
LQ Newbie
 
Registered: Oct 2010
Posts: 9

Rep: Reputation: 0
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
 
Old 04-16-2011, 03:35 AM   #2
smoker
Senior Member
 
Registered: Oct 2004
Distribution: Fedora Core 4, 12, 13, 14, 15, 17
Posts: 2,279

Rep: Reputation: 248Reputation: 248Reputation: 248
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.
 
Old 04-16-2011, 12:04 PM   #3
OrangeGrover
LQ Newbie
 
Registered: Oct 2010
Posts: 9

Original Poster
Rep: Reputation: 0
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();
    }
        
        
        
        
        
        
        

 
Old 04-16-2011, 01:29 PM   #4
smoker
Senior Member
 
Registered: Oct 2004
Distribution: Fedora Core 4, 12, 13, 14, 15, 17
Posts: 2,279

Rep: Reputation: 248Reputation: 248Reputation: 248
Check your sql statement INSERT INTO users ...
What is between zipCode and relationship ?
 
Old 04-16-2011, 02:29 PM   #5
OrangeGrover
LQ Newbie
 
Registered: Oct 2010
Posts: 9

Original Poster
Rep: Reputation: 0
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
 
Old 04-16-2011, 02:59 PM   #6
smoker
Senior Member
 
Registered: Oct 2004
Distribution: Fedora Core 4, 12, 13, 14, 15, 17
Posts: 2,279

Rep: Reputation: 248Reputation: 248Reputation: 248
Pesky syntax errors ;-)
 
1 members found this post helpful.
Old 05-08-2013, 05:36 PM   #7
visualrealityink
LQ Newbie
 
Registered: May 2013
Posts: 1

Rep: Reputation: Disabled
Exclamation

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!

Last edited by visualrealityink; 05-08-2013 at 05:38 PM. Reason: typo
 
  


Reply

Tags
exception, inserting, mysql, php


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
output the occurence number in sed or grep results in every regex match mbaste2 Linux - General 3 04-06-2011 01:58 AM
match number mmhs Linux - Newbie 8 02-25-2011 02:55 AM
Identify and explain the major number, minor number, and revision number in Linux... turbomen Linux - Newbie 1 11-16-2010 02:48 AM
Regular expression to match unspecified number of characters until a '>' cygnal Linux - General 7 07-15-2010 12:21 PM
why sector number not match block number? bitzsk Linux - Kernel 1 06-09-2009 05:32 AM


All times are GMT -5. The time now is 11:25 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration