phpsqlarrayscodeigniterwhere-in

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


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?


Solution

  • 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;
            }
        }