4

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?

user3144542
  • 599
  • 3
  • 9
  • 19
  • See example #5 on the docs page for `->execute()`. http://www.php.net/manual/en/pdostatement.execute.php – gen_Eric Feb 26 '14 at 17:58

2 Answers2

6

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);
Community
  • 1
  • 1
Amal Murali
  • 75,622
  • 18
  • 128
  • 150
1

->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);
gen_Eric
  • 223,194
  • 41
  • 299
  • 337
  • 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? – user3144542 Feb 26 '14 at 18:04
  • @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 Feb 26 '14 at 18:07
  • @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))`. – gen_Eric Feb 26 '14 at 18:15
  • 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](http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15991146) I had to restructure the query a little bit by doing the `IN` before the `=` but now it works as intended. – user3144542 Feb 26 '14 at 18:15