PDO execute array to string conversion error

Question

I am getting an array to string conversion error when I am trying to run a PDO execute. The execute has to inputs one being a normal string and the other an array. Heres my code:

$id = "1";    
$array = array("a",  "b",  "c");
$in  = str_repeat('?,', count($array) - 1) . '?';

$sql1 = "SELECT * FROM tbl1 AS tb1 RIGHT JOIN tbl2 AS tb2 ON (tb1.e_id = tb2.e_id)LEFT JOIN tbl3 AS tb3 ON (tb2.m_id = tb3.m_id) WHERE tb1.u_id = ? AND tb1.letters IN ($in)";


$statement1 = $db_handle->prepare($sql1);
$statement1->setFetchMode(PDO::FETCH_ASSOC);
$statement1->execute(array($id,$array));

$res = $statement1->fetchAll();

So the execute line gives me the string conversion error. I have printed my array and the question marks and they output fine. The sql statement is also fine, I have tried it on phpMyAdmin and it works just fine so clearly I am not using the execute properly but I am not sure how to change the way I have executed it.

Can someone explain it to me?

Solution 1

execute() method expects a single array. From the documentation:

Execute the prepared statement. If the prepared statement included parameter markers, you must either:

• (snip)
• pass an array of input-only parameter values

With array($id,$array) you'd be passing a multi-dimensional array that looks like this:

Array
(
    [0] => 1
    [1] => Array
        (
            [0] => a
            [1] => b
            [2] => c
        )

)

This is certainly not what it expects. It needs a single-dimensional array containing the values to be inserted.

To fix this issue, you have to modify the array format. Add the ID to the start of the array, like so:

$array = array(1, "a",  "b",  "c");

If the ID variable is generated dynamically, then you can manually add it to the array start using array_unshift() function:

$id = "1";    
$array = array("a",  "b",  "c");
$array = array_unshift($array, $id);

... and then modify the execute() call like so:

$statement1->execute($array);

Solution 2

->execute() expects you to send it an array with each element representing each ? in the query.

You are only sending it 2 elements, so it's gonna try to use the entire array as the 2nd ?, which is why it's trying to convert it to a string. And then it will probably complain about not having enough parameters.

Try it like this:

$dataArray = $array;
array_unshift($dataArray, $id);

$statement1->execute($dataArray);

Comments

[user3144542]

This did get rid of the string conversion error thank you but it creates a further error and gives me this error Invalid parameter number: number of bound variables does not match number of tokens is this down to the conversion or another error entirely?

[gen_Eric]

@user3144542: Try to echo $sql1; to see how many ?s there are in it. Then var_dump($dataArray) and make sure the counts match.

[gen_Eric]

@AmalMurali: You've misinterpreted that line. It's saying that you cannot do this: SELECT * FROM table WHERE id IN (:ids) and then try to call ->execute(array(":ids" => array(1,2,3)).

[user3144542]

Nope the ? match but I did find a solution a user posted the answer then removed it but the answer is in the following link I had to restructure the query a little bit by doing the IN before the = but now it works as intended.

[gen_Eric]

You've misinterpreted that line. Check Example #5 on the docs page for ->execute().