Bind array to placeholder in a CodeIgniter query() containing SQL with WHERE ... IN(?)

Question

I'm trying to pass an array to a model which has a query. I'm not sure how to correctly pass the array or if I have to manipulate the array somehow.

I have this array:

Array
(
    [0] => 1
    [1] => 2
)

I have a controller with this line:

$ratings = $this->login_model->get_ratings($mechanicIds);   // get the mechanic ratings

I have this model:

function get_ratings($mechanicId)
{
    $sql = "select m.mechanic_id, 
                   m.mechanic_name, 
                   m.city, 
                   m.state, 
                   count(mr.rating_id) as num_ratings, 
                   round(avg(mr.rating_id),2) avg_rating
            from mechanic m, mechanic_rating mr, rating r
            where m.mechanic_id in (?)
                and m.mechanic_id = mr.mechanic_id
                and mr.rating_id = r.rating_id";
        
    $query = $this->db->query($sql, $mechanicId);
            
    if ($query->num_rows() > 0) {
        return $query->result_array();
    } else {
        return false;
    }
}

It actually returns results, but the problem is it only returns the results 1 row when it should be returning 2 since there are 2 results in my array. Anyone know what I'm doing wrong?

Answer 1

I found this question which helped.

Below is the code I used.

Controller that contains this:

                $mIds_size = count($mIds);
                $i = 1;

                foreach($mIds as $row)
                {
                    if($i == $mIds_size)
                    {
                        $mechanicIds .= $row;
                    }
                    else
                    {
                        $mechanicIds .= $row.', ';
                    }
                    $i++;
                }

                $ratings = $this->login_model->get_ratings($mechanicIds);   // get the mechanic ratings 

Model which contains this:

    function get_ratings($mechanicId)
    {

        $this->db->escape($mechanicId);

        $sql = "select m.mechanic_id, 
                       m.mechanic_name, 
                       m.city, 
                       m.state, 
                       count(mr.rating_id) as num_ratings, 
                       round(avg(mr.rating_id),2) avg_rating
                from mechanic m, mechanic_rating mr, rating r
                where m.mechanic_id in ($mechanicId)
                and m.mechanic_id = mr.mechanic_id
                and mr.rating_id = r.rating_id
                group by mechanic_id";

        $query = $this->db->query($sql, $mechanicId);

        if($query->num_rows() > 0)
        {
            return $query->result_array();
        }
        else
        {
            return false;
        }
    }