How to add Dynamic filter using PHP MySQL AJAX

Question

I am trying to get data from mysql table using php ajax. it showing when I select the filter from select option but when I change the select another value for filter it does not reset the table but it append new result with old result. when page load it should not filter it should filter when select filter option.

My PHP to get data from data base.

<?php
if(!empty($_POST["assign_to"])){
    $filter = $_POST["assign_to"];
    $sql = "SELECT * FROM projects WHERE assign_to ='".$filter ."' ";
    $result = mysqli_query($connect, $sql);
    $output = array();
    while($row = mysqli_fetch_assoc($result))
    {
     $output[] = $row;

    }
    echo json_encode($output);
}

Filter HTML

<select id="user_ids" class="form-control" name="assign_to" required="">
    <option value="">Select User</option>
    <option value="85">Manager</option>
    <option value="86">User 1</option>
    <option value="87">User 2</option>
    <option value="88">User 3</option>
</select>

Script for AJAX Call

function fetch_project_data_filtered() {
       $("#user_ids").change(function(){ 
          var assign_to = $(this).val(); 
          var dataString = "assign_to="+assign_to; 
          //alert(assign_to);

          $.ajax({ 
            type: "POST", 
            url: "x-fetch.php",
            data: dataString, 
            dataType:"json",
            success: function(data)
                {
                for(var count=0; count<data.length; count++)
                {
                 var html_data = '<tr><td>'+data[count].project_id+'</td>';
                 html_data += '<td data-name="project_name" class="project_name" data-type="text" data-pk="'+data[count].project_id+'">'+data[count].project_name+'</td>';
                 html_data += '<td data-name="created_on" class="created_on" data-type="text" data-pk="'+data[count].project_id+'">'+data[count].created_on+'</td>';
                 html_data += '<td data-name="target_date" class="target_date" data-type="date" data-pk="'+data[count].project_id+'">'+data[count].target_date+'</td>';
                 html_data += '<td data-name="assign_to" class="assign_to" data-type="text" data-pk="'+data[count].project_id+'">'+data[count].assign_to+'</td>';
                 html_data += '<td data-name="current_status" class="current_status" data-type="textarea" data-pk="'+data[count].project_id+'">'+data[count].current_status+'</td>';
                 html_data += '<td data-name="previous_status" class="previous_status" data-type="textarea" data-pk="'+data[count].project_id+'">'+data[count].previous_status+'</td>';
                 html_data += '<td data-name="cito_comment" class="cito_comment" data-type="textarea" data-pk="'+data[count].project_id+'">'+data[count].cito_comment+'</td>';


                 $('#project_data').append(html_data);
                }
               }
          });

        });
    }

Not related to the question, but your PHP coude is vulnerable to SQL injection! — Martin Heralecký
– Martin Heralecký, Commented Aug 1, 2018 at 6:02
@MartinHeralecký I know it is for an example php not yet add any mysqli escape or filter to protect xss attack . — Firefog
– Firefog, Commented Aug 1, 2018 at 6:04

Sakezzz · Accepted Answer · 2018-08-01 06:03:51Z

2

You can use empty method beforeSend ajax

function fetch_project_data_filtered() {
  $("#user_ids").change(function(){ 
      var assign_to = $(this).val(); 
      var dataString = "assign_to="+assign_to; 
      //alert(assign_to);

      $.ajax({ 
        type: "POST", 
        url: "x-fetch.php",
        data: dataString, 
        dataType:"json",
        beforeSend: $('#project_data').empty(),
        success: function(data)
            {
            for(var count=0; count<data.length; count++)
            {
             var html_data = '<tr><td>'+data[count].project_id+'</td>';
             html_data += '<td data-name="project_name" class="project_name" data-type="text" data-pk="'+data[count].project_id+'">'+data[count].project_name+'</td>';
             html_data += '<td data-name="created_on" class="created_on" data-type="text" data-pk="'+data[count].project_id+'">'+data[count].created_on+'</td>';
             html_data += '<td data-name="target_date" class="target_date" data-type="date" data-pk="'+data[count].project_id+'">'+data[count].target_date+'</td>';
             html_data += '<td data-name="assign_to" class="assign_to" data-type="text" data-pk="'+data[count].project_id+'">'+data[count].assign_to+'</td>';
             html_data += '<td data-name="current_status" class="current_status" data-type="textarea" data-pk="'+data[count].project_id+'">'+data[count].current_status+'</td>';
             html_data += '<td data-name="previous_status" class="previous_status" data-type="textarea" data-pk="'+data[count].project_id+'">'+data[count].previous_status+'</td>';
             html_data += '<td data-name="cito_comment" class="cito_comment" data-type="textarea" data-pk="'+data[count].project_id+'">'+data[count].cito_comment+'</td>';


             $('#project_data').append(html_data);
            }
           }
      });

    });
}

answered Aug 1, 2018 at 6:03

Sakezzz

4686 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Firefog Over a year ago

beforeSend: $('#project_data').empty() reset the table correctly but how can I get all result if select value is null?

Firefog Over a year ago

I added another ajax call for on page load to view all result thanx

Jagdeesh Kumar · Accepted Answer · 2018-08-01 06:09:36Z

You can return table from AJAX page in html table form.

HRML Select Box.

                                        <select name="scanType" id="type" class="form-control m-bot15">
                                            <option selected disabled>-select type-</option>
                                            <option value="all">All</option>
                                            <option value="USG">USG</option>
                                            <option value="CT">CT</option>
                                            <option value="X-RAY">X-RAY</option>
                                            <option value="MR">MR</option>
                                            <option value="LAB">LAB</option>
                                        </select>

JavaScript function which runs onSubmit().

function SearchData() {
        var fromDate = $('#fromDates').val();
        var toDate = $('#toDates').val();
        var type = $('#type').val();
        var url = 'ajax_searchData.php?from='+fromDate+'&&to='+toDate+'&&type='+type;
        $.ajax({
            type: 'GET',
            url: url,
            dataType: 'html',
            success: function (data) {
                    $('#LoadTable').html(data);
            }
        })
    }

ajax_searchData.php Page

<?php
include('config.php');
$fromDate = $_GET['from'];
$toDate = $_GET['to'];
$type = $_GET['type'];
$x = 1;
$total = 0;
$grandTotal = '';
$byData = mysqli_query($con,"CALL reportByDate('".$fromDate."','".$toDate."')") or die(mysqli_error($con));
if(mysqli_num_rows($byData)>0)
{
if($type=='all')
{
?>
    <table class="table table-bordered table-striped table-condensed">
        <thead>
        <tr>
            <th>#</th>
            <th class="numeric">Type</th>
            <th class="numeric">Token</th>
            <th class="numeric" style="width:15%">Patient Name</th>
            <th class="numeric">Age</th>
            <th class="numeric">Gender</th>
            <th class="numeric" style="width:12%">Refferred By</th>
            <th class="numeric" style="width:15%">Reff. By Address</th>
            <th class="numeric" style="width:13%">Purpose of visit</th>
            <th class="numeric">Amount</th>
            <th class="numeric" style="width:12%">Date</th>
            <th class="numeric">Time</th>
            <th class="numeric">Status</th>
        </tr>
        </thead>
<?php
while($byDataResult = mysqli_fetch_assoc($byData))
{
    $total = $total+$byDataResult['Amount'];

    ?>
    <tr>
        <td><?php echo $x; ?></td>
         <td><?php echo $byDataResult['ScanType']; ?></td>
        <td><?php echo $byDataResult['TokenYesterday']."/".$byDataResult['TokenToday'] ?></td>
        <td class="numeric"><?php echo $byDataResult['PatientName']; ?></td>
        <td class="numeric"><?php echo $byDataResult['Age']; ?></td>
        <td class="numeric"><?php echo $byDataResult['Sex']; ?></td>
        <td class="numeric"><?php echo $byDataResult['RefferredBy']; ?></td>
        <td class="numeric"><?php echo $byDataResult['ReffByAddress']; ?></td>
        <td class="numeric" style="width: 300px;"><?php echo substr($byDataResult['PurposeOfvisit'],0,100); ?></td>
        <td class="numeric"><?php echo number_format($byDataResult['Amount'],2); ; ?></td>
        <td class="numeric"><?php echo $byDataResult['Date']; ?></td>
        <td class="numeric"><?php echo $byDataResult['created_at']; ?></td>
        <td class="numeric" style="color: red;text-align: center;">
                                                <?php
                                                if( $byDataResult['Status']==2)
                                                {
                                                    echo 'Edited';
                                                }
                                                else{
                                                    echo "---";
                                                }

                                              ?></td>
    </tr>
    <?php
    $x++;
}
?>
        <tr class="totalRow">
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th style="text-align: right;">Total</th>
            <th><?php echo number_format($total,2); ?>/-</th>
            <th></th>
            <th></th>
            <th></th>

        </tr>
        </tbody>
    </table>
<?php
}
else{

    $x = 1;
$total = 0;
$grandTotal = '';

$byDataType = mysqli_query($con,"CALL reportByDateType('".$fromDate."','".$toDate."','".$type."')") or die(mysqli_error($con));
if(mysqli_num_rows($byDataType)>0){
    ?>
  <table class="table table-bordered table-striped table-condensed">
        <thead>
        <tr>
            <th>#</th>
            <th class="numeric">Type</th>
            <th class="numeric">Token</th>
            <th class="numeric" style="width:15%">Patient Name</th>
            <th class="numeric">Age</th>
            <th class="numeric">Gender</th>
            <th class="numeric" style="width:12%">Refferred By</th>
            <th class="numeric" style="width:15%">Reff. By Address</th>
            <th class="numeric" style="width:13%">Purpose of visit</th>
            <th class="numeric">Amount</th>
            <th class="numeric" style="width:12%">Date</th>
            <th class="numeric">Time</th>
            <th class="numeric">Status</th>
        </tr>
        </thead>
<?php
while($byDataResults = mysqli_fetch_assoc($byDataType))
{
    $total = $total+$byDataResults['Amount'];

    ?>
    <tr>
        <td><?php echo $x; ?></td>
         <td><?php echo $byDataResults['ScanType']; ?></td>
        <td><?php echo $byDataResults['TokenYesterday']."/".$byDataResults['TokenToday'] ?></td>
        <td class="numeric"><?php echo $byDataResults['PatientName']; ?></td>
        <td class="numeric"><?php echo $byDataResults['Age']; ?></td>
        <td class="numeric"><?php echo $byDataResults['Sex']; ?></td>
        <td class="numeric"><?php echo $byDataResults['RefferredBy']; ?></td>
        <td class="numeric"><?php echo $byDataResults['ReffByAddress']; ?></td>
        <td class="numeric"><?php echo substr($byDataResults['PurposeOfvisit'],0,100); ?></td>
        <td class="numeric"><?php echo number_format($byDataResults['Amount'],2); ; ?></td>
        <td class="numeric"><?php echo $byDataResults['Date']; ?></td>
        <td class="numeric"><?php echo $byDataResults['created_at']; ?></td>
        <td class="numeric" style="color: red;text-align: center;">
        <?php
        if( $byDataResults['Status']==2)
        {
            echo 'Edited';
        }
        else{
            echo "---";
        }

      ?></td>
    </tr>
    <?php
    $x++;
}
?>
        <tr class="totalRow">
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th style="text-align: right;">Total</th>
            <th><?php echo number_format($total,2); ?>/-</th>
            <th></th>
            <th></th>
            <th></th>

        </tr>
        </tbody>
    </table>


    <?php
    }
    else{
        ?>
         <h3 style="text-align: center">Sorry! No Record Found</h3>
        <?php
    }
}
}
else{
 ?>
    <h3 style="text-align: center">Sorry! No Record Found</h3>
<?php
}
    ?>

Hope it will help You :)

Sandeep Chavda · Accepted Answer · 2018-08-01 06:25:19Z

function fetch_project_data_filtered() {
   $("#user_ids").change(function(){ 
      var assign_to = $(this).val(); 
      var dataString = "assign_to="+assign_to; 
      //alert(assign_to);

      $.ajax({ 
        type: "POST", 
        url: "x-fetch.php",
        data: dataString, 
        dataType:"json",
        success: function(data)
            {
            for(var count=0; count<data.length; count++)
            {
             var html_data = '<tr><td>'+data[count].project_id+'</td>';
             html_data += '<td data-name="project_name" class="project_name" data-type="text" data-pk="'+data[count].project_id+'">'+data[count].project_name+'</td>';
             html_data += '<td data-name="created_on" class="created_on" data-type="text" data-pk="'+data[count].project_id+'">'+data[count].created_on+'</td>';
             html_data += '<td data-name="target_date" class="target_date" data-type="date" data-pk="'+data[count].project_id+'">'+data[count].target_date+'</td>';
             html_data += '<td data-name="assign_to" class="assign_to" data-type="text" data-pk="'+data[count].project_id+'">'+data[count].assign_to+'</td>';
             html_data += '<td data-name="current_status" class="current_status" data-type="textarea" data-pk="'+data[count].project_id+'">'+data[count].current_status+'</td>';
             html_data += '<td data-name="previous_status" class="previous_status" data-type="textarea" data-pk="'+data[count].project_id+'">'+data[count].previous_status+'</td>';
             html_data += '<td data-name="cito_comment" class="cito_comment" data-type="textarea" data-pk="'+data[count].project_id+'">'+data[count].cito_comment+'</td>';


             $('#project_data').empty().html(html_data);
            }
           }
      });

    });
}

CJ Dennis · Accepted Answer · 2018-08-01 06:48:55Z

0

Use

$('#project_data').html(html_data)

instead of

$('#project_data').append(html_data)

in your change script.

edited Aug 1, 2018 at 6:48

CJ Dennis

4,3843 gold badges43 silver badges73 bronze badges

answered Aug 1, 2018 at 6:05

Mani krishnan

11 bronze badge

Collectives™ on Stack Overflow

How to add Dynamic filter using PHP MySQL AJAX

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related