Binding an array to the placeholders in a SQL query

Question

I am trying to pass an array to my query placeholders but its not working as i expect. This is the query :

select * from A where id IN (1,2,3)

This is the PHP function :

$ids=['1','2','3'];
$result = $this->queryall_array(select * from A where id IN (?,?,?),$ids);

I am not sure if this is the right way to do that. Thanks,

What RDBMS are you actually using? Please only tag that database. I'm not PHP savvy, but it looks like you are asking for 3 parameters and only passing 1. — SS_DBA, Jun 23 '17 at 12:57
@WEI_DBA Hi. I am using Oracle. I though that the function queryall_array() is auto convert the $ids to 3 parameters. This queryall_array() is something that one of my co-worker wrote. — daniel8x, Jun 23 '17 at 13:02
@WEI_DBA Do you know how can i do that in this case. I have been trying to use implode() to add comma to $ids so i can have 3 parameters to pass to the ? instead of 1 now. But it did not work. Thanks — daniel8x, Jun 23 '17 at 13:07
@WEI_DBA Never Mind. I got it works. After convert it to string with comma, I am passing directly inside IN. Thanks for your consideration. — daniel8x, Jun 23 '17 at 13:15
You can pass an [Oracle collection as a bind parameter](https://stackoverflow.com/a/41161057/1509264) - here is [an example of how to do it in Java](https://stackoverflow.com/a/34699771/1509264). No idea how to do it in PHP though. — MT0, Jun 23 '17 at 13:15
@trinhdh. Very good! Can you update your post with your answer for future reference for others? Thanks! — SS_DBA, Jun 23 '17 at 13:35

score 0 · Answer 1 · answered Jun 23 '17 at 14:59

We can't see the content of your custom queryall_array function but from the php documentation here is the correct way to do it with oracle:

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$stid = oci_parse($conn,"INSERT INTO mytab (id, text) VALUES(:id_bv, :text_bv)");

$id = 1;
$text = "Data to insert     ";
oci_bind_by_name($stid, ":id_bv", $id);
oci_bind_by_name($stid, ":text_bv", $text);
oci_execute($stid);

http://php.net/manual/en/function.oci-bind-by-name.php

Binding an array to the placeholders in a SQL query

1 Answers1