php - 使用 PHPmyadmin 和 SQL 检查计划可用性的方法和算法

Question

更新

我正在尝试为所有学生在最后一年创建一个基本时间表，以便向 2 位主管展示他们的最终项目。我已经成功创建了一个没有限制的时间表，但现在我需要根据主管的可用性创建一个时间表。

这是问题的详细描述。

一名学生被分配一名主管，一名主管将监督一名以上的学生。主管也在白天上课。现在我需要为所有学生创建一个时间表，以向他们的主管和一名监督其他学生的额外主管展示。（目前我专注于分配给学生的主管，而不是第二个，直到我开始工作。

我想比较主管的空闲时间是否有空，然后将它们分配给插槽，然后将该时间的可用性更新为 false 以避免使用 PHP sql 查询进行重复预订。

所以...

到目前为止我做了什么：

// get all of the Slots. 
    $sql = "SELECT Slot_ID  FROM slot WHERE  StartTime < '18:00:00'"; 
    $result = mysqli_query($con, $sql);
    $DayTimeSlots = array(); 
    if(mysqli_num_rows($result) > 0){
        while($row = mysqli_fetch_assoc($result)){

            $DayTimeSlots [] = $row;

        }
    }

// Store slots into simple array 

    function extractSlotId($DayTimeSlots){
    return $DayTimeSlots['Slot_ID'];

    }

    $slots = array_map("extractSlotId",$DayTimeSlots);

    // SHOW SLOTS
    foreach ($slots as $slotID) {
    echo " SlotID:  = $slotID <br>"; 

    }

    // Get All students 


    $sql = "SELECT Student_ID FROM student WHERE PartTime =0"; 
    $result = mysqli_query($con, $sql);
    $FullTimeStudents = array(); 
    if(mysqli_num_rows($result) > 0){
        while($row = mysqli_fetch_assoc($result)){

            $FullTimeStudents [] = $row;

        }
    }


  // Store into a simple array 


    // Extract student id and Supervisor_ID 

    function extractStudentId($FullTimeStudents){
    return $FullTimeStudents['Student_ID'];

    }

    $students = array_map("extractStudentId",$FullTimeStudents);


    // Combine the Slot and Students array 

    $min = min(count($FullTimeStudents), count($DayTimeSlots));
    $result = array_combine(array_slice($students , 0, $min), array_slice($slots, 0, $min));

     foreach($result as $key=>$value){ // $Key = Student_ID, $value = Slot_ID
        echo " $key : $value "; 

        // get supervisor ID 

    $sql = "select Supervisor_ID FROM student where Student_ID = $key";
    $query = mysqli_query($con, $sql);
    $row = mysqli_fetch_array($query); 
    $SuperID = $row['Supervisor_ID'];
    echo  "SuperID : $SuperID "; 

    // get slotID 

    $sql = "select Date, StartTime FROM Slot where Slot_ID = $value";
    $query = mysqli_query($con, $sql);
    $row = mysqli_fetch_array($query); 
    $Date = $row['Date'];
    $StartTime = $row['StartTime'];
    echo  "Slot Date : $Date Start Time : $StartTime "; 

        // get Date id 

    $sql = "select Date_ID FROM dates where Date = '$Date'";
    $query = mysqli_query($con, $sql);
    $row = mysqli_fetch_array($query); 
    $DateID = $row['Date_ID'];
    echo  "Date ID : $DateID "; 

    // Check if the supervisor is available 

    $sql = "select `$StartTime` FROM supervisor_availability where Date_ID = $DateID AND Supervisor_ID = $SuperID";
    $query = mysqli_query($con, $sql);
    $row = mysqli_fetch_array($query); 
    $Available = $row["$StartTime"];
    echo  "Is the Lecture Available?  : $Available  "; 
    $Time = "`$StartTime`"; 

    if($Available == 1){

    $sql = "INSERT INTO student_slot (Student_ID, Slot_ID) VALUES ($key, $value)";
    $result = mysqli_query($con, $sql);
    $sql = "UPDATE `supervisor_availability` SET $Time = '0' WHERE `supervisor_availability`.`Supervisor_ID` = $SuperID AND `supervisor_availability`.`Date_ID` = $DateID" ;
    $result = mysqli_query($con, $sql); 


    } else if ($Available == 0) {

        // Not sure what to do here. 
        // Can i make the Slot it's currently checking go to the end of the 
        // array for a different student and then check the next slot and come 
        // back to it.  
    }
     }

我正在使用 echo 进行调试。

如果主管可用，该算法可以正常工作，它会正确分配它，然后更新该时隙的主管可用性

如果他们不可用，只需要帮助如何处理它。

任何帮助或建议将不胜感激。

Supervisor_Availability 插槽表 学生表 日期表 DB_Structure

标签： phpsqlalgorithm