Jump to content

Invalid parameter number: number of bound variables does not match number of tokens


ktpmm5
Go to solution Solved by kicken,

Recommended Posts

I'm getting the dreaded " Invalid parameter number: number of bound variables does not match number of tokens" error and I've looked at this for days.  Here is what my table looks like:

|  id          | int(4)       | NO   | PRI | NULL    | auto_increment |
| user_id     | int(4)       | NO   |     | NULL    |                |
| recipient   | varchar(30)  | NO   |     | NULL    |                |
| subject     | varchar(25)  | YES  |     | NULL    |                |
| cc_email    | varchar(30)  | YES  |     | NULL    |                |
| reply       | varchar(20)  | YES  |     | NULL    |                |
| location    | varchar(50)  | YES  |     | NULL    |                |
| stationery  | varchar(40)  | YES  |     | NULL    |                |
| ink_color   | varchar(12)  | YES  |     | NULL    |                |
| fontchosen  | varchar(30)  | YES  |     | NULL    |                |
| message     | varchar(500) | NO   |     | NULL    |                |
| attachment  | varchar(40)  | YES  |     | NULL    |                |
| messageDate | datetime     | YES  |     | NULL    |        

Here are my params:

            $params = array(                 
               ':user_id' => $userid,                 
               ':recipient' => $this->message_vars['recipient'],                 
               ':subject' => $this->message_vars['subject'],                 
               ':cc_email' => $this->message_vars['cc_email'],                 
               ':reply' => $this->message_vars['reply'],                 
               ':location' => $this->message_vars['location'],                 
               ':stationery' => $this->message_vars['stationery'],                 
               ':ink_color' => $this->message_vars['ink_color'],                 
               ':fontchosen' => $this->message_vars['fontchosen'],                 
               ':message' => $messageInput,                 
               ':attachment' => $this->message_vars['attachment'],                 
               ':messageDate' => $date             );

Here is my sql:

$sql = "INSERT INTO messages (user_id,recipient, subject, cc_email, reply, location,stationery, ink_color, fontchosen, message,attachment) VALUES( $userid, :recipient, :subject, :cc_email, :reply, :location, :stationery, :ink_color, :fontchosen, $messageInput, :attachment, $date);";
And lastly, here is how I am calling it:
            $dbh = parent::$dbh;             
           $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);              
           if (empty($dbh))    return false;             
                  $stmt = $dbh->prepare($sql);             
                 $stmt->execute($params) or die(print_r($stmt->errorInfo(), true));              
           if (!$stmt) {    print_r($dbh->errorInfo());              }

I know my userid is valid and and the date is set above (I've echo'd these out to make sure).  Since the id is auto_increment, I do not put that in my sql (though I've tried that too), nor in my params (tried that too).  What am I missing?  I feel certain it is something small, but I have spent days checking commas, semi-colons and spelling.  Can anyone see what I'm doing wrong?

Link to comment
Share on other sites

In your list of columns, you don't include MessageDate, however you pass the date as a parameter in the values. You also have some bind variables in there with some regular values. You should only have the bind variable names.

 

$userid, :recipient, :subject, :cc_email, :reply, :location, :stationery, :ink_color, :fontchosen, $messageInput, :attachment, $date
$userid, $messageInput, $date --- these should all be bind variable names, not the variable values.
Link to comment
Share on other sites

So my sql should be this:

 $sql = "INSERT INTO messages SET "
                . "`user_id`  = :userid,"
                . "`recipient` = :recipient,"
                . "`subject` = :subject,"
                . "`cc_email` = :cc_email,"
                . "`reply` = :reply,"
                . "`location` = :location,"
                . "`stationery` = :stationery,"
                . "`ink_color` = :ink_color,"
                . "`fontchosen` = :fontchosen,"
                . "`message` = :messageInput,"
                . "`attachment` = :attachment,"
                . "`messageDate` = :messageDate";

And I change my params to this:

 $params = array(                 ':user_id' => $userid,                
                                                         ':recipient' => $this->message_vars['recipient'],                
                                                          ':subject' => $this->message_vars['subject'],                
                                                           ':cc_email' => $this->message_vars['cc_email'],                
                                                            ':reply' => $this->message_vars['reply'],                
                                                             ':location' => $this->message_vars['location'],               
                                                               ':stationery' => $this->message_vars['stationery'],                
                                                                 ':ink_color' => $this->message_vars['ink_color'],                
                                                     ':fontchosen' => $this->message_vars['fontchosen'],                 
                                                      ':message' => $this->message_vars['messageInput'],                
                                                       ':attachment' => $this->message_vars['attachment'],                
                                                        ':messageDate' => $date

I've defined date like this:

$date = date('Y-m-d H:i:s');

The $user_id is  a session variable that I've assigned to $user_id - is that ok to go in the sql? 

Even making those changes, I get this error:   Invalid parameter number: parameter was not defined, when executing:

$stmt->execute($params) or die(print_r($stmt->errorInfo(), true));
Link to comment
Share on other sites

Much better now. It does appear that the Message date is most likely the issue.

 

To verify, comment that out of the sql and params list and see if the statement works ok.

 

What is the type of that column in the database?

Link to comment
Share on other sites

Unless it's a variation on the syntax with which I'm not familiar, your insert statement is malformed. It looks like you're using UPDATE syntax. The syntax in your first post "INSERT INTO [table] ([columns]) VALUES ([values])" is correct. The [column]=[value] syntax is used for updating.

  • Like 1
Link to comment
Share on other sites

Unless it's a variation on the syntax with which I'm not familiar, your insert statement is malformed. It looks like you're using UPDATE syntax. The syntax in your first post "INSERT INTO [table] ([columns]) VALUES ([values])" is correct. The [column]=[value] syntax is used for updating.

In my hurry to answer I glossed over the fact that you changed the syntax, but maxxd is exactly right. ;)

 

Go back to your original syntax, and just tweak the original code with the bind variables, and everything else is the same.

 

Insert statements use the INSERT (column,...) VALUES (:bind1,...).

 

Update statements use the SET column = :bind1, ... etc.

Link to comment
Share on other sites

  • Solution

Unless it's a variation on the syntax with which I'm not familiar,

INSERT INTO ... SET ... is valid (see second syntax block) but rarely used.

 

What your error means is that you are trying to bind a parameter that does not exist in the SQL text. In your case, you are trying to bind :message but your SQL text has the parameter named :messageInput.

Link to comment
Share on other sites

 

Hunh - it is valid. Never seen it in the wild, myself, but thanks for the info!

 

I think mysql dev group are using the same convention used by Unix man pages. So, about the link above posted by kicken, the options brackets ["and"] indicate that this modifier is not needed, but can be used. Also, any one from the link can be followed by a "..." which tells you that they can be used one or more times. On the other hand the compulsory curly braces {"and"} indicate that this modifier is required in the string as well as the bold italic text. So, the following sql syntax should be correct using mysql (never tested) 

 

INSERT tbl_name VALUE ('val_1','val_2',.....)

Link to comment
Share on other sites

... So, the following sql syntax should be correct using mysql (never tested) 

 

INSERT tbl_name VALUE ('val_1','val_2',.....)

That is valid, as long as you include a value for EVERY column in the table.

 

 

For the record, I have used the INSERT ... SET ... syntax on occasion. If you are building queries dynamically, you can build up the column = value list and then add it to an INSERT or UPDATE statement. So, you only need one process to build the query regardless of which statement you are building.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.