</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>
This is happening because the jquery code is looking for the input incorrectly.
Specifically, all the values are using $(this).val(); which would only work for the one that was actually just changed, not the other 2.
Also, $('#name'),$('#month'),$('#year').change( is not a valid syntax for attaching a handler to multiple elements .
try this:
$(document).ready(function(){
$('#name, #month, #year').change(function(){
var name = $('#name').val();
var month = $('#month').val() ;
var year = $('#year').val();
$.ajax({
url:"search.php",
method:"POST",
data:{name:name,month:month,year:year},
success:function(data){
$('#show_data').html(data);
}
});
});
});
As a side note, doing an ajax request each time any of the boxes change might be detrimental to your user experience. when users quickly select from multiple boxes, itll send off multiple ajax requests (one for each changed element) and you cant count on those responses always coming back in the same order. You might do better to have a button thats clicked after all selections are made.
Sticking to your immediate problem:
$qname = "SELECT * FROM employees";
$valid_nm = array($qname);
if (in_array($_POST['name'], $valid_nm))
Do you have any employees named "SELECT * FROM employees"? Because that's what you're checking for.
If you want to fix up this code more completely, I'd suggest something like this for Javascript, where you are serializing the form and sending the relevant values, instead of setting them all to the same value for some reason:
$(document).ready(function(){
$('#name, #month, #year').change(function() {
var form = this.closest("form");
$.post("search.php", form.serialize(), function(data) {
$('#show_data').html(data);
});
});
});
Then in your PHP, start by using PDO which is less verbose and provides a more modern syntax. Use prepared statements for security and don't waste your resources doing checks for "valid" data. Break out of PHP for long sections of HTML using alternative syntax and short echo tags to keep things neat. And always escape output for HTML.
<?php
$db = new \PDO("mysql:host=localhost;dbname=test", "root", "");
$sql = 'SELECT name, timeIn, timeOut, MONTH(timeIn) AS month, YEAR(timeIn) AS year,
TIMESTAMPDIFF(MINUTE, timeIn, timeOut)/60 AS total_hrs
FROM attendance
WHERE ';
$where = ["TRUE"];
$params = [];
if (isset($_POST["name"])) {
$where[] = "name = ?";
$params[] = $_POST["name"];
}
if (isset($_POST["month"])) {
$where[] = "month = ?";
$params[] = $_POST["month"];
}
if (isset($_POST["year"])) {
$where[] = "year = ?";
$params[] = $_POST["year"];
}
$sql .= implode(" AND ", $where);
$stmt = $db->prepare($sql);
$stmt->execute($params);
$data = $stmt->fetchAll(\PDO::FETCH_ASSOC);
/*
honestly, you should just be returning json_encode($data) here
the presentation of the data as a table doesn't belong here
*/
if (count($data) === 0) {
echo '<p class="alert">No results found</p>';
exit;
}
?>
<div class="fixed-header col-sm-12">
<table class="table table-hover table-sm">
<thead class="thead-dark">
<th style="width:15%; text-align:center;">Day</th>
<th style="width:25%; text-align:center;">Date</th>
<th style="width:20%; text-align:center;">Time In</th>
<th style="width:20%; text-align:center;">Time Out</th>
<th style="width:20%; text-align:center;">Total Hours</th>
</thead>
<tbody>
<?php foreach($data as $row): ?>
<tr>
<td style="width:15%; text-align:center;"><?= htmlspecialchars(date('l', strtotime($row["timeIn"]))) ?></td>
<td style="width:25%; text-align:center;"><?= htmlspecialchars(date('d-M-Y', strtotime($row["timeIn"]))) ?></td>
<td style="width:20%; text-align:center;"><?= htmlspecialchars(date('h:i A', strtotime($row["timeIn"]))) ?></td>
<?php if($row["timeOut"] < 0): ?>
<td style="width:20%; text-align:center">NA</td>
<td style="width:20%; text-align:center; color:red">NA</td>
<?php else: ?>
<td style="width:20%; text-align:center;"><?= htmlspecialchars(date('h:i A', strtotime($row["timeOut"]))) ?></td>
<td style="width:20%; text-align:center;"><?= htmlspecialchars(number_format($row['total_hrs'],2)) ?></td>
<?php endif; ?>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
So I realized the reason why I am getting ALL the data. I haven't fetch the array for name and year properly. Here's my updated code below that worked:
$query_nm = "SELECT name FROM employees";
$result_nm = mysqli_query($connect,$query_nm);
$valid_nm = array();
while($row = mysqli_fetch_assoc($result_nm)) {
$valid_nm[] = $row['name'];
}
$valid_mo = array('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12');
$query_yr = "SELECT year(timeIn) FROM attendance GROUP BY year";
$result_yr = mysqli_query($connect,$query_yr);
$valid_yr = array();
while($row = mysqli_fetch_assoc($result_yr)) {
$valid_yr[] = $row['year'];
}