在PHP函数中加入SQL中的5个表

Question

What I'm trying to return in a set: The lessonIDs associated to the userID and schoolID within a given startTime and endTime.

This is to be displayed on a calendar. I only want to display the lessons that are associated to the user viewing it for a selected school within the time range they are viewing on the calendar.

This data is just dummy data to show the table structure and data types.

Lesson Table:

lessonID  roomFK    startTime                 endTime
   1        2      2014-08-01 13:00:00   2014-08-01 14:00:00 
   2        3      2014-08-01 13:00:00   2014-08-01 14:00:00 

Room Table:

roomID   schoolFK
1            1
2            1
3            2
4            2

School Table:

schoolID 
   1
   2

User Table:

userID
  1
  2
  3

Lesson/Teacher Junction Table:

lessonFK    teacherFK
    1          1
    1          2
    2          3

What I've tried:

function find_lessons_by_school_id_and_teacher_id_ranged_feed($school_id, $teacher_id, $start, $end) {
    global $connection;

    $school_id = mysqli_real_escape_string($connection, $school_id);
    $teacher_id = mysqli_real_escape_string($connection, $teacher_id);
    $start = mysqli_real_escape_string($connection, $start);
    $end = mysqli_real_escape_string($connection, $end);

    $start = strftime("%Y-%m-%d %H:%M:%S", $start);
    $end = strftime("%Y-%m-%d %H:%M:%S", $end);

    $query = "SELECT * ";
    $query .= "FROM lesson ";
    $query .= "JOIN room ON lesson.roomFK = room.roomID ";
    $query .= "JOIN school ON room.schoolFK = school.schoolID ";
    $query .= "JOIN user ON user.userID = junc_lesson_teacher.teacherFK ";
    $query .= "JOIN junc_lesson_teacher ON junc_lesson_teacher.lessonFK = lesson.lessonID ";
    $query .= "WHERE room.schoolFK = '{$school_id}' ";
    $query .= "AND lesson.startTime >= '{$start}' ";
    $query .= "AND lesson.endTime <= '{$end}' ";
    $query .= "AND user.userID = '{$teacher_id}' ";
    $query .= "ORDER BY lesson.roomFK ASC";

    $set = mysqli_query($connection, $query);


    return $set;

}

When I run this, mySQL complains about the query statement.

Any input on how to do this properly would be greatly appreciated! Thanks!

Answer 1

The order of your join operations is wrong. You've got to swap the last two ones. Instead of

$query .= "FROM lesson ";
$query .= "JOIN room ON lesson.roomFK = room.roomID ";
$query .= "JOIN school ON room.schoolFK = school.schoolID ";
$query .= "JOIN user ON user.userID = junc_lesson_teacher.teacherFK ";
$query .= "JOIN junc_lesson_teacher ON junc_lesson_teacher.lessonFK = lesson.lessonID ";

write

$query .= "FROM lesson ";
$query .= "JOIN room ON lesson.roomFK = room.roomID ";
$query .= "JOIN school ON room.schoolFK = school.schoolID ";
$query .= "JOIN junc_lesson_teacher ON junc_lesson_teacher.lessonFK = lesson.lessonID ";
$query .= "JOIN user ON user.userID = junc_lesson_teacher.teacherFK ";

because every table you use in a join must be introduced first. By your statement you can't use junc_lesson_teacher to join the user table, so you've got to join junc_lesson_teacher before you join user. Think of your tables as a chain without interrupts.