How to use AJAX in WordPress in MYSQL query?

I have custom page template with a form with 2 dropdown and 1 input submit. When the user submit the form, the result will be displayed, this is working properly. But I have a problem, the browser is reload when fetching the data in the database.

I want to use AJAX to prevent reloading of page. I saw some article about Ajax WordPress, but I cannot work it correctly.

Anyone can please help me out? How to do with AJAX? How can I call the custom PHP file in the AJAX?

AJAX (functions.php)

function ajax_enqueue_sl(){
    wp_enqueue_script('ajax_load_sl', get_template_directory_uri().'/js/alternate-dropdown.js', array('jquery'));
    wp_localize_script('ajax_load_sl', 'ajax_object', array('ajax_url' => admin_url( 'admin-ajax.php')));
}
add_action('wp_enqueue_scripts', ajax_enqueue_sl);

PHP w/ HTML (test.php)

$results_malls = $wpdb->get_results('SELECT DISTINCT street FROM tablename WHERE code IN ("test1") ORDER BY malls ASC', OBJECT);
$results_stores = $wpdb->get_results('SELECT DISTINCT stores FROM tablename WHERE code IN ("test2") ORDER BY stores ASC', OBJECT);

<form action='' method='post' name='myform' id="myform">
<div class="pos-div">
<select name="street_list" id="filterbystreet">
    <option name="default" class="filter_by" value="Select by">Select by</option>
    <?php
    foreach($results_malls as $option){
        if(isset($_POST['street_list']) && $_POST['street_list'] == $option->malls)
            echo '<option name="street_list" class="filter_by" selected value="'. $option->street .'">'. $option->street .'</option>';
        else    
         echo '<option name="street_list" class="filter_by" value="'. $option->street .'">'. $option->street .'</option>';
     };
    ?>
</select>
</div>
<span class="or">or</span>
<div class="pos-div">
<select name="store_list" id="filterby">
    <option name="default" class="filter_by" value="Select by">Select by</option>
    <?php 
    foreach($results_stores as $option){
        if(isset($_POST['store_list']) && $_POST['store_list'] == $option->stores)
            echo '<option name="store_list" class="filter_by" selected value="'. $option->stores .'">'. $option->stores .'</option>';
        else    
         echo '<option name="store_list" class="filter_by" value="'. $option->stores .'">'. $option->stores .'</option>';
     };
    ?>
</select>
</div>
<input type="submit" value="List all partner stores" class="pos-submit"/>
</form>

if (isset($_POST['store_list']) && $_POST['store_list'] != 'Select by Store'){
        $store_list = $_POST['store_list'];
        $stores= $wpdb->get_results($wpdb->prepare("SELECT malls FROM tablename WHERE stores= '" . $store_list . "' AND code IN ('test3') ORDER BY street ASC", OBJECT));
        foreach ($stores as $record_s){
            echo '<div class="records">';
            echo '<div><span>' . $record_s->malls . '</span></div>';
            echo '</div>';
        }
    } elseif (isset($_POST['street_list']) && $_POST['street_list'] != 'Select by'){
    $street_list = $_POST['street_list'];
    $streets = $wpdb->get_results($wpdb->prepare("SELECT street FROM tablename WHERE street_list= '" . $street_list. "' AND code IN ('test3') ORDER BY stores ASC", OBJECT));
    foreach ($streets as $record_m){
        echo '<div class="records">';
        echo '<div><span>' . $record_m->stores . '</span></div>';
        echo '</div>';
    }
}

ajax_js.js

jQuery('#filterbymall').change(function(){
    jQuery('#filterbystore').prop('selectedIndex','Select by Store');
});

jQuery('#filterbystore').change(function(){
    jQuery('#filterbystreet').prop('selectedIndex','Select by');
});

jQuery('#myform').submit(ajaxSubmit);

function ajaxSubmit(){
    var myform= jQuery(this).serialize();

    jQuery.ajax({
        type:"POST",
        url: "/wp-admin/admin-ajax.php",
        data: myform,
        success:function(data){
            jQuery(".records").html(data);
        }
    });
return false;
}

Solutions Collecting From Web of "How to use AJAX in WordPress in MYSQL query?"

In WordPress it little bit complicated then default way to handle ajax form data. odify your js code like this.

function ajaxSubmit(e){

    // prevent the default action.
    e.preventDefault();

    var myform= jQuery(this).serialize();

    jQuery.ajax({
        type:"POST",
        // Get the admin ajax url which we have passed through wp_localize_script().
        url: ajax_object.ajax_url,
        action: "submitAjaxForm",
        data: myform,
        success:function(data){
            jQuery(".records").html(data);
        }
    });
return false;
}

In above JS code I have replaced the form url and added action parameter. WordPress gives us a unified file to use that is admin-ajax.php. It is used to call the server side PHP function through admin-ajax.php. If an action is not specified, admin-ajax.php will exit, and return 0 in the process.

Why form url replaced with ajax_object.ajax_url ?

As you already have passed the admin-ajax.php to JS file using wp_localize_script. Also it is used to call the server side PHP function through admin-ajax.php. If an action is not specified, admin-ajax.php will exit, and return 0 in the process.

Define form action.

To define the form action, we need to add these action using wp_ajax_ and wp_ajax_nopriv_ as prefix. With callback function to handle those form data.
Example.

add_action('wp_ajax_{YourFormAction}','{CallbackFunction}');`
add_action('wp_ajax_nopriv_{YourFormAction}','{CallbackFunction}');
  • wp_ajax_nopriv_{YourFormAction} executes for users that are not logged in.

Add this code to functions.php file

add_action('init', 'registerFormAction');

    function registerFormAction(){

        // To handle the form data we will have to register ajax action. 
        add_action('wp_ajax_nopriv_submitAjaxForm','submitAjaxForm_callback');
        add_action('wp_ajax_submitAjaxForm','submitAjaxForm_callback');

    }

Handle ajax form data

Put your all code which is going to handle the form data, Inside the callback function which we have recently hooked with the wp_ajax_{yourAction} above.

function submitAjaxForm_callback(){
    global $wpdb;
    if (isset($_POST['store_list']) && $_POST['store_list'] != 'Select by Store'){
            $store_list = $_POST['store_list'];
            $stores= $wpdb->get_results($wpdb->prepare("SELECT malls FROM tablename WHERE stores= '" . $store_list . "' AND code IN ('test3') ORDER BY street ASC", OBJECT));
            foreach ($stores as $record_s){
                echo '<div class="records">';
                echo '<div><span>' . $record_s->malls . '</span></div>';
                echo '</div>';
            }
        } elseif (isset($_POST['street_list']) && $_POST['street_list'] != 'Select by'){
        $street_list = $_POST['street_list'];
        $streets = $wpdb->get_results($wpdb->prepare("SELECT street FROM tablename WHERE street_list= '" . $street_list. "' AND code IN ('test3') ORDER BY stores ASC", OBJECT));
        foreach ($streets as $record_m){
            echo '<div class="records">';
            echo '<div><span>' . $record_m->stores . '</span></div>';
            echo '</div>';
        }
    }

    // We must have to terminate, to get proper response.
    wp_die();
}

Hope this help! 🙂