Filter table by checkbox using ajax and PHP

prinzeoluwa · September 1, 2020, 1:43pm

I will like to filter a table that has is data fetch from database when checkbox is checked, this is not working.

below is the php file
<?php
  $pdo = new PDO(
    'mysql:host=localhost;dbname=db-dashboard', 'root', ''
  );
  $select = 'SELECT full_name, phone_number, gender, age, qualification, state, lga, community, crop, farm_size, created';
  $from = ' FROM farmers';
  $where = ' WHERE TRUE';
  $opts = isset($_POST['filterOpts'])?
            $_POST['filterOpts'] :
            array('');


  if (in_array("crop", $opts)){
    $where .= " AND crop = 'crop'";
  }

  $sql = $select . $from . $where;
  $statement = $pdo->prepare($sql);
  $statement->execute();
  $results = $statement->fetchAll(PDO::FETCH_ASSOC);
  $json = json_encode($results);
  echo($json);
?>

<div class="row">
    <div class="col-md-12">
      <div class="tile">
        <div class="tile-body">
          <table class="table table-hover table-responsive table-bordered" id="sampleTable">

                <div class="row-center">
                  <?php
                    if(isset($_SESSION['error'])){
                      echo
                      "
                      <div class='alert alert-danger text-center'>
                        <button class='close'></button>
                        ".$_SESSION['error']."
                      </div>
                      ";
                      unset($_SESSION['error']);
                    }
                    if(isset($_SESSION['success'])){
                      echo
                      "
                      <div class='alert alert-success text-center'>
                        <button class='close'></button>
                        ".$_SESSION['success']."
                      </div>
                      ";
                      unset($_SESSION['success']);
                    }
                  ?>
                </div>


           <form>
           <div class="container">
            <!-- <p>The form below contains three inline checkboxes:</p> -->
   
            <h6>Crops: </h6>
            <?php
            $query = "
            SELECT DISTINCT(crop) FROM farmers ORDER BY crop DESC
            ";
            $statement = $connect->prepare($query);
            $statement->execute();
            $result = $statement->fetchAll();
            foreach($result as $row)
            {
            ?>
              <label class="checkbox-inline">
                <input type="checkbox" class="filter_all crop" value="<?php echo $row['crop']; ?>">
                <?php echo $row['crop']; ?>
              </label>
              <?php
          }
          ?>

            </form>

          </div>
            <thead>
              <tr>
                <!-- <th>#</th> -->
                <th>FullNname</th>
                <th>PhoneNumber</th>
                <th>Gender</th>
                <th>Age</th>
                <th>Qualification</th>
                <th>State</th>
                <th>LGA</th>
                <th>Community</th>
                <th>Crop</th>
                <th>FarmSize(Ha)</th>
                <th>Created</th>

                <!-- <th width="150">Action</th> -->
              </tr>
            </thead>
            <tbody>

            <?php
            include('recordsEditDeleteModal.php');
            include('addRecordsModal.php') ;

            ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>

  </div><br><br><br>
</main>
<?php include("includes/footer.php"); ?>

<!-- checkbox filter  -->
<script src="http://code.jquery.com/jquery-latest.js"></script>
<script>
  function makeTable(data){
   var tbl_body = "";
      $.each(data, function() {
        var tbl_row = "";
        $.each(this, function(k , v) {
          tbl_row += "<td>"+v+"</td>";
        })
        tbl_body += "<tr>"+tbl_row+"</tr>";
      })
    return tbl_body;
  }
  function getEmployeeFilterOptions(){
    var opts = [];
    $checkboxes.each(function(){
      if(this.checked){
        opts.push(this.name);
      }
    });
    return opts;
  }
  function updateEmployees(opts){
    $.ajax({
      type: "POST",
      url: "submit.php",
      dataType : 'json',
      cache: false,
      data: {filterOpts: opts},
      success: function(records){
        $('#sampleTable tbody').html(makeTable(records));
      }
    });
  }
  var $checkboxes = $("input:checkbox");
  $checkboxes.on("change", function(){
    var opts = getEmployeeFilterOptions();
    updateEmployees(opts);
  });
  updateEmployees();
</script>

Here is the HTML with ajax code

droopsnoot · September 1, 2020, 3:06pm

Can you expand on “this is not working” please? Too much code to wade through to try to figure out what’s wrong without knowing the symptoms. And what debugging steps have you taken? How far through the code does it get before it goes wrong?

prinzeoluwa · September 1, 2020, 3:51pm

Thank you so much for the prompt response
The checkbox values are coming from a filed (crop) from the database. The crops ranging from Maize, Rice Millet and Sorghum, it is expected that when I check any of these crops it should populate only the crop that have the checked value, but it doesn’t right now.

Below is the code that fetch the checkbox values

        <?php
                $query = "
                SELECT DISTINCT(crop) FROM farmers ORDER BY crop DESC
                ";
                $statement = $connect->prepare($query);
                $statement->execute();
                $result = $statement->fetchAll();
                foreach($result as $row)
                {
                ?>
                  <label class="checkbox-inline">
                    <input type="checkbox" class="filter_all crop" value="<?php echo $row['crop']; ?>">
                    <?php echo $row['crop']; ?>
                  </label>
                  <?php
              }
              ?>

While below is the php code that is used to fetch the checkbox data (submit.php)

<?php
  $pdo = new PDO(
    'mysql:host=localhost;dbname=db-dashboard', 'root', ''
  );
  $select = 'SELECT full_name, phone_number, gender, age, qualification, state, lga, community, crop, farm_size, created';
  $from = ' FROM farmers';
  $where = ' WHERE TRUE';
  $opts = isset($_POST['filterOpts'])?
            $_POST['filterOpts'] :
            array('');


  if (in_array("crop", $opts)){
    $where .= " AND crop = 'crop'"; //a bit not sure here
  }


  $sql = $select . $from . $where;
  $statement = $pdo->prepare($sql);
  $statement->execute();
  $results = $statement->fetchAll(PDO::FETCH_ASSOC);
  $json = json_encode($results);
  echo($json);
?>

and lastly below is the ajax code

  <script>
      function makeTable(data){
       var tbl_body = "";
          $.each(data, function() {
            var tbl_row = "";
            $.each(this, function(k , v) {
              tbl_row += "<td>"+v+"</td>";
            })
            tbl_body += "<tr>"+tbl_row+"</tr>";
          })
        return tbl_body;
      }
      function getCropFilterOptions(){
        var opts = [];
        $checkboxes.each(function(){
          if(this.checked){
            opts.push(this.name);
          }
        });
        return opts;
      }
      function updateCropss(opts){
        $.ajax({
          type: "POST",
          url: "submit.php",
          dataType : 'json',
          cache: false,
          data: {filterOpts: opts},
          success: function(records){
            $('#sampleTable tbody').html(makeTable(records));
          }
        });
      }
      var $checkboxes = $("input:checkbox");
      $checkboxes.on("change", function(){
        var opts = getCropFilterOptions();
        updateCrops(opts);
      });
      updateCrops();
    </script>

James_Hibbard · September 1, 2020, 3:55pm

If it helps, this is code from one of my old tutorials.

I’m afraid that if I was to write that tutorial today, I’d probably do it a bit differently and it depends whether the error is in the PHP or the JavaScript as to how much help I’ll be.

As dropsnoot says, this doesn’t give us much to go on. Ideal would be if you could make a short self-contained example that demonstrated the problem. You can also look in your browser’s console and network tab to see if they contain any information that might help to debug.

prinzeoluwa · September 1, 2020, 4:06pm

Yes, it is from your old tutorial that I found useful, I need to make a little changes. Where I felt the issues is or where it is a bit differs from the exact tutorial is I need to used the below code to generate the checkbox values and not predefined in the HTML.

      <?php
                $query = "
                SELECT DISTINCT(crop) FROM farmers ORDER BY crop DESC
                ";
                $statement = $connect->prepare($query);
                $statement->execute();
                $result = $statement->fetchAll();
                foreach($result as $row)
                {
                ?>
                  <label class="checkbox-inline">
                    <input type="checkbox" class="filter_all crop" value="<?php echo $row['crop']; ?>">
                    <?php echo $row['crop']; ?>
                  </label>
                  <?php
              }
              ?>

Also, am not too certain in the below code where I need to fetch the corresponding data of the checkbox

<?php
  $pdo = new PDO(
    'mysql:host=localhost;dbname=db-dashboard', 'root', ''
  );
  $select = 'SELECT full_name, phone_number, gender, age, qualification, state, lga, community, crop, farm_size, created';
  $from = ' FROM farmers';
  $where = ' WHERE TRUE';
  $opts = isset($_POST['filterOpts'])?
            $_POST['filterOpts'] :
            array('');


  if (in_array("crop", $opts)){
    $where .= " AND crop = 'crop'"; //a bit not sure here
  }

  $sql = $select . $from . $where;
  $statement = $pdo->prepare($sql);
  $statement->execute();
  $results = $statement->fetchAll(PDO::FETCH_ASSOC);
  $json = json_encode($results);
  echo($json);
?>

I will really appreciate any help. Thank you.

droopsnoot · September 1, 2020, 5:32pm

Well, this isn’t correct for a start:

if (in_array("crop", $opts)){
    $where .= " AND crop = 'crop'"; //a bit not sure here
  }

That’s checking to see if your “crop” field contains the string “crop”. You need to get the id of the crop that you are looking for.

What does it do? If it’s nothing at all, then maybe it’s the problem above.

mabismad · September 1, 2020, 7:08pm

You need to check what the code is actually doing, at what point is it doing what you expect and at what point is it not.

To start with, add the following line to the submit.php code so that you can log what data is being submitted -

file_put_contents('log.txt',print_r($_POST,true),FILE_APPEND);

You will find that you are getting empty elements. The reason for this is because the javascript code is accessing the checkbox .name attributes, but the checkboxes don’t have names, they have values. I recommend that you change the value=‘…’ attributes back to name=‘…’ attributes. This should get the expected data into the php code.

Next, you will be getting an array of selected crop names. These have the same level/meaning. If more than one is selected, the query needs to OR the terms, not AND them. To OR terms in a query, you can either 1) build each term separately (in an array), then implode the array with the ’ OR ’ keyword between the terms, 2) use an IN() comparison, or 3) use FIND_IN_SET(str,strlist). All three of these methods work as long as one or more choices are checked. Methods #1 and #2 require you to supply a separate prepared query place-holder for each value. Method #3 uses a single prepared query place-holder for the 2nd strlist parameter. You will need to supply an array of input value(s) to the ->execute([…]) method call. For the FIND_IN_SET method, you would supply an imploded, comma separated list of the $_POST['filterOpts'] choices.

prinzeoluwa · September 2, 2020, 9:33am

Thank you so much for this detail guide.
I have made some corrections by changing checkbox .name to

    opts.push($(this).val());
            // opts.push(this.name);

Also, putting this into the submit.php file_put_contents('log.txt',print_r($_POST,true),FILE_APPEND);

give this result when I check the checkboxes

Array
(
)
Array
(
    [filterOpts] => Array
        (
            [0] => Soybeans
        )

)
Array
(
    [filterOpts] => Array
        (
            [0] => Soybeans
            [1] => Sorghum
        )

)
Array
(
    [filterOpts] => Array
        (
            [0] => Soybeans
            [1] => Sorghum
            [2] => Rice
        )

)
Array
(
    [filterOpts] => Array
        (
            [0] => Soybeans
            [1] => Sorghum
            [2] => Rice
            [3] => Millet
        )

)
Array
(
    [filterOpts] => Array
        (
            [0] => Soybeans
            [1] => Sorghum
            [2] => Rice
            [3] => Millet
            [4] => Maize
        )

)

However, it does not populate only the values of the checkbox to the table, rather it just refresh the page.

Am not sure of the best way to go about the given three options above as this still seems one of the major bottleneck here if (in_array("crop", $opts)){ $where .= " AND crop = '$opts'"; }

I will sincerely appreciate you and any other person for further guide and support.

prinzeoluwa · September 2, 2020, 10:08am

Thanks all, finally the code below give me the desired output.

	$allCrops = array('Maize', 'Millet', 'Rice', 'Sorghum', 'Soybeans');

	$selectedCrops = array();
	foreach ($allCrops as $crop) {
		if (in_array($crop, $opts)) {
			$selectedCrops[] = $crop;
		}
	}

	if (count($selectedCrops)) {
		$where .= " AND crop IN ('".implode("', '", $selectedCrops)."')";
	}

However, I still need help,how do I add an auto increment number to the first column of the table (I know I can fetch the id from the DB, but I will prefer an auto increment), secondly how do I add a href link where I can edit and delete record by using a selected id.
Thanks in advance

prinzeoluwa · September 2, 2020, 10:23am

Meanwhile, what happen if I don’t know the list of the crops before hand and it is incremental as it is to be fetched from the DB.

droopsnoot · September 2, 2020, 12:21pm

Run a query to fetch the crops from the database, instead of this line?

mabismad · September 2, 2020, 12:50pm

Your code already has a query in it to get all the distinct choices for producing the check boxes. How about moving that up in the code so that it is at a common point and can be used for both purposes. You will want to change how the data is fetched (see the PDO::FETCH_COLUMN fetch mode.)

prinzeoluwa · September 6, 2020, 11:52pm

Thank you.
I still need guide on how to export the filter data into an excel sheet

system · December 7, 2020, 6:52am

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.