VERY complex search query!! help!

Space_Cowboy

Hi guys, need a little help with a search query as im at a bit of a loss on how to start this.

I have a database that lists restraunts, each has an address. Inparticular there is a field that contains a valid UK style Postcode (ie HU1 1QE).

I am trying to write a search query that will search for restraunts based on user input of part of the postcode.

So the user might type in "H" or "HU" or "HU1" for example. Hoever some UK postcode might start more like this "GL33" or "G11"

After user input and submit I need to:
- check that the first character is alphabetic.
- If a second character exists, it must be in the range A-Z or 0-9.
- If a third character exists, it must be in the range 0-9.
- If a fourth character exists, it must be in the range A-Z or 0-9.

If G was entered, match only on G postcodes and not GL – in other words, the second character of a retrieved postcode will be numeric for a single ‘G’ to match.

Any help much appreciated.

tomhath

This should be easy with a regular expression. http://dev.mysql.com/doc/refman/5.1/en/regexp.html

If you don't know how to write a regex this is a good time to learn, they're kind of cryptic but very powerful.

Roger_Ramjet

You can use the following regex to validate UK postcode:

$bs7666 = '(GIR 0AA|[A-PR-UWYZ]([0-9]{1,2}|([A-HK-Y][0-9]|[A-HK-Y][0-9]([0-9]|[ABEHMNPRV-Y]))|[0-9][A-HJKS-UW])[0-9][ABD-HJLNP-UW-Z]{2})';

which is the published British Standard for Post Code format

$allexisting = '(((^[BEGLMNS][1-9]\d?) | (^W[2-9] ) | ( ^( A[BL] | B[ABDHLNRST] | C[ABFHMORTVW] | D[ADEGHLNTY] | E[HNX] | F[KY] | G[LUY] | H[ADGPRSUX] | I[GMPV] | JE | K[ATWY] | L[ADELNSU] | M[EKL] | N[EGNPRW] | O[LX] | P[AEHLOR] | R[GHM] | S[AEGKL-PRSTWY] | T[ADFNQRSW] | UB | W[ADFNRSV] | YO | ZE ) \d\d?) | (^W1[A-HJKSTUW0-9]) | (( (^WC[1-2]) | (^EC[1-4]) | (^SW1) ) [ABEHMNPRVWXY] ) ) (\s*)? ([0-9][ABD-HJLNP-UW-Z]{2})) | (^GIR\s?0AA)';

which validates all existing postcodes, which may change in the future

These will of course only work against full postcodes. What you are asking is how to match just the first part, the Incode, which identifies the sorting office and hence geographic location. For that you just need:

$incode = 'GIR 0AA|[A-PR-UWYZ]([0-9]{1,2}|([A-HK-Y][0-9]|[A-HK-Y][0-9]([0-9]|[ABEHMNPRV-Y]))|[0-9][A-HJKS-UW])';

You should realise that incodes cover very different sized areas, and using the leading character(s) will not work for a geographic search: eg (from my postcode database)
('CB3', NULL, '1AA', 'AZZ', 'CAMBRIDGE', 'Cambridgeshire'),
('CB4', NULL, '1AA', 'AZZ', 'CAMBRIDGE', 'Cambridgeshire'),
('CB5', NULL, '1AA', 'AZZ', 'CAMBRIDGE', 'Cambridgeshire'),
('CB6', NULL, '1AA', 'AZZ', 'ELY', 'Cambridgeshire'),
('CB7', NULL, '1AA', 'AZZ', 'ELY', 'Cambridgeshire'),
('CB8', NULL, '1AA', 'AZZ', 'NEWMARKET', 'Suffolk'),
('CB9', NULL, '1AA', 'AZZ', 'HAVERHILL', 'Suffolk'),
('CB10', NULL, '1AA', 'AZZ', 'SAFFRON WALDEN', 'Essex'),
('CB11', NULL, '1AA', 'AZZ', 'SAFFRON WALDEN', 'Essex'),
('CB21', NULL, '1AA', 'AZZ', 'CAMBRIDGE', 'Cambridgeshire'),

('AL5', NULL, '1AA', 'AZZ', 'HARPENDEN', 'Hertfordshire'),
('AL6', NULL, '1AA', 'AZZ', 'WELWYN', 'Hertfordshire'),
('AL7', '1AA-9AE', '1AA', '9AE', 'WELWYN GARDEN CITY', 'Hertfordshire'),
('AL7', '9AG', '9AG', '9AG', 'WELWYN', 'Hertfordshire'),
('AL7', '9AH-9DA', '9AH', '9DA', 'WELWYN GARDEN CITY', 'Hertfordshire'),
('AL7', '9DB-9DD', '9DB', '9DD', 'WELWYN', 'Hertfordshire'),
('AL7', '9DL-9EL', '9DL', '9EL', 'WELWYN GARDEN CITY', 'Hertfordshire'),
('AL7', '9EN', '9EN', '9EN', 'WELWYN', 'Hertfordshire'),
('AL7', '9EP-9ER', '9EP', '9ER', 'WELWYN GARDEN CITY', 'Hertfordshire'),
('AL7', '9ES', '9ES', '9ES', 'WELWYN', 'Hertfordshire'),
('AL7', '9ET-9SA', '9ET', '9SA', 'WELWYN GARDEN CITY', 'Hertfordshire'),
('AL8', NULL, '1AA', 'AZZ', 'WELWYN GARDEN CITY', 'Hertfordshire'),
('AL9', NULL, '1AA', 'AZZ', 'HATFIELD', 'Hertfordshire'),
('AL10', NULL, '1AA', 'AZZ', 'HATFIELD', 'Hertfordshire'),

Here, just using AL7 will give you towns that are 10 miles apart.

So you must have the full incode for a geographic location search

Space_Cowboy

Thanks for the advice guys....

Ive got it partly working. This is what I have so far:

<?php

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

SELECT * FROM account.postcode REGEXP '^" . substr($postcode, 0, $postcode_initial) . "[0-9].*'

?>

This works in that if you search "H" it brings all postcodes starting with "H" and not "HU".

But its not searching the 3rd and 4th characters entered. So if you search "HU33" its displaying results that are "HU71".

Half way there :eek:

johanafm

Well, what you do is:
1. match any two characters that are not digits (0-9) against $postcode.
2. assign 1 plus the number of times the above matches to $postcode_initial. Also, since you are using preg_match and not preg_match_all, this value will either be 0 or 1. So the assignment to $postcode_initial is always 1 or 2, regardless of what you have in $postcode!
3. Based on the above, take either the first character or the first two characters of $postcode.
4 Create a regexp based on those 1 or 2 characters, require that it/they are followed by one didigt (0-9) and then any amount of any characters and use this in the SQL query.

Now, let's assume I search for ".34". Then you're query will be:

SELECT * FROM account.postcode REGEXP '^.*[0-9].*'

In other words, now it will match any string containing at least one digit somewhere.

And for another example: "34AB"

SELECT * FROM account.postcode REGEXP '^3[0-9].*'

Which matches 3 + one digit (0-9) followed by anything.

But if you only need the user input to retrieve information allready in the database, why bother with regexp?

$sql = "SELECT * FROM account WHERE postcode like '$postcode%'"

I'd only worry about checking the post code validity when you enter a new post code into the database.

Space_Cowboy

I guess the idea is that If you type in "H" (it will only display results for "Hxx" - 'xx' being a number and not find results for "HU".
Searching for HU3, wont bring up results for "HU33" etc

Im still stuck on this!!

REGEXP is relativley new to me, and ive played about with this for a couple of hours now but have not as yet been able to crack it :eek:

I your examples, where does the following code fall into?
. substr($postcode, 0, $postcode_initial) .

Any help appreciated

johanafm

Add a third argument to the call to preg_match and use that instead of the return value. The return value is the number of matches, the third function argument is the matches themselves.

I fail to see why a search for 'H' should bring only matches for H followed by a digit and not H followed by another character, but perhaps that's due to my lack of knowledge of the UK postal code system. It still does seem strange to me though.

But in that case, go with a pattern like

$pattern = '#\D*#';

Space_Cowboy

ummm... Ive added the 3rd argument, like so:

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

$GetContact .= " account.postcode REGEXP '^" . substr($postcode, 0, $postcode_initial) . "[0-9].*' AND";

but its still not working?!

The reason for it being like this, is for example:
G is Glasgow and GL is Gloustershire

People who are searching for a restraunt in Glasgow (via "G") will not want results from Gloustershire (which is a completly different part of the country).

cheers

johanafm

Are you aware of the site php.net? They have documentation for the PHP language.
Having a look at the docs for preg_match tells us just by looking at the function definition

int preg_match  ( string $pattern  , string $subject  [, array &$matches  [, int $flags  [, int $offset  ]]] )

the the first 2 arguments MUST be supplied, while the 3rd is optional. Furthermore it tells us that:
- Argument 1: Should be string
- Argument 2: Should be string
- Argument 3: Should be an array. It is also passed by reference which is how we get the matching data back from the function.

Now, let's have a look at your code again:

$postcode_initial = preg_match("/^\\D\\D/", $postcode, "#\D*#");

You supply 3 strings as arguments. I've no idea if this code will break or not, but even if it "works", that's besides the point. You supply a string literal as the third argument, which means that it does indeed have a memory location.
So if this does "work", that would be because your string literal is converted into an array (the actual mechanics may not work in exactly this way, but for all practical purposes that's what it means to us), but you don't have a reference to that memory, which means that you can't access the found matches afterwards.

Moreover, to quote myself

But in that case, go with a pattern like
PHP Code:
$pattern = '#\D*#';

Having another look at the above information:
Where do you think that pattern is supposed to go?
What would you supply as the third argument to preg_match

Roger_Ramjet

but its still not working?!

The reason for it being like this, is for example:
G is Glasgow and GL is Gloustershire

People who are searching for a restraunt in Glasgow (via "G") will not want results from Gloustershire (which is a completly different part of the country).

cheers

Which is precicely what I pointed out in my post - with examples.

Now, the regular expressions that I gave you will validate a full postcode. Once you know that it is a viable and valid postcode you can start to match it. (You will of course have validated the postcodes that you have stored in the database and against which you are searching.)

Now, I use a post office database that we purchased for £50. You can see it here

With that, you could look up the user input to get the town/county and use those to look up your restaurants: and you can do all of that in 1 query with suitable joins.

There is a better way to do this, by the way. Ordinance Survey now publish the geographical location of each postcode here. What your search engine is really trying to do is to find restaurant within a given radius of a supplied location. This radius will cross postcode areas so the simplistic search methods you are using is not very usefull. For instance, I have a Leeds postcode, but I live 10 miles from the centre of Leeds. Leeds postcode on the other side of Leeds would not be anywhere near me. On the other hand, a restaurant in Skipton or Shipley would be close to me but would have a Bradford postcode. So it is up to you if you think your revenuse will support the purchase of the right tools to do the right job. Certainly, £50 for the postcode db is trivial.