Ajax $wpdb not returning table data

Question

I'm using WordPress 5.0.2 with WooCommerce 3.5.3 and I have two selects dropdown field on the checkoutpage. I want to retrieve the selected region_code value from the billing_region dropdown and then depending on that value, populate the billing_province select dropdown but the ajax request return nothing (400)

Bellow my code

HTML :

<div class="delivery-form-row">
   <label for="billing_region">Region :</label>
   <select id="billing_region" name="billing_region">
      <option value="" selected="" disabled="">- Select your region -</option>
      <option value="01">REGION I </option>
      <option value="02">REGION II </option>
      <option value="03">REGION III </option>
   </select>
</div>
<div class="delivery-form-row">
   <label for="billing_province">Province :</label>
   <select id="billing_province" name="billing_province">
      <option value="" selected="">- Select your region first -</option>
   </select>
</div>

functions.php

add_action( 'wp_enqueue_scripts', 'enqueue_js_script', 30);
function enqueue_js_script() {
  wp_enqueue_script( 'custom-js', get_stylesheet_directory_uri() . '/js/custom.js', array('jquery'), '1.0.0', true );
  wp_localize_script( 'custom-js', 'postdata', array( 'ajax_url' => admin_url( 'admin-ajax.php' ) ) );
}


add_action ('wp_ajax_call_wc_get_phprpcb_data', 'wc_get_phprpcb_data') ;
add_action ('wp_ajax_nopriv_call_wc_get_phprpcb_data', 'wc_get_phprpcb_data') ;
function wc_get_phprpcb_data() {
  global $wpdb;

  if(isset($_POST['regcode'])) {

    $stmt = $wpdb->query( $wpdb->prepare("SELECT * FROM skjdbt_sphilippine_provinces WHERE region_code = " . $_POST['regcode'] . " ORDER BY province_description ASC") ); 
    $stmt->execute();
    $provinces = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo wp_send_json_success($provinces);
    wp_die();
  }
}

custom.js :

  $("#billing_region").change(function(){
    var $this = $(this),
        regcode = $this.val(),
        $billingProvince = $("#billing_province");
        console.log(regcode)

    $.ajax({
      method: 'post',
      url: postdata.ajax_url,
      dataType: 'JSON',
      data: {
        action: 'wc_get_phprpcb_data',
        regcode: regcode,
      },
      success: function(provinces) {

        $billingProvince
        .empty()
        .append('<option value="" selected="" disabled="">Please choose your province</option>');

        provinces = JSON.parse(provinces);
        provinces.forEach(function(province){
          $billingProvince.append('<option value="' + province.province_code + '">' + province.province_description +'</option>');
        });
      },
    });
  });

Screenshot of the province table :

Any help would be appreciated

[EDIT] @Krzysiek Dróżdż]

Thanks for your help mate. Bellow the working code.

add_action ('wp_ajax_wc_get_phprpcb_data', 'wc_get_phprpcb_data') ;
add_action ('wp_ajax_nopriv_wc_get_phprpcb_data', 'wc_get_phprpcb_data') ;
function wc_get_phprpcb_data() {
  global $wpdb;

  if(isset($_POST['regcode'])) {

    $regcode = $_POST['regcode'];
    $sql = "SELECT * FROM {$wpdb->prefix}philippine_provinces WHERE region_code = " . $regcode . " ORDER BY province_description ASC";
    $result = $wpdb->get_results($wpdb->prepare($sql, $regcode));

    echo json_encode($result);

    die();
    exit;
  }
}

Answer 1

The main problem, I see in your code, is that you use query method of wpdb and assume that it is PDO statement object...

But it is not.

Take a look at wpdb Codex: https://codex.wordpress.org/Class_Reference/wpdb

query method is used to run custom queries on DB - that’s all.

What you want to use is get_results.

And BTW, seriously, do never concatenate user input with SQL statements. Such data should ALWAYS be escaped properly - otherwise you create SQL Injection vulnerability. You can use prepare method to create safe queries in WP.

I also don’t think you should echo wp_send_json_success - I’m pretty sure this function already echoes values...

And one more thing... Your hooks are wrong. You send action wc_get_..., but then use hooks like wp_ajax_call_wc_get... (so your php code assumes the action is call_wc_get....