Codesigniter Wählen Sie Abfrage mit linken Verknüpfungen und wo die Bedingungen keine Ergebnisse zurückgeben ⇐ Php

[Anonymous]

Ich versuche eine Frage zu machen, aber es zeigt mir keine Ergebnisse. Meine Tabellen sind: < /p>

Code: Select all

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

Ich möchte nur Schüler derselben Schule des Lehrers holen, aber nicht von diesem Lehrer gelehrt. /> Mein aktiver Datensatzversuch: < /p>

Code: Select all

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 (); Es zeigt mich:

Code: Select all

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

Dann habe ich eine rohe Abfrage ausprobiert und ein wobei () Methode Anruf:
angehängt

Code: Select all

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

, aber dieser zweite Versuch enthält nicht die Bedingung aus dem WHERE () Methodenaufruf.