CODEWITHSUNDEEP
phpsqlarrayscodeigniterwhere-in
Catfish
Catfish

Reputation: 19294

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?

Upvotes: 2

Views: 12442

Answers (3)

mickmackusa
mickmackusa

Reputation: 47894

To pass an array into a raw query (not built with helper methods) for an IN condition, use the ? placeholder without wrapping in parentheses.

When binding the array to the placeholder, you must declare your array inside of an array. In other words, the "parameters" (2nd) argument of query() method expects an array which relates to each ? in the SQL string. Because the first ? is bound to an array, the $mechanicIds array must be declared as the first element of the "parameters" argument. I have tested this advice to work successfully in a CodeIgniter3 instance that I have access to.

$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) AS avg_rating
        FROM mechanic        AS m,
             mechanic_rating AS mr,
             rating          AS r
        WHERE m.mechanic_id IN ?                            /* <--- here */
          AND m.mechanic_id = mr.mechanic_id
          AND mr.rating_id = r.rating_id";

$query = $this->db->query($sql, [$mechanicIds]);

The DB_driver.php core file contains this portion of code in compile_binds() which escapes each value in the array and wraps the comma-joined string in parentheses before returning the sql string.

...
do
{
    $c--;
    $escaped_value = $this->escape($binds[$c]);
    if (is_array($escaped_value))
    {
        $escaped_value = '('.implode(',', $escaped_value).')';
    }
    $sql = substr_replace($sql, $escaped_value, $matches[0][$c][1], $ml);
}
while ($c !== 0);
...

Upvotes: 0

Catfish
Catfish

Reputation: 19294

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

Upvotes: 2

jfoucher
jfoucher

Reputation: 2281

Change this line:

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

to this: 

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

as per the manual

Upvotes: 0

Related Questions