Multiple Select Filter - AJAX

Question

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.

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Feb 29 '20 at 16:17

score 1 · Answer 1 · answered Feb 27 '20 at 18:11

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>

tried to run your code, now it's not showing any results, also I changed `$qname = "SELECT * FROM employees";` to `$qname = "SELECT name FROM employees";` but I still didn't get the results that I need — Richelle, Feb 28 '20 at 11:37
Well you might need to do some debugging, I just typed this off the top of my head. And it doesn't matter what you change the query to if you aren't running it. — miken32, Feb 28 '20 at 15:13

Wesley Smith · Answer 2 · 2020-02-27T13:28:19.977

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.

@Richelle Glad it helped, see the note I added at the bottom just as a heads up :) — Wesley Smith, Feb 27 '20 at 13:27
sorry, I must be very dizzy ;) I tried to run the code again and it still shows all the data on my table, and yes you're right I really need to put on a search button beside — Richelle, Feb 27 '20 at 13:36
@Richelle what do you get if you do `var_dump($_POST)` in `search.php`? — Wesley Smith, Feb 27 '20 at 14:21

Richelle · Answer 3 · 2020-03-02T13:46:17.200

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']; 
        }

Multiple Select Filter - AJAX

3 Answers3