Postcode search

kev69

I have a database that I need to search for UK postcodes. UK postcodes are made up of 1 or 2 letters, 1 or 2 numbers, a space, 1 number and 2 letters, e.g. NW1 1AB or L2 3XT.
What I want to happen is that when a user types in their own postcode the server will extract the letters from the front (NW or L in the examples) and list all the locations in the database that have the same letters as the start of their postcode, it must not have extra letters though, i.e. a search of the L postcodes must not retrun postcodes such as LE3 5XZ.
The idea is that it will return those locations nearest to them.
I hope that's clear.
If anyone has any ideas about how to do this I will be very grateful as this is only my second attempt at PHP coding.
Thanks in advance
Kev

Rodders

You could use a SUBTRING of the postcodes you are searching. So if the user chooses 'L' your SQL will look like this....

WHERE SUBSTRING(postcode, 1, 1) = 'L'

Or maybe use the LEFT function.....

WHERE LEFT(postcode, 1) = 'L'

If they chose to search PE.....

WHERE SUBSTRING(postcode, 1, 2) = 'PE'

WHERE LEFT(postcode, 2) = 'PE'

kev69

How do I make sure the server knows the difference between 1 letter postcodes and 2 letter postcodes?

ultraslacker

You would use SUBSTRING again to make sure that for one letter searches the second character is a number -

AND ASCII(SUBSTRING(postcode, 2, 1)) BETWEEN 47 AND 57

Rodders

Just to clarify your query, can you answer these questions:

What is stored in your database for postcode? Is it just the first block of letters, or is it full postcodes?

What will the user enter into the form? Will it be their own postcode in full, or just the first block of letters?

kev69

The database holds the full postcode, the user will enter their full postcode. Ther server will then extract the first letters (1 or 2), compare them to the database and select the ones that match.

ultraslacker

I happen to be working on something similar, so here's how I would solve it -


#check if postcode is initial two letter
IF (preg_match("^\\D\\D.*", $postcode)
{	
	#initial two letter postcode
	$where = "WHERE postcode REGEXP '^[^0-9]{2}.*'";
} else {
	#initial one letter postcode
	$where = "WHERE postcode REGEXP '^[^0-9][0-9].*'";
}

http://www.mysql.com/doc/en/Regexp.html
http://www.php.net/manual/en/pcre.pattern.syntax.php

kev69

Thanks for your help.
So will this code turn the variable 'postcode' into the 1 or 2 letters?

ultraslacker

No, the where clause was wrong.

# postcode_initial = 2 if a 2 letter initial postcode, otherwise 1.
$postcode_initial = preg_match("^\D\D.*", $postcode) + 1;

# search for appropriate initial postcode character(s) 
$where = "WHERE postcode REGEXP '^" . substr($postcode, 0, $postcode_initial) . "[0-9].*'";

kev69

thanks, I'll try that out

kev69

I keep getting this warning:
Warning: No ending delimiter '^' found in /home/yfc/public_html/local-postcode.php on line 11

not sure what it means

ultraslacker

forgot to type the expression as '\D\D' - html translated the single slash.

 (preg_match("^\\D\\D.*", $postcode)

kev69

Hi, still getting the same error. Here is the code:

# # postcode_initial = 2 if a 2 letter initial postcode, otherwise 1.
$postcode_initial = preg_match("^\\D\\D.*", $postcode) + 1;
# search for appropriate initial postcode character(s) 
$where = "WHERE postcode REGEXP '^" . substr($postcode, 0, $postcode_initial) . "[0-9].*'";

mysql_select_db($database_yfcdb, $yfcdb);
$query_Recordset2 = "SELECT Name, Address1, Address2, City, Postcode, County, Tel, Email FROM centres WHERE Postcode LIKE '$postcode_initial' ORDER BY Name ASC";.

The error is in the $postcode_initial = preg_match line.

ultraslacker

Ahah - forward slashes are needed. I tested this and it works.

$postcode_initial = preg_match("/^\\D\\D/", $postcode) + 1;

MrRosary

I pulled all the distinct first 2 characters in my postcode field into another table..... use that to construct a drop down list on the search page.

Then the user selects exactly what you let them..... no need to worry about what they enter as you control it precisely....

MrRosary

kev69

Thanks, I'll try that out and get back to you.
MrRosary, I still need a search as not many people will know which postcodes in the list are the ones nearest to them, but thanks for the tip, I'll be able to use that elsewhere.

kev69

success!!!!!!!
check this out
http://yfc.co.uk/local-search.htm
Thanks ultraslacker

ultraslacker

Nice looking site!

You may want to addslashes to the user postcode - currently a postcode of '..' will match all locations.