Postal code validation does not work for postal code ranges

Question

Trying to create postal code validation in woocommerce.

I can't get my query to look at range intervals like 10000-000...15000-000.

My query works for simple zip codes (10000-000 or 11001-001) but not for zip code ranges.

global $wpdb;
    $tabela_cep = $wpdb->prefix . 'woocommerce_shipping_zone_locations';
    $query = "SELECT location_code FROM $tabela_cep WHERE location_code = %s LIMIT 1";
    $resultado = $wpdb->get_var($wpdb->prepare($query, $cep));

I don't know how woocommerce stores all ranges for all shipping areas in the database.

I've tried "Between" sintax to filter the fields locationcode and locationcode2, but I'm not sure if that's the correct field name/table name or even if there's another way to achieve this.

global $wpdb; 
$tabela_cep = $wpdb->prefix . 'woocommerce_shipping_zone_locations'; 
$query = "SELECT location_code FROM $tabela_cep WHERE %s BETWEEN CAST(location_code AS UNSIGNED) AND CAST(location_code2 AS UNSIGNED) LIMIT 1";
$resultado = $wpdb->get_var($wpdb->prepare($query, $cep));

Thanks

Answer 1

I don't know if this helps, but there is already a core WooCommerce function called wc_postcode_location_matcher() that checks if a given postcode is within a postcode range.

You first need to calculate $postcode_locations:

global $wpdb;
$postcode_locations = $wpdb->get_results( "SELECT zone_id, location_code FROM {$wpdb->prefix}woocommerce_shipping_zone_locations WHERE location_type = 'postcode';" );

Then you need $country, so if you are on the checkout page:

$country = WC()->customer->get_billing_country();

...or if you are verifying your order:

$country = $order->get_billing_country();

Then you’re good to go:

$matches = wc_postcode_location_matcher( $postcode, $postcode_locations, 'zone_id', 'location_code', $country );
return $matches; // if empty there is no match