extracting UK postcode data for shipping module

[Hoheria]

Hi, help appreciated please. I'm looking to adapt an Oz shipping module "tollzipzones" for use with postcodes in the UK. There is no module which does this for UK postcodes which are a different format to most- I currently use a regions/county based module which works fine but is not compatible with the info my carrier provides and postcodes is the post office preffered way of dealing with addresses.

 

Because of the size of the postcode data (close to 3000 codes for the first block of characters) I need to store this on the database rather then have as info within the shipping module. That's Ok, I think I can do that, but I need to compare the first block of the customers postcode (which is either 3 or 4 characters and may or may not have a space after it) with the stored database postcode information. The postcode fomat is YO24 3AW or could be YO2 3AW or could be inputted by customer as YO243AW.

 

The best way, I think, to do this is to strip off the final block of 3 characters from the customer's retrieved postcode. Here's the existing bit of code from the Australian shipping file. Is anyone able to adapt this to strip the 3 terminal characters and compare with the line from the database? Database will store YO1 up to YO62?

 

$sql = "SELECT *

FROM toll_zones

WHERE

$zip >= t_postcode and

$zip <= t_postcode";

 

$qResult = tep_db_query($sql); // run the query

$rec = tep_db_fetch_array($qResult); // get the first row of the result

$zone_id = $rec['t_zone'];

 

if ( $zone_id == '' ){

// Something is wrong, we didn't find any zone

$this->quotes['error'] = MODULE_SHIPPING_TOLLZIPZONES_NO_ZONE_FOUND;

return $this->quotes;

}

There seem to be a bewildering number of functions that might be useful . . . str_split() . . . trim() . . . explode() - but what to do??

If I can get it working I'll post the whole thing with database etc for all to use.

 

Martin

[Hoheria]

Slowly getting there, I've changed the code to split off the first bit of the postcode having already ensured that the postcode is indeed split into two bits by using the uk postcode check module available in contributions but when I run this page at the store checkout I get this error message:

 

1054 -unknown column AB10 (the bit of extracted postcode which I've called "area") in 'where clause'

 

I imagine I need to define what I'm passing to the database for comparison as "area" or can I write the where clause in a different way so that the result of splitting the postcode (called area) is compared with the same line stored in the MySql database table and then fetching the matching zone data?

 

Ideas please

 

Martin

 

//First get the destination postcode and check the db for matching delivery zone

$postcode = $order->delivery['postcode'];

list($area, $local) = split('[/ -]', $postcode);

 

if ( $area == '' ){

// Something is wrong, no matching area code

$this->quotes['error'] = MODULE_SHIPPING_UKPOSTZONES_NO_ZIPCODE_FOUND;

return $this->quotes;

}

 

$sql = "SELECT *

FROM toll_zones

WHERE

$area >= t_postcode and

$area <= t_postcode";

 

$qResult = tep_db_query($sql); // run the query

$rec = tep_db_fetch_array($qResult); // get the first row of the result

$zone_id = $rec['t_zone'];

[Druid6900]

Slowly getting there, I've changed the code to split off the first bit of the postcode having already ensured that the postcode is indeed split into two bits by using the uk postcode check module available in contributions but when I run this page at the store checkout I get this error message:

 

1054 -unknown column AB10 (the bit of extracted postcode which I've called "area") in 'where clause'

 

I imagine I need to define what I'm passing to the database for comparison as "area" or can I write the where clause in a different way so that the result of splitting the postcode (called area) is compared with the same line stored in the MySql database table and then fetching the matching zone data?

 

Ideas please

 

Martin

 

//First get the destination postcode and check the db for matching delivery zone

$postcode = $order->delivery['postcode'];

list($area, $local) = split('[/ -]', $postcode);

 

if ( $area == '' ){

// Something is wrong, no matching area code

$this->quotes['error'] = MODULE_SHIPPING_UKPOSTZONES_NO_ZIPCODE_FOUND;

return $this->quotes;

}

 

$sql = "SELECT *

FROM toll_zones

WHERE

$area >= t_postcode and

$area <= t_postcode";

 

$qResult = tep_db_query($sql); // run the query

$rec = tep_db_fetch_array($qResult); // get the first row of the result

$zone_id = $rec['t_zone'];

 

A little off topic, but, I was working with someone to modify the Canada Post with Dimensions Shipping module for use in the UK, since we have the same postal code structure as the UK (being a colony and all that LOL).

 

She said that they had some mechanism similar to our Canada Post by which we can pull shipping costs from the CP server and display them in a grid for the customer to chose from, regardless of where they were in the world.

 

I sent her off to talk to the people at the Royal Mail and never heard back from her.

 

It might be worth persuing modifying the CP module (which doesn't use tables) rather than having to store all those postal codes and changes to rates and weights.

 

Just a thought, but, be warned, there doesn't seem to be much in the way of support from either the original author or anyone that modded the module.

[Hoheria]

A little off topic, but, I was working with someone to modify the Canada Post with Dimensions Shipping module for use in the UK, since we have the same postal code structure as the UK (being a colony and all that LOL).

 

She said that they had some mechanism similar to our Canada Post by which we can pull shipping costs from the CP server and display them in a grid for the customer to chose from, regardless of where they were in the world.

 

I sent her off to talk to the people at the Royal Mail and never heard back from her.

 

It might be worth persuing modifying the CP module (which doesn't use tables) rather than having to store all those postal codes and changes to rates and weights.

 

Just a thought, but, be warned, there doesn't seem to be much in the way of support from either the original author or anyone that modded the module.

 

 

Thanks for that Druid and I'll look into that, what I liked about the oz module is that all the different postcodes are stored in the database (3000 approx for the UK) and the lookup against the shipping zone should be quite neat. I've done the database bit and the postcode/zones data should be pretty much a one off unless carrier has a radical change. What I'm not so keen on is having the zones data also stored in the database because there are only about 10 of these and it would be neater for store admin to have these configurable/updateable from admin. But first I'd like to try and get in up and running using the Oz config for the zones though I'd welcome input on changing this to run from data held on the shipping page.

 

Since my last posting I have struggled with the datbase query a bit longer and changed from this:

 

$sql = "SELECT *

FROM toll_zones

WHERE

$area >= t_postcode and

$area <= t_postcode";

 

to this:

 

$sql = "SELECT *

FROM toll_zones

WHERE

t_postcode= '$area'";

 

which at least appears to run OK but does not insert any value for the carriage, I've no idea what the need isfor the duplicate code in previous case.

 

 

Martin