UK Postcode Sort / Group

essexboyracer

Hi All, I have one mysql table that inserts sent data, name, address, full UK postcode. They want to pull out daily results that are near to their locale, select on a limit of LIMIT 150. They dont want to do this by specifying a radius, but instead by specifying the out postcode, like CO or LU or LE etc... they would then need the result sorted on rank - nearest first, furthest last

Would i have to get php/mysql to give each postcode a ranking on insertion, based on a regexp match on the first 1/2 charccters of the code, or is there a better mysql query that will work?

Vague - yes. Any takers?

essexboyracer

Hi All, edited this as I had a thought. A better way to do this is;

Strip the UK postcode down to the first 1 or 2 letters
Match that string against an 'exclude' list, e.g. pseudo code

    if ($record = 'HP' OR 'AL' OR 'LU') {
     $update = mysql_query("UPDATE proposer SET outcode = '1'");
    }

if ($record = 'OX' OR 'NN' OR 'CB') {
 $update = mysql_query("UPDATE proposer SET outcode = '2'");
}

and so on.

Then when It comes to extract info, all I need to do is order on the new outcode field?

Therefore the question would be what would I construct as the regexp initially in step 1? I have the following;

http://www.govtalk.gov.uk/gdsc/html/frames/PostCode.htm

and as well as a regexp, from the same site

http://www.govtalk.gov.uk/gdsc/schemaHtml/bs7666-v2-0-xsd-PostCodeType.htm

Any ideas? im stil playing