0 votes
4.7k views
asked in Magento by
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!)

1 Answer

0 votes
answered by
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

Related questions

+1 vote
1 answer 3.3k views
0 votes
1 answer 785 views
0 votes
1 answer 6.3k views
+1 vote
1 answer 2.2k views
0 votes
1 answer 1.1k views
asked Feb 13, 2017 in Magento by Toms
0 votes
1 answer 1.1k views
asked Dec 15, 2016 in Magento by anonymous
...