Multiple Table Rates Extension

0 votes
603 views
asked Mar 30 in Magento by Mark
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 Mar 31 by mkr
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 574 views
0 votes
1 answer 555 views
0 votes
1 answer 689 views
0 votes
1 answer 512 views
0 votes
1 answer 542 views
asked Dec 16, 2016 in Magento by anonymous
+2 votes
0 answers 285 views
...