I'm trying to make a query, but it doesn't show me any results. My tables are:
TABLE teacher_student_conn:
id, teacher_id, student_id, created_at
TABLE users:
user_id, username, password, school_id, class, division, role_id, deactivated_at
TABLE schools:
school_id, school_name
I want to fetch only students from the same school of the teacher, but not being taught by that teacher.
I take the teacher from $this->uri->segment(3)
and school from $this->uri->segment(4)
.
I don't know exactly how to adjust my query.
My active record attempt:
public function select_students()
{
$this->db->select('users.user_id, users.username, users.school_id, users.class, users.division, users.role_id, schools.school_name, schools.region,class_divisions.division');
$this->db->from('users');
$this->db->join('teacher_student_conn', 'teacher_student_conn.student_id=users.user_id', 'left');
$this->db->join('teacher_student_conn AS T', 'T.teacher_id=users.user_id', 'left');
$this->db->join('class_divisions', 'class_divisions.id=users.division', 'left');
$this->db->join('schools', 'schools.school_id=users.school_id', 'left');
$this->db->where('(users.deactivated_at = "0000-00-00 00:00:00" OR users.deactivated_at IS NULL) AND users.role_id = 1 ');
$this->db->where('users.school_id', $this->uri->segment(4) );
$this->db->where('teacher_student_conn.teacher_id', $this->uri->segment(3) );
$this->db->where("teacher_student_conn.student_id IS NULL");
$result = $this->db->get();
return $result->result();
}
After echo $this->db->last_query();
it shows me:
SELECT users.user_id, users.username, users.school_id, users.class, users.division, users.role_id, schools.school_name, schools.region,class_divisions.division
FROM users
LEFT JOIN teacher_student_conn ON teacher_student_conn.student_id=users.user_id
LEFT JOIN teacher_student_conn as T ON T.teacher_id=users.user_id
LEFT JOIN class_divisions ON class_divisions.id=users.division
LEFT JOIN schools ON schools.school_id=users.school_id
WHERE (users.deactivated_at = '0000-00-00 00:00:00' OR users.deactivated_at IS NULL)
AND users.role_id = 1
AND users.school_id = 2
AND teacher_student_conn.teacher_id = 10
AND
Then I tried a raw query and appended a where()
method call:
public function select_students()
{
$query = ("SELECT users.user_id, users.username, users.school_id, users.class, users.division, users.role_id, schools.school_name, schools.region,class_divisions.division FROM users
LEFT JOIN teacher_student_conn ON teacher_student_conn.student_id=users.user_id
LEFT JOIN teacher_student_conn as T ON T.teacher_id=users.user_id
LEFT JOIN class_divisions ON class_divisions.id=users.division
LEFT JOIN schools ON schools.school_id=users.school_id
WHERE (users.deactivated_at = '0000-00-00 00:00:00' OR users.deactivated_at IS NULL) AND users.role_id = 1 AND
users.school_id = 2 ");
$this->db->where('teacher_student_conn.student_id', NULL);
$result = $this->db->query($query);
echo $this->db->last_query();
return $result->result();
}
But this second attempt doesn't include the condition from the where()
method call.
This is just sql query format print your query and paste in PHPmyadmin SQL or echo $this->db->last_query();.
public function select_students() {
$query = "SELECT users.user_id, users.username, users.school_id, users.class, users.division, users.role_id, schools.school_name, schools.region,class_divisions.division FROM users"
. "LEFT JOIN teacher_student_conn ON teacher_student_conn.student_id=users.user_id"
. "LEFT JOIN teacher_student_conn as T ON T.teacher_id=users.user_id"
. "LEFT JOIN class_divisions ON class_divisions.id=users.division"
. "LEFT JOIN schools ON schools.school_id=users.school_id"
. "WHERE (users.deactivated_at = '0000-00-00 00:00:00' OR users.deactivated_at IS NULL) AND users.role_id = 1 AND "
. "users.school_id = ".$this->uri->segment(4)." AND teacher_student_conn.teacher_id = ".$this->uri->segment(4)." AND teacher_student_conn.student_id IS NULL";
$result = $this->db->query($query);
return $result->result();
}