好的,这是我的问题。我想进行一个查询,该查询将我表的所有行都占用其中用户输入我的表单的日期根本不堆叠。
我有一个包含两个日期字段的表单:
<!DOCTYPE html>
<head>
<title>Ha</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" href="jquery-ui.css">
<script src="jquery.min.js" type="text/javascript" ></script>
<script src="jquery-ui.js"></script>
<script>
$(function() {
$( "#datepicker" ).datepicker();
});
$(function() {
$( "#datepicker1" ).datepicker();
});
</script>
</head>
<body>
<form method="POST" action="cars.php">
<fieldset>
<legend>Rent A Car</legend>
<div class='ddate'><p>Дата на наемане:<input type="date" id="datepicker" name="d1"></p></div>
<div class='ddate'><p>Дата на връщане:<input type="date" id="datepicker1" name="d2"></p></div>
<input type="submit" value="Провери" name="submit"/>
</fieldset>
</form>
</body>
</html>
我有一个查询,我认为问题是日期格式。
<?php
//Connection
include "connect.php";
$d1 = $_POST['d1'];
$d2 = $_POST['d2'];
//Query
$sql = "SELECT cars.brand,cars.model,cars.reg_num,cars.horse_powers,cars.color FROM cars INNER JOIN clients_cars ON cars.id=clients_cars.cars_id WHERE cars.id IN
(SELECT DISTINCT clients_cars.cars_id FROM clients_cars WHERE (rent_date not between '$d1' AND '$d2') AND (return_date not between '$d1' AND '$d2') AND ((return_date < '$d1') OR (rent_date > '$d2')))";
$result = mysqli_query($con, $sql);
echo mysqli_error($con);
while ($row= mysqli_fetch_array($result)){
$brand = $row['brand'];
$model = $row['model'];
$reg_num = $row['reg_num'];
$horse_powers = $row['horse_powers'];
$color = $row['color'];
echo $brand;
}
?>
当我提交此查询时,无论我在日期输入字段中选择的参数如何,它都会带我出"品牌"列的所有行。
我应该怎么做?也许我需要更改日期类型?或者我不知道...看起来查询是正确的,它的工作,但它不关心 $d 1 和 $d 2 请有人可以帮助我?
假设您的输入格式为 mm/dd/yyyy.
您需要使用 date(
) 转换为 mysql 格式并strtotime
.
$d1 = date("Y-m-d", strtotime($_POST['d1']));
$d2 = date("Y-m-d", strtotime($_POST['d2']));