CODEWITHSUNDEEP
phpmysqlajax
Firefog
Firefog

Reputation: 3174

How to add Dynamic filter using PHP MySQL AJAX

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);
                }
               }
          });

        });
    }

Upvotes: 1

Views: 2240

Answers (4)

Mani krishnan
Mani krishnan

Reputation: 1

Use

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

instead of

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

in your change script.

Upvotes: 0

Sandeep Chavda
Sandeep Chavda

Reputation: 1

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);
            }
           }
      });

    });
}

Upvotes: 0

Jagdeesh Kumar
Jagdeesh Kumar

Reputation: 1760

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

Upvotes: 0

Sakezzz
Sakezzz

Reputation: 468

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);
            }
           }
      });

    });
}

Upvotes: 2

Related Questions