Multiple Table Rates Extension

Question

Hi,

I'm using this module for a site to set shipping rates.

https://www.magentocommerce.com/magento-connect/webshopapps-matrixrate-1-multiple-table-rates-extension-1.html

I've added some UK postcode variations - nothing massive. Problem is the pattern matching syntax I think.
When I put in e.g. EH12 as a postcode, I am shown postage options for both EH1 and EH12. CSV has postcodes as EH1% and EH12%.
Can you please advise on correct syntax, or point me to a resource that will allow me to figure it out myself?
Attempted adding spaces before the % but to no avail.
Many thanks for the support (and help!)

Answer 1

Well thats a tricky question, and is a bit hard to solve. I had once
got the same problem from a client, after hours of troubleshooting
this is what I end up with. You will see the following SQL in the
file, which is returning all the results for shipping options.

SELECT `shipping_matrixrate`.* FROM `shipping_matrixrate` WHERE (
(dest_country_id='GB'  AND dest_region_id=''  AND
STRCMP(LOWER(dest_city),LOWER('')) = 0   AND 'EH1244444' LIKE
dest_zip )) AND (condition_name='package_weight') AND
(condition_from_value<=62) AND (condition_to_value>=62) AND
(website_id='1') ORDER BY `dest_country_id` DESC, `dest_region_id`
DESC, `dest_zip` DESC, `condition_from_value` DESC

Assuming what we have in the database is that of 4* and 3* char checking
And with PHP extract the first 4 characters of the zip input by customer and execute the query like this (in your case its "EH12" which is the first 4 letters of zip code)

SELECT `shipping_matrixrate`.* FROM `shipping_matrixrate` WHERE (
(dest_country_id='GB'  AND dest_region_id=''  AND
STRCMP(LOWER(dest_city),LOWER('')) = 0   AND 'EH1244444' LIKE
dest_zip )) AND (condition_name='package_weight') AND
(condition_from_value<=62) AND (condition_to_value>=62) AND
(website_id='1') AND dest_zip!="EH12%" ORDER BY `dest_country_id` DESC,
`dest_region_id` DESC, `dest_zip` DESC, `condition_from_value` DESC

If there is records returned, it means you will get duplicate if you run the original query (since the 3 letter "EH1" of customer input will return both 3 and 4 char records as match).
So just add condition only to run the original query if and only if no records is returned by our custom query.

Hope this helps.

mkr