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

[ktpmm5]

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?

[gizmola]

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.

[ktpmm5]

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

[gizmola]

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?

[maxxd]

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.

[gizmola]

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.

[kicken]

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.

[maxxd]

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

[gizmola]

More mysql syntax nonsense. Even if it's valid let's pretend it isn't and just use the standard syntax that all other RDBMS's use.

[jazzman1]

 

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',.....)

[DavidAM]

... 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.

[ktpmm5]

That was it - thank you so much!