将选定的日期值传递给查询

Question

我在这里发现了类似的问题，但大多数解决方案对我无效。我有一个'jQuery UI日期选择器'的表单，我想在给定的日期获取搜索结果。我试过更改日期格式，但它不返回任何结果。

搜索形式 -

<div class="form-group row"> 
    <label for="duration" class="col-sm-4 col-form-label"> 
    <input type="checkbox" name="search2" value="checkbox" id="duration" class="search2"> Duration</label> 
    <div class="col-sm-4"> 
    <input type="date" class="form-control input-sm data2" name="data1" id="startdate" placeholder="Start Date" disabled="disabled"> 
    </div> 
    <div class="col-sm-4"> 
    <input type="date" class="form-control input-sm data2" name="data2" id="enddate" placeholder="End Date" disabled="disabled"> 
    </div> 
    </div> 

搜索查询 -

$sql = "SELECT * FROM reservation 
    INNER JOIN package ON reservation.tid = package.tid 
    INNER JOIN reservation_type ON reservation.type = reservation_type.id 
    INNER JOIN customer ON reservation.cid = customer.cid 
    INNER JOIN reservation_status ON reservation.status = reservation_status.id  
    WHERE " ; 
    if(isset($_POST['search2'])){ $start_date = date('Y-m-d', strtotime($_POST['data1'])); $sql .= " start_date = $start_date AND" ; } 
    if(isset($_POST['search2'])){ $end_date = date('Y-m-d', strtotime($_POST['data2'])); $sql .= " end_date = $end_date AND" ; } 
    if(isset($_POST['search'])){ $rid = mysqli_real_escape_string($db, $_POST['data']); $sql .= " rid = $rid AND" ; } 
    if(isset($_POST['search3'])){ $cid = mysqli_real_escape_string($db, $_POST['data3']); $sql .= " reservation.cid = $cid AND" ; } 
    if(isset($_POST['search5'])){ $tid = mysqli_real_escape_string($db, $_POST['data5']); $sql .= " reservation.tid = $tid AND" ; } 
    if(isset($_POST['search6'])){ $type = mysqli_real_escape_string($db, $_POST['data6']); $sql .= " reservation.type = $type AND" ; } 
    if(isset($_POST['search4'])){ $status = mysqli_real_escape_string($db, $_POST['data4']); $sql .= " status = $status AND" ; }  

    $sql = rtrim($sql, "AND") ; 


    $result = mysqli_query($db, $sql)or die("Error: ".mysqli_error($db)); 
    $num_rows = mysqli_num_rows($result); 
    if($num_rows >0){ 
    while ($row = mysqli_fetch_assoc($result)) { 
    ?> 
<tr align="center"> 
<td><?php echo $row["start_date"]; ?></td> 
<td><?php echo $row["end_date"]; ?></td> 

复选框选择 -

$(".search2").on('click', function() { 
if($(this).is(':checked')){ 
    $(".data2").prop('disabled',false); 
} else { 
    $(".data2").prop('disabled',true); 
} 
}) 

JqueryUI-

$(function() { 
          var dateFormat = "yy-mm-dd", 
           startdate = $("#startdate") 
           .datepicker({defaultDate: "+1w",changeMonth: true, numberOfMonths: 1}) 
           .on("change", function() {enddate.datepicker("option", "minDate", getDate(this));}), 
           enddate = $("#enddate") 
           .datepicker({defaultDate: "+1w",changeMonth: true,numberOfMonths: 1}) 
           .on("change", function() {startdate.datepicker("option", "maxDate", getDate(this));}); 

          function getDate(element) { 
           var date; 
           try { 
           date = $.datepicker.parseDate(dateFormat, element.value); 
           } catch(error) { 
           date = null; 
           } 
           return date; 
          } 
          }); 

       $('#startdate').datepicker({dateFormat: 'yy-mm-dd'}); 
       $('#enddate').datepicker({dateFormat: 'yy-mm-dd'}); 

查询正常工作与其他ckeckings（如下所述图像）; Search Result 1

但不适合的日期， Search Result 2

我是一名学生，新的编码。任何帮助表示赞赏！谢谢。

Answer 1

在您的查询，把单引号值： 如：

$sql .= " start_date = '$start_date' AND" ;

Answer 2

由于日进已经回答了，你缺少的日期的报价。然而，既然你表示你是编程新手，我强烈建议你已经熟悉使用准备好的语句，而不是你正在构建SQL查询的方式。这是更好地建立这样的代码：

$sql = "SELECT * FROM reservation 
    INNER JOIN package ON reservation.tid = package.tid 
    INNER JOIN reservation_type ON reservation.type = reservation_type.id 
    INNER JOIN customer ON reservation.cid = customer.cid 
    INNER JOIN reservation_status ON reservation.status = reservation_status.id"; 

$where = []; 

if(isset($_POST['search2'])){ 
    $start_date = date('Y-m-d', strtotime($_POST['data1'])); 
    $where["start_date = ?"] = $start_date; 

    $end_date = date('Y-m-d', strtotime($_POST['data2'])); 
    $where["end_date = ?"] = $end_date; 
} 
if(isset($_POST['search'])){ 
    $rid = mysqli_real_escape_string($db, $_POST['data']); 
    $where["rid = ?"] = $rid; 
} 

if(!empty($where)) { 
    $sql .= ' WHERE ' . implode(' AND ',array_keys($where)); 
} 


$stmt = $db->prepare($sql); 
if(!$stmt) { 
    trigger_error('An error occured: (' . $db->errno . ', ' . $db->error . '), sql: ' . $sql, E_USER_ERROR); 
} 

foreach($where as $value) { 
    /* Bind parameters. Types: s = string, i = integer, d = double, b = blob */ 
    $stmt->bind_param('s', $value); 
} 

$stmt->execute(); 

关于为什么以及如何陈述准备应该用更多信息，请参阅How can I prevent SQL injection in PHP?。