How to use AJAX in WordPress in MYSQL query?

Question

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

Answer 1

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