我有一个相当棘手的搜索功能，它允许用户搜索一个或多个变量：
$where = [];
if( isset( $_POST['destination_search'] )){
$where[] = "`destination` LIKE '%" . mysql_real_escape_string( $_POST['destination_search'] ) . "%'";
}
if( isset( $_POST['cargo_search'] )){
$where[] = "`cargo` LIKE '%" . mysql_real_escape_string( $_POST['cargo_search'] ) . "%'";
}
if( isset( $_POST['dueDate_search_last'] )){
$where[] = "`delivery_date` LIKE '%" . mysql_real_escape_string( $_POST['dueDate_search_first'] ) . "%'";
}
$whereStatement = "";
if( count($where) > 0 )
{
$whereStatement = "AND" . implode( " AND ", $where );
}
$query_Recordset_table = "
SELECT *
FROM `jobs` WHERE bid_deadline >= CURDATE() AND status != 'Offer accepted'
" . $whereStatement . "
ORDER BY `destination` ASC";
用户以以下形式输入搜索词：
<form id="form_searchJobs" name="form1" method="post" action="">
<input name="destination_search" type="text" class="bb-input bb-input-large" placeholder="Destination"> <br>
<input type="text" name="cargo_search" placeholder="Cargo" class="bb-input bb-input-large"> <br>
<input name="dueDate_search_first" type="text" id="Due_date_from" class="bb-input bb-input-small" placeholder="Due Date (from)"/> <input name="dueDate_search_last" type="text" id="Due_date_to" class="bb-input bb-input-small" placeholder="Due Date (to)"/>
<input type="submit" name="search" id="search" class="button3" value="Search" />
</form>
为了在日期之间进行搜索，我尝试了以下搜索查询：
if( isset( $_POST['dueDate_search_first'], $_POST['dueDate_search_last'] )){
$where[] = "`delivery_date` BETWEEN '%" . mysql_real_escape_string( $_POST['dueDate_search_first'] ) . "%' AND '%" . mysql_real_escape_string( $_POST['dueDate_search_last'] ) . "%'";
}
但它们不会返回任何内容，而且还会阻止其他搜索变量工作。我在查询之间的搜索有什么问题？
这没有意义：
`delivery_date` BETWEEN '%" . mysql_real_escape_string( $_POST['dueDate_search_first'] ) . "%' AND '%" . mysql_real_escape_string( $_POST['dueDate_search_last'] ) . "%'";
%与LIKE一起使用。。你不能把它扔到任何你想扔的地方。
接下来，当使用BETWEEN时，您必须将字符串转换为MySQL能够理解的日期或时间。如果你让用户自由键入日期，很可能他们不会键入MySQL能理解的内容。
我通常的做法是，将日期作为时间戳存储在数据库中，为表单使用某种日历选择器JS库，然后使用PHP的strtotime()或其他东西将这些日期转换为时间戳。然后您可以在查询中使用这些时间戳。
$curdate = date('Y-m-d h:i:s');
$query_Recordset_table = "
SELECT * FROM `jobs` WHERE bid_deadline >= $curdate AND status != 'Offer accepted'
" . $whereStatement . "
ORDER BY `destination` ASC";