Nimesha Dewapriya Nimesha Dewapriya - 1 month ago 7
MySQL Question

Passing selected date value to a query

I found similar questions here but most of the solutions don't work for me. I have a form with 'jquery UI date picker' and I want to get the search results on given date. I tried changing date formats but it doesn't return any result.

Search Form-

<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>


Search Query-

$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>


Checkbox Selection-

$(".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'});


Query works fine with the other ckeckings (as the image below);
Search Result 1

But not for the date,
Search Result 2

I am a student and new to coding. Any help is appreciated! Thank you.

Answer

In your query, put single quotes for values : Eg :

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