Codeigniter在循环内组合查询

Question

I have an array of IDs and I am fetching the details of those items from the database. I created a foreach loop to get each item one by one then push it to the array before returning to the controller. The problem is the query builder combines all the IDs in one single query.

public function get_product_by_ids($ids) {
        $products = array();
        foreach ($ids as $id) {
            $this->db->where('product_id', $id);
            $query = $this->db->get('products')->row_array();

            array_push($products, $query);
        }

    return $products;
}

Here is the result of that code, using the profiler.

My IDs are

Array
(
    [0] => 22
    [1] => 18
    [2] => 21
)

The produced query from the profiler:

SELECT *
FROM `products`
WHERE `product_id` = '22'
AND `product_id` = '18'
AND `product_id` = '21'
AND `product_id` = '22' 

SELECT *
FROM `products`
WHERE `product_id` = '18' 

SELECT *
FROM `products`
WHERE `product_id` = '21' 

Output: The first one is empty, then I got the second up to the last.

I tried using where_in() as for @danblack said and here is the produced query:

SELECT *
FROM `products`
WHERE `product_id` = '22'
AND `product_id` = '18'
AND `product_id` = '21'
AND `product_id` IN('22', '18', '21') 

Here are my new codes:

public function get_product_by_ids($ids) {
        $this->db->where_in('product_id', $ids);
        $query = $this->db->get('products')->result_array();

        return $query;
}

Output: Empty Array.

Answer 1

I don't know if this is a problem with CodeIgniter (on codeigniter 3.x) or I made something wrong, but here is my temporary solution:

public function get_product_by_ids($ids) {
        $newquery = "SELECT * FROM products WHERE product_id IN(";

        $counter = 0;
        foreach ($ids as $id) {
            $newquery .= "'".$id."'";
            if($counter < (sizeof($ids) - 1)) {
                $newquery .= ", ";
            }
            $counter++;
        }

        $newquery .= ")";
        $query = $this->db->query($newquery)->result_array();

        return $query;
}