Leaderboard

I have been playing around with a possible database solution to your problem Given that a postcode such as "EH12 3AB" breaks down into four parts viz +------+----------+--------+------+ | area | district | sector | unit | +------+----------+--------+------+ | EH | 12 | 3 | AB | +------+----------+--------+------+ ... I was toying with this table structure CREATE TABLE `postcode` ( `pc_id` int(11) NOT NULL AUTO_INCREMENT, `seller` int(11) DEFAULT NULL, `area` varchar(2) DEFAULT NULL, `district` varchar(2) DEFAULT NULL, `sector_min` char(1) DEFAULT NULL, `sector_max` char(1) DEFAULT NULL, `unit_min` char(2) DEFAULT NULL, `unit_max` char(2) DEFAULT NULL, `deliverable` tinyint(4) DEFAULT NULL, `price` decimal(8,2) DEFAULT NULL, PRIMARY KEY (`pc_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-------+--------+------+----------+------------+------------+----------+----------+-------------+-------+ | pc_id | seller | area | district | sector_min | sector_max | unit_min | unit_max | deliverable | price | +-------+--------+------+----------+------------+------------+----------+----------+-------------+-------+ | 1 | 1 | EH | 1 | 1 | 4 | AA | ZZ | 1 | 1.50 | | 2 | 1 | EH | 1 | 5 | 5 | AA | BZ | 1 | 1.80 | | 3 | 1 | EH | 1 | 5 | 5 | CA | ZZ | 0 | 2.00 | | 4 | 1 | EH | 2 | 1 | 9 | AA | ZZ | 1 | 2.25 | | 5 | 1 | EH | 3 | 1 | 9 | AA | PZ | 1 | 2.50 | +-------+--------+------+----------+------------+------------+----------+----------+-------------+-------+ My code was $postcodes = [ 'EH1 2DB', 'eh15bg' , 'eh1 5ba', 'eh15dg', 'EH2 7HJ', 'EH3 2PT', 'EH3 8SX', 'EH146DE' ]; echo '<pre>'; foreach ($postcodes as $pc) { vprintf('%s%s %s%s : %s<br>', deliveryPrice($db, $pc)); } echo '</pre>'; function deliveryPrice($db, $pcode) { $pcode = strtoupper(str_replace(' ', '', $pcode)); $area = $district = ''; $sector = substr($pcode,-3, 1); $unit = substr($pcode, -2); $l = strlen($pcode); $first = str_split(substr($pcode, 0, $l-3)); foreach ($first as $c) { if (ctype_digit($c)) { $district .= $c; } else { $area .= $c; } } $res = $db->prepare("SELECT price FROM postcode WHERE area = ? AND district = ? AND ? between sector_min AND sector_max AND ? BETWEEN unit_min AND unit_max AND deliverable "); $res->execute( [ $area, $district, $sector, $unit ] ); $p = $res->fetchColumn(); $price = $p ? number_format($p, 2) : 'N/A'; return [$area, $district, $sector, $unit, $price ]; } RESULTS: EH1 2DB : 1.50 EH1 5BG : 1.80 EH1 5BA : 1.80 EH1 5DG : N/A EH2 7HJ : 2.25 EH3 2PT : 2.50 EH3 8SX : N/A EH14 6DE : N/A