"No tables used" error in CodeIgniter when calling query(), then get()

Question

I'm trying to get this query to work in CodeIgniter but it's spitting out an error:

A Database Error Occurred Error Number: 1096

No tables used

SELECT *

If I put the query directly into MySQL it works fine (I replace the $variables with values). Here is the query as input into codeigniter:

$this->db->query(" 
    SELECT * 
    FROM writing_quests 
    LEFT OUTER JOIN members_quests_completed 
    ON members_quests_completed.quest_id = writing_quests.id 
    WHERE writing_quests.level_required <=  $userlevel 
    AND ( 
        members_quests_completed.user_id = $user_id
        OR 
        members_quests_completed.user_id IS NULL )"
);
$query = $this->db->get();

Am I doing something that is wrong and I am missing it? I've included the whole function call in case the problem is somewhere else? I've done this exact thing many times without an issue.

function get_all_quests_for_user() {
    $user_id = $this->session->userdata('user_id');
    $userlevel = $this->session->userdata('user_level');

    $this->db->query("SELECT writing_quests.id, writing_quests.points_availible, writing_quests.name, writing_quests.note, writing_quests.instructions, writing_quests.time_limit, members_quests_completed.location_completed, members_quests_completed.status  FROM writing_quests LEFT OUTER JOIN members_quests_completed ON members_quests_completed.quest_id = writing_quests.id WHERE writing_quests.level_required <=  '$userlevel' AND writing_quests.unlocked = 1 AND ( members_quests_completed.user_id = '$user_id' OR members_quests_completed.user_id IS NULL )");
    $query = $this->db->get();
    $this->db->last_query();
    return $query->result();
}

Froh

Perhaps CI's query() method doesn't like line breaks. – Eweneck

Thanks BoltClock. I've atcually tried it without. I also have queries written with line breaks that do work. – Froh

Answer 1

SELECT writing_quests.*, ( put needed fields from members_quests_completed ) 
FROM writing_quests
LEFT JOIN members_quests_completed ....

same fields names corrupting results column's names in result object

FYI it not wise to use *, you should have list of fields...
and what is most important:

$result = $this->db->query ( ... );

$this->db->query ( ... ) is good when INSERT or UPDATE (mostly), when you pulling data assign results to variable: http://codeigniter.com/user_guide/database/results.html

Underpainting

Added a field list, same result, same error. This works fine in mysql command line. – Froh

yes, it does work fine in mysql. This is why I'm so confused. – Froh

second part of answer handle CI part – Underpainting

Answer 2

I think it should be like this:

$query = $this->db->query(" 
SELECT * 
FROM writing_quests 
LEFT OUTER JOIN members_quests_completed 
ON members_quests_completed.quest_id = writing_quests.id 
WHERE writing_quests.level_required <=  $userlevel 
AND ( 
    members_quests_completed.user_id = $user_id
    OR 
    members_quests_completed.user_id IS NULL )"
);

Or:

$query = $this->db->get('mytable');

Socman

Answer 3

The returned error doesn't have anything to do with query(). query() is not part of the query builder class.

query() executes the raw SQL string that you've supplied it (but you never attempted to fetch the result set (using a method like ->result()).

The problem comes when you call get() with no parameter. get() executes the active record including all query components created up to that point (SELECT * is applied by default).

If you add a table name string as get()'s first parameter, the error will disappear, but the new query will be SELECT * FROM your_table and the fetched result set will have nothing to do with your raw SQL string.

You should either omit the get() call and chain a result set fetching method to query() or refactor your raw SQL to completely adopt query building methods.

Scramble