CODEWITHSUNDEEP
phpmysqlarrayspdo
user3144542
user3144542

Reputation: 599

PDO execute array to string conversion error

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?

Upvotes: 4

Views: 18793

Answers (2)

Amal Murali
Amal Murali

Reputation: 76656

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

Upvotes: 6

gen_Eric
gen_Eric

Reputation: 227260

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

Upvotes: 1

Related Questions