JoeM05 JoeM05 -3 years ago 87
MySQL Question

CodeIgniter MySQL query not working

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

Answer Source
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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download