我如何让每部电影在各自的日期下出现

Question

我似乎无法让每部电影都出现在各自的日期下，并为电影时间选择提供正确的时间下拉菜单

$sql_date = "SELECT DISTINCT sDate, sTitle FROM movieScreenings ";
$result_date = mysqli_query($db, $sql_date);
while($row = mysqli_fetch_array($result_date)) {
echo "<h2>" . $row['sDate'] . "</h2>";
$sql_movie = "SELECT * FROM movieList, movieScreenings WHERE title = '" . $row['sTitle'] . "'";
$result_movie = mysqli_query($db, $sql_movie);
while($row2 = mysqli_fetch_array($result_movie)) {

echo "<div class='box'>
<img class='poster' src='posters/" . $row2['poster'] . "'/>       
<h2>" . $row2['title'] . "</h2>
<p>" . $row2['description'] . "</p>";

$sql_time = "SELECT DISTINCT sTime FROM movieScreenings WHERE sTitle = '" . $row2['title'] . "' AND sDate = '" . $row['sDate'] . "'";
$result_time = mysqli_query($db, $sql_time);
while($row3 = mysqli_fetch_array($result_time)) {
echo "<select name='sTime'>
<option value='" . $row3['sTime'] . "'>" . $row3['sTime'] . "</option>
</select>";
echo "</div>";
}
}
}
mysqli_free_result($result_date);
mysqli_free_result($result_movie);
mysqli_free_result($result_time);
// Close connection
mysqli_close($db);

Answer 1

您可能想用ORDER BY sDate按sDate对第一个查询进行排序。

在第二个查询中，您再次从movieScreenings中进行选择，这是不必要的，因为我认为您访问的所有数据(海报、标题、描述(都存储在电影列表。

第三，在最后一个while循环中，您将在每个放映时间重复<select>标签。这将导致多个下拉输入。此外，您将在这个循环中包含结束</div>标记。所以只循环<option>标签。

有了这些更改，您的代码将如下所示：

$sql_date = "SELECT DISTINCT sDate, sTitle FROM movieScreenings ORDER BY sDate ASC";
$result_date = mysqli_query($db, $sql_date);
while($row = mysqli_fetch_array($result_date)) {
echo "<h2>" . $row['sDate'] . "</h2>";
$sql_movie = "SELECT * FROM movieList WHERE title = '" . $row['sTitle'] . "'";
$result_movie = mysqli_query($db, $sql_movie);
while($row2 = mysqli_fetch_array($result_movie)) {
echo "<div class='box'>
<img class='poster' src='posters/" . $row2['poster'] . "'/>       
<h2>" . $row2['title'] . "</h2>
<p>" . $row2['description'] . "</p>";
$sql_time = "SELECT DISTINCT sTime FROM movieScreenings WHERE sTitle = '" . $row2['title'] . "' AND sDate = '" . $row['sDate'] . "'";
$result_time = mysqli_query($db, $sql_time);
echo "<select name='sTime'>";
while($row3 = mysqli_fetch_array($result_time)) {
echo "<option value='" . $row3['sTime'] . "'>" . $row3['sTime'] . "</option>";
}
echo "</select>";
echo "</div>";
}
}
...

最后，我建议使用ID作为您的外键，以防止将来重复。可能有多部电影的标题相同。