我有一个使用php和MySQL的网站。在我的一个页面上，我有一个select查询，其中包含一些由表单传递的参数。在我的表格上有一个<select>框：
<select name="courseCode">
<option value="" selected>Show All</option>
<option value="1">Emergency First Aid</option>
<option value="2">Advanced Burns Training</option>
<option value="3">Fire Safety</option>
<option value="4">Environmental Awareness</option>
</select>
还有设置开始日期和结束日期的选项。
我面临的问题是在我的php中，当使用ShowAll选项时，它不会获取所有记录，我不确定写的最佳方式
<?php
$min = "2016-01-01"; // Default search criteria for
$max = "2016-12-31"; // date ranges to search
$code;
if (isset($_GET['submit'])) {
$min = $_GET['min'];
$max = $_GET['max'];
$code = $_GET['courseCode'];
}
$sql = "SELECT tblcourses.Course_Name, tblclasses.Class_OnDate, tblclasses.Class_TimeStart, tblclasses.Class_TimeFinish, Class_MaxAllowed
FROM tblcourses INNER JOIN tblclasses ON tblcourses.Course_Code = tblclasses.Course_Code
WHERE (tblclasses.Class_OnDate Between '$min' and '$max') AND (tblclasses.Course_Code = '$code')
ORDER BY tblclasses.Class_OnDate ASC;";
试试这个；）
<?php
$min = "2016-01-01"; // Default search criteria for
$max = "2016-12-31"; // date ranges to search
$code;
$filterWithCode = '';
if(isset($_GET['submit'])){
$min = $_GET['min'];
$max = $_GET['max'];
$code = $_GET['courseCode'];
if($code){
$filterWithCode = " AND (tblclasses.Course_Code = '$code') ";
}
}
$sql = "SELECT tblcourses.Course_Name, tblclasses.Class_OnDate, tblclasses.Class_TimeStart, tblclasses.Class_TimeFinish, Class_MaxAllowed
FROM tblcourses INNER JOIN tblclasses ON tblcourses.Course_Code = tblclasses.Course_Code
WHERE (tblclasses.Class_OnDate Between '$min' and '$max') $filterWithCode
ORDER BY tblclasses.Class_OnDate ASC;";
我认为最简单的方法是
if ($code == '')
$sql = "SELECT without WHERE";
else
$sql = "SELECT with WHERE";
顺便说一句，您确实应该作为一个准备好的语句来执行此操作，这意味着您需要使用mysqli或PDO（无论如何都应该这样做）
尝试
<?php
$min = "2016-01-01"; // Default search criteria for
$max = "2016-12-31"; // date ranges to search
$code;
if (isset($_GET['submit'])) {
$min = $_GET['min'];
$max = $_GET['max'];
$code = $_GET['courseCode'];
}
if($code){
$sql = "SELECT tblcourses.Course_Name, tblclasses.Class_OnDate, tblclasses.Class_TimeStart, tblclasses.Class_TimeFinish, Class_MaxAllowed
FROM tblcourses INNER JOIN tblclasses ON tblcourses.Course_Code = tblclasses.Course_Code
WHERE (tblclasses.Class_OnDate Between '$min' and '$max') AND (tblclasses.Course_Code = '$code')
ORDER BY tblclasses.Class_OnDate ASC;";
} else{
$sql = "SELECT tblcourses.Course_Name, tblclasses.Class_OnDate, tblclasses.Class_TimeStart, tblclasses.Class_TimeFinish, Class_MaxAllowed
FROM tblcourses INNER JOIN tblclasses ON tblcourses.Course_Code = tblclasses.Course_Code
ORDER BY tblclasses.Class_OnDate ASC;";
}
希望它能有所帮助：）