Codeigniter MySQL Query with Variables
<p>I'm trying to run the following sample query in Codeigniter</p>

<pre><code>SELECT users.id, users.first_name, users.last_name, users.game_id
FROM users
WHERE users.id NOT IN
(SELECT banned.users_id FROM banned) AND game_id = '1'
ORDER BY last_name
</code></pre>

<p>Now this works fine in MySQL, however I would require the following to be variables (game_id, '1', last_name) so that I can do the following</p>

<p><strong>MODEL</strong></p>

<pre><code>function get_where_custom_ordered_checked($col, $value, $order_by) {

        $query = $this-&gt;db-&gt;query('
                                    SELECT users.id, users.first_name, users.last_name, users.game_id
                                    FROM users
                                    WHERE users.id NOT IN
                                    (SELECT banned.users_id FROM banned) AND $col = $value
                                    ORDER BY $order_by
                                    ');
        return $query;
    }
</code></pre>

<p>Obviously this doesn't work, and the query doesn't recognise the variable. How would I achieve this? I have also tried using SET as shown in my research like the following (but still no avail).</p>

<p><strong>MODEL</strong></p>

<pre><code>function get_where_custom_ordered_checked($col, $value, $order_by) {
        $query1 = 'SET @col = '$col', @value = '$value', @order_by = '$order_by'';
        $this-&gt;db-&gt;query($query1);
        $query2 = $this-&gt;db-&gt;query('
                                    SELECT users.id, users.first_name, users.last_name, users.game_id
                                    FROM users
                                    WHERE users.id NOT IN
                                    (SELECT players_phases.players_id FROM players_phases) AND @col = @value
                                ORDER BY @order_by
                                    ');
        $query = $this-&gt;db-&gt;query($query2);
        return $query;
    }
</code></pre>

<p>Now I've tried breaking it down instead to use the Codeigniter Active Record Class,  but I don't know how to include the where_not_in() inside a where(), especially since I need to run the NOT IN query against a second table - unlike the where_not_in() examples shown in the Active Records CI documentation where it is against the same table.</p>

<p>Any advice or assistance would be much appreciated.</p>

<p>Many thanks.</p>