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;
}
}
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;
}
}
1 Answer
Reset to default 2The 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/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....
wpdbclass. – birgire Commented Dec 30, 2018 at 14:10