我有一个数据表，它是一个表单提交的结果，表单有start_date和end_date，其类型为DATETIME-LOCAL。
在我的数据库中，它的DATETIME（不显示AM和PM），
我想在我的数据表中添加基于日期而非日期和时间的搜索功能，
基于type和NAME的其他类型的搜索正在工作，但日期不会显示，也不会出现错误和结果。
<form action="search.php" id="searchform" method="POST" class="searchbox-container">
<input type="text" id="searchbox" placeholder="Search" name="searchbox" class="searchbox" />
<input type="date" name="date">
<select name="select" id="select">
<option value="type">Type</option>
<option value="name">Name</option>
</select>
<input type="submit" name="search" class="searchbox-btn" value="Go" />
</form>
<?php
if(isset($_POST['search'])){
$search=preg_replace('#[^a-z 0-9?!]#i','',$_POST['searchbox']);
$user="admin";
$pass="neehahs";
$host="localhost";
$db_name="eventregisteration";
$con=mysqli_connect($host, $user, $pass, $db_name);
if(mysqli_connect_errno($con)){
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$date=$_POST['date'];
if($_POST['select']=="type"){
$sqlcommand="SELECT * FROM eventform WHERE event_type LIKE '%$search%'";
}
elseif($_POST['select']=="name"){
$sqlcommand="SELECT * FROM eventform WHERE event_name LIKE '%$search%'";
}
else {
if($date!=0)
$sqlcommand="SELECT * FROM eventform WHERE start_date='$date' ORDER BY start_date DESC";
}
$sqldata=mysqli_query($con,$sqlcommand)
or die("Error Getting Data");
$count=mysqli_num_rows($sqldata);
if($count!=0){
echo "<table border=2 bordercolor=#440000 cellpadding=2 bgcolor=#DDDDDD width=100%>";
echo "<tr bgcolor=#555555 style=font-size:18px align=center><th>Event Code</th><th>Event Name</th><th>Event Type</th><th>Event Level</th><th>Start Date</th>
<th>End Date</th><th>Point</th><th>Picture</th><th>Video</th><th>Description</th></tr>";
while($row=mysqli_fetch_array($sqldata)){
echo "<tr align=center><td>";
echo $row['event_code'];
echo "</td><td>";
echo $row['event_name'];
echo "</td><td>";
echo $row['event_type'];
echo "</td><td>";
echo $row['event_level'];
echo "</td><td>";
echo $row['start_date'];
echo "</td><td>";
echo $row['end_date'];
echo "</td><td>";
echo $row['points'];
echo "</td><td>";
echo "<a href=http://localhost/greenstudio/".$row['pic'].">".$row['pic']."</a>";
echo "</td><td>";
echo "<a href=http://localhost/greenstudio/".$row['video'].">".$row['video']."</a>";
echo "</td><td>";
echo $row['description'];
echo "</td></tr>";
}
echo "</table>";
}else{
echo "hi";
$search_output="<hr/>0 Results for<strong>$search</strong><hr/>";
}
}
?>
以索引友好的方式编写的查询可能看起来像这样的
SELECT *
FROM eventform
WHERE start_date >= '$date'
AND start_date < '$date' + INTERVAL 1 DAY
ORDER BY start_date DESC
其中$date应为YYYY-MM-DD格式（例如2013-11-27）
这是SQLFiddle演示
附带说明：由于您已经在使用mysqli，请考虑学习和使用准备好的语句，而不是插入查询字符串，从而使其对sql注入非常开放。