Ryan N. Ryan N. - 7 months ago 20
SQL Question

Codeigniter MySQL Query with Variables

I'm trying to run the following sample query in Codeigniter

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


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

MODEL

function get_where_custom_ordered_checked($col, $value, $order_by) {

$query = $this->db->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;
}


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).

MODEL

function get_where_custom_ordered_checked($col, $value, $order_by) {
$query1 = 'SET @col = '$col', @value = '$value', @order_by = '$order_by'';
$this->db->query($query1);
$query2 = $this->db->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->db->query($query2);
return $query;
}


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.

Any advice or assistance would be much appreciated.

Many thanks.

Answer

Use the following code. Generally anything which is given within the single quotes consider as a string, so it doesn't recognize the variables.

function get_where_custom_ordered_checked($col, $value, $order_by) {

    $query = $this->db->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;
}
Comments