Reload results from SQL query using AJAX in WordPress PHP page file

I want to change the way my database results are displayed on a page based on the select filter option chosen by my visitors without having to reload the whole page using AJAX in WordPress. I managed to filter the displayed results by submitting a from with jQuery and using the $_GET method. However, I am thinking AJAX might be best in terms of UX and speed.

In my page.php file:

global $wpdb;
$table_name = $wpdb->prefix . "mydatabase";
...
$main_cat_query = $wpdb->prepare("FROM $table_name WHERE product_category LIKE %s ", $category);
$results = $wpdb->get_results("SELECT * $main_cat_query ORDER BY $filter_sort_query;");
...
<div class="top-filter-select-container">
<select class="selectpicker" id="sort-filter" name="sort">
    <option value="popularity" <?php echo $sort_option == 'popularity' ? 'selected' : ''; ?>>Sort by Popularity</option>
    <option value="rating" <?php echo $sort_option == 'rating' ? 'selected' : ''; ?>>Sort by Rating</option>
    <option value="lowest-price" <?php echo $sort_option == 'lowest-price' ? 'selected' : ''; ?>>Sort by Lowest Price</option>
    <option value="highest-price" <?php echo $sort_option == 'highest-price' ? 'selected' : ''; ?>>Sort by Highest Price</option>
</select>
</div>
...
/* Loop through results */
<div class="results-main-container">
    <?php foreach($results as $key => $row): ?> 
    <div class="item-container">
        ...
    </div>
    <?php endforeach; ?>
</div>

In my custom.js file:

$(function(){
 $('#sort-filter').on('change', function(e) {
    e.preventDefault();
    e.stopImmediatePropagation();
    var value = $(this).val();

    $.ajax({
        url: customajaxscript.ajax_url,
        type: 'GET',
        dataType: 'json',
        data:{
            action: 'sortresults',
            sort: value,
        }
    });
    }); 
});

In my custom-functions.php file (WordPress AJAX):

function sortresults(){

    global $wpdb;
    $table_name = $wpdb->prefix . "mydatabase";

    $sort_option = isset($_GET['sort']) ? $_GET['sort'] : null;
    $filter_sort_query = find_sort_query($sort_option);

    die(); // this is required to return a proper result
}

add_action('wp_ajax_nopriv_sortresults', 'sortresults');
add_action('wp_ajax_sortresults', 'sortresults');

My find_sort_query() function returns a string based on the $sort_option string, i.e. "product_sale_price DESC". This string is stored in the $filter_sort_query variable and used to build my SQL query.

What would be the right approach to reload what is inside my results-main-container div based on a SQL query built from the selected filter option. I still want to have a default $filter_sort_query, when my page initially loads.

On a side note, is $_GET the right approach here or should I use $_POST? If $_GET is used with AJAX, should I be adding a ?sort=rating at the end of my URL? If so, how should it be added and what would be the main advantage of doing so.

Read more here: Reload results from SQL query using AJAX in WordPress PHP page file

Leave a Reply

Your email address will not be published. Required fields are marked *