title: Codeigniter MySQL Query with Variables
body: <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>