php Bind array to IN()

Question

I am trying to pass an array into a SELECT * FROM table WHERE IN() statement. I am expecting 3 result. but I got 2 result instead.

I am aware same question in the below 2 posts

Can I bind an array to an IN() condition?

and

MySQL WHERE IN () + AND , PDO returns only one row

However I am still learning php pdo, thus I dont understand the code enough to figure out how to repurpose their code to my code.

I hope someone can point out where I did wrong. because I get partial results from the select in statement. I suspect I did not pass array into the IN() correctly.

below is php code of my controler page

$customer_ids = array(1,2,3,4,5,6,7); 
//in actual case I get array from another query.

$customer_id = implode(',',$customer_ids);
//add comma to the array

$template->orders = $rephome->getOrder($customer_id);
//passing the imploded array into my Model page with class named rephome, 
//call on the method of getOrder, which is the select query

below is my model page.

public function getOrder($customer_id){
$this->db->query("SELECT * FROM orders WHERE customer_id_fk IN(.$customer_id.)");
$results = $this->db->resultset();
return $results;

Below is what is in my database class

public function query($query) {
        $this->stmt = $this->dbh->prepare($query);
    }


    public function bind($param, $value, $type = null) {
        if (is_null ( $type )) {
            switch (true) {
                case is_int ( $value ) :
                    $type = PDO::PARAM_INT;
                    break;
                case is_bool ( $value ) :
                    $type = PDO::PARAM_BOOL;
                    break;
                case is_null ( $value ) :
                    $type = PDO::PARAM_NULL;
                    break;
                default :
                    $type = PDO::PARAM_STR;
            }
        }
        $this->stmt->bindValue ( $param, $value, $type );
    }


    public function execute(){
        return $this->stmt->execute();
    }


    public function resultset(){
        $this->execute();
        return $this->stmt->fetchAll(PDO::FETCH_OBJ);
    }

Build the PDO string with as many placeholders as items in the array. Bind each placeholder with the appropriate value. — user2864740, May 31 '16 at 19:13
In any case, a/the "problem" is `".. IN(.$customer_id.)"` results in `.. IN(.1,2,3,4,5,6,7.)` which is invalid SQL - the `.` is just a literal inside the string. The error message would have said as much. — user2864740, May 31 '16 at 19:16
I think you just solved my problem. by removing the dots. I got the correct result. — codenoob, May 31 '16 at 19:18

score 3 · Accepted Answer · answered May 31 '16 at 19:24

You're not binding an array. You're converting your array into a string, and then binding that string. Bound parameters can represent only SINGLE values, not lists-of-values, so your implode/bind version is functionally identical to

SELECT ... foo IN ('1,2,3')

which executes exactly the same as

SELECT ... foo = '1,2,3'

instead of

SELECT ... IN (1,2,3)
               ^-^-^--- three separate values

What you want is simply not possible with a single placeholder. You need to build your query dynamically (one placeholder per value), and then you pass your array in to the query call, e.g.

$temp = array_fill(1, count($customer_ids), '?');
$in_arg = implode(',', $temp);
$sql = "SELECT ... IN ($in_arg)";
$stmt = $db->prepare(sql);
$res = $stmt->execute($customer_ids);

This would produce a query string like

SELECT ... IN (?,?,?,....)

with a placeholder for every value you want to bind.

score 0 · Answer 2 · answered May 31 '16 at 19:17

You can modify the bottom of your switch statement to something like this, what it will do is identify if you have an array and implode it with the , seperator, and then flow into your default type of PDO::PARAM_STR ... which it now is.

case is_array ( $value ) :
    $value = implode(',', $value);
default :
    $type = PDO::PARAM_STR;

php Bind array to IN()

2 Answers2

Linked