Chris Chris - 1 year ago 41
MySQL Question

Error:sqlstate[hy093]: invalid parameter number

I'm at a loss for this code, I'll start with what works.

I can convert the echo'ed output in to an sql statement that executes in myphpadmin going...

From this:

INSERT INTO crumbs (ip_address,ip_address_2,device_info,user_id,connections) VALUES(?,?,?,?,?)Value:'', '', 0,0000, 1

Into this:

INSERT INTO crumbs (ip_address,ip_address_2,device_info,user_id,connections) VALUES('', '', 0,0000, 1)

It inserts the data in to the DB, no errors, however it executes through PHP-PDO...


SQLSTATE[HY093]: Invalid parameter number

The code:

$columns = '('.implode(',', array_keys($user_connection)).''.",user_id,connections)";
$inserts="(".implode(',',array_fill(0,count($user_connection)+2, '?')).")";
$values = implode(', ',($user_connection)).",$user_id, 1";

$sql_insert = "INSERT INTO crumbs ".$columns." VALUES".$inserts;
$stmt = $this->_db->prepare($sql_insert);

Edit-Adding $user_connection

$user_connection [ 'ip_address'] = "'".$_SERVER['REMOTE_ADDR']."'";
$user_connection [ 'ip_address_2']="'".$_SERVER['HTTP_X_FORWARDED_FOR']."'";
$user_connection ['device_info']=0;

The error occurs during the execution of the SQL code. I've gone over all the examples and found nothing that's equivalent, I'm thinking it's something simple I'm missing (a rule?) since the code executes locally. Thanks for your help.

Answer Source

You have to do something like this:

// ..code..
$values = $user_connection;
$values[] = $user_id;
$values[] = 1;
// ..code..

Explanation of the problem:

When you have multiple ? placeholders, you can pass each value to be bounded as the values of an array (See Example #3 from the manual).

Now, since you are using implode, $values will be a single string, something like

'', '', 0, 'userid', 1

That means that when you call execute(array($values)), it will, in fact, bound it like this (representation-only, it's not really like this)

INSERT INTO crumbs (ip_address,ip_address_2,device_info,user_id,connections) VALUES ("'', '', 0, 'userid', 1", ?, ?, ?, ?)

because you only sent and array that has one value: the implosion of the other array. Since you didn't provide the same amount of values (1) as you have placeholders (5), you end up with

Invalid parameter number