我无法进行查询

Question

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 show me only these students that are in the school of the teacher and he doesn't teach them - I take the teacher from $this->uri->segment(3) adn school - from $this->uri->segment(4). It must show these students because admin could add them to the teacher. I don'w exactly know how to make my query to show me these students that are in the school of the teacher and he does not teach them. My attempt for the query is:

 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 

Now I used this 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 = 2 ");
   $this->db->where('teacher_student_conn.student_id', NULL);
    $result = $this->db->query($query);
    echo $this->db->last_query();
            return $result->result();   

}

I found the solution:

$teacher=$this->uri->segment(3);
   $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
    AND teacher_student_conn.teacher_id = $teacher
    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.student_id IS NULL");

  
    $result = $this->db->query($query);
    echo $this->db->last_query();
            return $result->result();   

}

</div>

Answer 1

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