如何突破sql查询无限循环

问题描述：

running this outputs every result from 100 to 165 times. Seems like an infinite loop, but i've got no clue how to break out of it or identify the problem. Any help is appreciated.

    $sql = "SELECT *
FROM timetable t, class c, room r
WHERE c.id = t.class AND t.week = '".$_GET['week']."' AND t.id = '".$_GET['class']."' 
ORDER BY CASE
          WHEN Day = 'Sunday' THEN 1
          WHEN Day = 'Monday' THEN 2
          WHEN Day = 'Tuesday' THEN 3
          WHEN Day = 'Wednesday' THEN 4
          WHEN Day = 'Thursday' THEN 5
          WHEN Day = 'Friday' THEN 6
          WHEN Day = 'Saturday' THEN 7
      ELSE Day
     END ASC, Hour;
";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "Timetable of the class: ".$_GET['class']."";
    echo "<table>";
    while($row = $result->fetch_assoc()) {


        echo "<tr>";
        echo "<td>" . $dan . "</td><td>". $row["ura"] . "</td><td>". $row["razred"] . "</td><td>" . $ucilnica . "</td><td>" . $row["ucitelj"] . "</td>";
        echo "</tr>";
        }
    echo "</table>";
} else {
    echo "0 results";
}
$conn->close();
}
?>

答

You are missing the link to the room table, so it is selecting all rooms for each combination.

SELECT *
FROM timetable t, class c, room r
WHERE r.? = ?.? AND c.id = t.class AND t.week ...

You can also try the new JOIN notation...

SELECT *
FROM timetable t
JOIN class c ON c.id = t.class
JOIN room r ON ?
WHERE t.week...

I would also suggest looking into prepared statements to protect against SQL injection etc.

如何突破sql查询无限循环

相关推荐