</div>
</div>
</div>
<hr class="my12 outline-none baw0 bb bc-powder-2">
<div class="grid fw-nowrap fc-black-600">
<div class="grid--cell mr8">
<svg aria-hidden="true" class="svg-icon iconLightbulb" width="18" height="18" viewbox="0 0 18 18"><path d="M9.5.5a.5.5 0 0 0-1 0v.25a.5.5 0 0 0 1 0V.5zm5.6 2.1a.5.5 0 0 0-.7-.7l-.25.25a.5.5 0 0 0 .7.7l.25-.25zM1 7.5c0-.28.22-.5.5-.5H2a.5.5 0 0 1 0 1h-.5a.5.5 0 0 1-.5-.5zm14.5 0c0-.28.22-.5.5-.5h.5a.5.5 0 0 1 0 1H16a.5.5 0 0 1-.5-.5zM2.9 1.9c.2-.2.5-.2.7 0l.25.25a.5.5 0 1 1-.7.7L2.9 2.6a.5.5 0 0 1 0-.7z" fill-opacity=".4"></path><path opacity=".4" d="M7 16h4v1a1 1 0 0 1-1 1H8a1 1 0 0 1-1-1v-1z" fill="#3F3F3F"></path><path d="M15 8a6 6 0 0 1-3.5 5.46V14a1 1 0 0 1-1 1h-3a1 1 0 0 1-1-1v-.54A6 6 0 1 1 15 8zm-4.15-3.85a.5.5 0 0 0-.7.7l2 2a.5.5 0 0 0 .7-.7l-2-2z" fill="#FFC166"></path></svg>
</div>
<div class="grid--cell lh-md">
<p class="mb0">
<b>Want to improve this question?</b> <a href="/posts/60434018/edit">Update the question</a> so it's <a href="/help/on-topic">on-topic</a> for Stack Overflow.
</p>
<p class="mb0 mt6">Closed <span title="2020-02-29 19:25:08Z" class="relativetime">last month</span>.</p>
</div>
</div>
</aside>
I wanted to create a form where a user can search the records of a certain person with a specific month. Here's my form:
<div class="form-row">
<div class="col-sm-4">
<select class="form-control" name="name" id="name">
<option selected="selected" style="display:none" value="">Select Employee</option>
<?php echo fill_employees($connect); ?> <!---option list--->
</select>
</div>
<div class="col-sm-2">
<select class="form-control" name="month" id="month">
<option selected="selected" style="display:none" value="0">Month</option>
<option value="1">January</option>
<option value="2">February</option>
<option value="3">March</option>
<option value="4">April</option>
<option value="5">May</option>
<option value="6">June</option>
<option value="7">July</option>
<option value="8">August</option>
<option value="9">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>
</div>
<div class="col-sm-2">
<select class="form-control" name="year" id="year">
<option selected="selected" style="display:none" value="">Year</option>
<?php echo fill_year($connect); ?> <!---option list--->
</select>
</div>
</div>
<div class="row" id="show_data">Search Results</div>
Below is my script:
<script>
$(document).ready(function(){
$('#name'),$('#month'),$('#year').change(function(){
var name = $(this).val();
var month = $(this).val() ;
var year = $(this).val();
$.ajax({
url:"search.php",
method:"POST",
data:{name:name,month:month,year:year},
success:function(data){
$('#show_data').html(data);
}
});
});
});
</script>
and here is my search.php file
<?php
$connect = mysqli_connect("localhost", "root", "", "test");
$output = '';
echo '<div class="fixed-header col-sm-12">';
echo '<table class="table table-hover table-sm">';
echo '<thead class="thead-dark">';
echo '<th style="width:15%; text-align:center;">Day</th>';
echo '<th style="width:25%; text-align:center;">Date</th>';
echo '<th style="width:20%; text-align:center;">Time In</th>';
echo '<th style="width:20%; text-align:center;">Time Out</th>';
echo '<th style="width:20%; text-align:center;">Total Hours</th>';
echo '</thead>';
$qname = "SELECT * FROM employees";
$valid_nm = array($qname);
$valid_mo = array('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12');
$qyear = "SELECT year(timeIn) FROM attendance GROUP BY year";
$valid_yr = array($qyear);
$q = "SELECT name, timeIn, timeOut,
date(timeIn) as date,
month(timeIn) as month,
year(timeIn) as year,
TIMESTAMPDIFF(MINUTE, timeIn, timeOut)/60 AS total_hrs
FROM attendance";
// initialize array for WHERE clause conditions
$where = array('TRUE');
if (in_array($_POST['name'], $valid_nm))
{
$where[] = 'name = "' . $_POST['name'] . '"';
}
if (in_array($_POST['month'], $valid_mo))
{
$where[] = 'month(timeIn) = "' . $_POST['month'] . '"';
}
if (in_array($_POST['year'], $valid_yr))
{
$where[] = 'year(timeIn) = "' . $_POST['year'] . '"';
}
$output = '';
$sql = 'SELECT name, timeIn, timeOut,
date(timeIn) as date,
month(timeIn) as month,
year(timeIn) as year,
TIMESTAMPDIFF(MINUTE, timeIn, timeOut)/60 AS total_hrs
FROM attendance
WHERE ' . implode(' AND ', $where);
$result = mysqli_query($connect, $sql);
while ($row = mysqli_fetch_array($result))
{
$output .= '<tr>';
$output .= '<td style="width:15%; text-align:center;">'. date('l', strtotime($row["timeIn"])) .'</td>';
$output .= '<td style="width:25%; text-align:center;">'. date('d-M-Y', strtotime($row["timeIn"])) .'</td>';
$output .= '<td style="width:20%; text-align:center;">'. date('h:i A', strtotime($row["timeIn"])) .'</td>';
$int = strtotime($row["timeOut"]);
if ($int < 0)
{
$output .= '<td style="width:20%; text-align:center">NA</td>';
$output .= '<td style="width:20%; text-align:center; color:red">NA</td>';
} else {
$output .= '<td style="width:20%; text-align:center;">'. date('h:i A', strtotime($row["timeOut"])) .'</td>';
$output .= '<td style="width:20%; text-align:center;">'. number_format($row['total_hrs'],2) .'</td>';
};
$output .= '</tr>';
}
echo $output;
echo '</table>';
echo '</div>';
?>
It supposedly needs to show the results of a certain person and selected month and year but instead, it is showing ALL the data on my table. I think the mistake is on my criteria but I don't know where exactly. This is my first attempt at AJAX.
</div>