How to fetch data from db based on multiple filter selections using AJAX in Wordpress

Question

So I want to filter the data from db using multiple filters. I have 2 selects for now, GENDER and MARITAL STATUS. If I choose for example 'male' from GENDER, and nothing on MARITAL STATUS, I want the results to be all the data that has the option 'male'. If I select also 'divorced' from MARITAL STATUS, then the results will be the data that has the options 'male' and 'divorced' at the same time.

<div class="sidebar-filters">
  <div class="filters">
      <span>Gender</span>
      <select id="gender" name="gender">
         <option value="">Please select gender</option>
         <option value="male">male</option>
         <option value="female">female</option>
         <option value="agender">agender</option>
      </select>

      <span>Marital Status</span>
      <select id="marital_status" name="marital_status">
         <option value="">Please select marital status</option>
         <option value="married">married</option>
         <option value="widowed">widowed</option>
         <option value="separated">separated</option>
         <option value="divorced">divorced</option>
         <option value="single">single</option>
       </select>

    </div>
 </div>
<div class="results-wrap">
       <?php
            $query = "SELECT * FROM reviews1";
            $result = mysqli_query($conn, $query);
            $queryResult = mysqli_num_rows($result);
            if($queryResult > 0){
                 while($row = mysqli_fetch_assoc($result)){
                     echo "<div class='review-wrap'>
                           <h3>".$row['r_title']."</h3>
                           <p>".$row['age']."</p>
                           </div>";
                   }
           }else{
               echo "There are no results matching your search";
           }
       ?>

In my scripts.js I have

$('#gender').on('change', function(){
     var value = $(this).val();
     $.ajax({
           url: "http://uttertest.ecom-shops.com/wp-admin/admin-ajax.php",
           type: 'POST',
           data: {
               action: 'filtering',
               request: value
                },
           beforeSend:function(){
                 $(".results-wrap").html("Wait...");
           },
           success:function(data){
                 $(".results-wrap").html(data);
           },
           error:function(xhr){
                 $(".results-wrap").html("Error");
           }
           });
 }); 

In functions.php I have

add_action( 'wp_ajax_nopriv_filtering', 'filtering' );
add_action( 'wp_ajax_filtering', 'filtering' );

function filtering(){
    $server = "localhost";
    $username = "username";
    $password = "*****";
    $dbname = "databasename";
    $conn = mysqli_connect($server, $username, $password, $dbname);
          $request = $_POST['request'];
          $query = "SELECT * FROM reviews WHERE gender='$request'";
          $result = mysqli_query($conn, $query);
          $queryResult = mysqli_num_rows($result);
          if($queryResult > 0){
            while($row = mysqli_fetch_assoc($result)){
                echo "<div class='review-wrap'>
                <h3>".$row['r_title']."</h3>
                <p>".$row['age']."</p>
            </div>";
            }
          }else{
            echo "There are no results matching your search";
          }

   } 

So far it's good, if I change the gender, the data is displayed correct, acorrding to the option chosed. The questions is how to combine the 2 choices from selections? And not only this 2 selections, also some checkboxes added in the future in the html, checked or not checked, and update the data fetched from db, every time a selection is changed or if a checkbox is checked or unchecked.

Also I don't need a submit button, the changes should apply on change, whenever a checkbox is checked or an option from select is chosed.

Answer 1

As you're using WordPress, don't use your own database queries because you can let WordPress do all of the heavy lifting for you so to speak and ensures data is properly escaped before being executed. See WP_Query on how to do this.

This is a rough starting point. It assumes the post type is reviews and the post status is set to publish. From there if either gender or marital status is set, it'll add it to the meta query to search via that too.

$args = array(
    'post_type'      => 'reviews',
    'post_status'    => 'publish',
    'orderby'        => 'post_title',
    'order'          => 'ASC',
    'meta_query'     => array(),
);

if ( '' != $gender ) {

    $args['meta_query'][] = array(
        'key'    => 'gender',
        'value'  => $gender
    );

}

if ( '' != $marital ) {

    $args['meta_query'][] = array(
        'key'    => 'marital',
        'value'  => $marital
    );

}

$posts = new WP_Query( $args );

The above also assumes that the gender and marital fields are saved as post meta as well in the wp_postmeta table. This is the most effective way of querying the database. It makes things much easier.

This then returns an object with info on the posts returned and the query that was actually used. See the developer reference for a little bit more information on the query class used here.