CanMan2004 Posted September 19, 2006 Share Posted September 19, 2006 Hi allI have been working on a postcode searcher and wondered if someone can give me some help.Basically, what im doing it to have a form which you can enter a postcode into, click a button and it does a query on a database of users, it then returns the list of users which match the postcode, there is a limit of 10 which should be found.Let me explainMy inital query looks like[code]SELECT * FROM users WHERE `post_code` LIKE '%".$postcode."%' LIMIT 10[/code]This then returns all the rows which postcode partially match. I then count the results found and deduct 10 from the total found, using[code]$num = mysql_num_rows($show);$total = 10 - $num;[/code]I then run an 'if' statement to run another query if less than 10 results are found, this is the part im stuck on.What I want to be able to do is to run (if less than 10 rows are found) a query (which I will place inside my 'if' statement) which would return rows which partically matched the one searched for, so ifNN7 6THis searched, and only 5 results are found, it would look forNN7 6TIthenNN7 6TJuntil it has found a total of 10, I can set a limit on how many are returned, using LIMIT $total, I can do this limit as I know how many rows have been found in the first postcode query (see top of this post).What I have been doing, is to use a script which some people on here helped with, and use it for this postcode search, basicaly, it takes the postcode that was searched for, and keeps trying combinations, so if you search forNN7 5THthen the next postcode it checks isNN7 5TIthenNN7 5TJthenNN7 5TKand so on, the only problem is, there are loads of postcode combinations it might need to try to return a result, and using this code below takes the page forever to fully load (as its checking so many combinations). The code is as follows;[code]<?$sql = "SELECT * FROM `users` WHERE";?><?phpfunction IncNextPos (&$id, $pos) { $min = is_numeric($id{$pos}) ? '1' : 'A'; $max = is_numeric($id{$pos}) ? '99' : 'Z'; if ($id{$pos}==$max) { $id{$pos} = $min; IncNextPos($id,$pos-1); } else { $x = $id{$pos}; $x++; $id{$pos} = $x; } }function calc_next ($id) { $pos_space = strpos($id, ' '); $b = str_replace(' ', '', $id); // remove space IncNextPos($b,strlen($b)-1); return substr($b,0,$pos_space) . ' ' . substr($b, $pos_space);}$next = $temppostcode;for ($i=0; $i<20000; $i++) { $next = calc_next($next);$sql = $sql. " `post_code` = '$next' ||";}}?><?$sql = $sql. " `post_code` = '' LIMIT 10";$show = @mysql_query($sql,$connection) or die(mysql_error());while ($rows = mysql_fetch_array($show)) {{?><? print $rows['post_code']; ?><?}?>[/code]Is this a better way to do this? Or do I need to explain this better?Thanks in advanceDave Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 19, 2006 Author Share Posted September 19, 2006 Is there a way to speed up the query? Quote Link to comment Share on other sites More sharing options...
shoz Posted September 19, 2006 Share Posted September 19, 2006 Does this give the desired result?[code]SELECT * FROM users WHERE `post_code` > '$postcode' ORDER BY post_code LIMIT 10[/code]You should also put an index on post_code[code]ALTER TABLE users ADD INDEX(post_code)[/code] Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 19, 2006 Author Share Posted September 19, 2006 It seems to ShozSo what is the > telling the query to do? Quote Link to comment Share on other sites More sharing options...
shoz Posted September 19, 2006 Share Posted September 19, 2006 [quote author=CanMan2004 link=topic=108626.msg437264#msg437264 date=1158678236]It seems to ShozSo what is the > telling the query to do?[/quote]Return the row if the value on the left is greater than the value on the right. In this case it does a string comparison, determining if the string on the left is greater than the other. If two users can have the same postal code, it should actually be ">=".Which is closer to NN7 110 btwNN7 11Aor NN7 120 Quote Link to comment Share on other sites More sharing options...
otuatail Posted September 19, 2006 Share Posted September 19, 2006 How can you do a > on text YO30 Rbd is greater than AB10 6QW 2 totaly difrent parts of the country?Des. Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 19, 2006 Author Share Posted September 19, 2006 Good question Quote Link to comment Share on other sites More sharing options...
shoz Posted September 19, 2006 Share Posted September 19, 2006 [quote author=otuatail link=topic=108626.msg437296#msg437296 date=1158680679]How can you do a > on text YO30 Rbd is greater than AB10 6QW 2 totaly difrent parts of the country?Des.[/quote]The query is based on the explanation given. Quote Link to comment Share on other sites More sharing options...
.josh Posted September 19, 2006 Share Posted September 19, 2006 string comparisons are done alphabetically. a < b because 'a' comes first in the alphabet. alex < andrew because 'l' comes before 'n' in the alphabetYO30 Rbd > AB10 6QW becaue 'Y' comes after 'A' in the alphabetjust think about it like alphabetizing names, only you are using random letters and numbers. Even throwing in numbers it's still the sameY0B3C < Y0B4A in a string comparison because Y0B are the same but when it gets to the 4th character, 3 < 4. this query will only "work" if the zipcodes are arranged in such a fashion that the closer you are to someone, the more similar your zipcodes are. if you have 2 people on either side of you:Person A <- You -> Person B -> Person Cand your zipcode is 123 and person A's zipcode is 124 but for some reason person B's zipcode is 910 and some other person C who is farther away has 212, then it's not going to work. But that's not really your fault; that's your post office's fault. If they arranged zipcodes in some random fashion like that, then there's not much else you can do but hardcode a map or something. Quote Link to comment Share on other sites More sharing options...
shoz Posted September 19, 2006 Share Posted September 19, 2006 [quote author=Crayon Violent link=topic=108626.msg437320#msg437320 date=1158683079]string comparisons are done alphabetically. a < b because 'a' comes first in the alphabet. alex < andrew because 'l' comes before 'n' in the alphabetYO30 Rbd > AB10 6QW becaue 'Y' comes after 'A' in the alphabetjust think about it like alphabetizing names, only you are using random letters and numbers. Even throwing in numbers it's still the sameY0B3C < Y0B4A in a string comparison because Y0B are the same but when it gets to the 4th character, 3 < 4. this query will only "work" if the zipcodes are arranged in such a fashion that the closer you are to someone, the more similar your zipcodes are. if you have 2 people on either side of you:Person A <- You -> Person B -> Person Cand your zipcode is 123 and person A's zipcode is 124 but for some reason person B's zipcode is 910 and some other person C who is farther away has 212, then it's not going to work. But that's not really your fault; that's your post office's fault. If they arranged zipcodes in some random fashion like that, then there's not much else you can do but hardcode a map or something. [/quote]As this is a response to the query rather than the OP's approach, I can only say that the query gives what was asked for. Quote Link to comment Share on other sites More sharing options...
otuatail Posted September 19, 2006 Share Posted September 19, 2006 A=65 a=97 therefore Z > A but a > Zneed to capitalise the data Quote Link to comment Share on other sites More sharing options...
shoz Posted September 19, 2006 Share Posted September 19, 2006 [quote author=otuatail link=topic=108626.msg437360#msg437360 date=1158685748]A=65 a=97 therefore Z > A but a > Zneed to capitalise the data[/quote]The comparison is done case-insensitively. The default collation/sort order is case-insensitive.It would be good practice to ensure that both the data in the tables and the search string are in upper case however. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 19, 2006 Share Posted September 19, 2006 IF you remember in your other post on this topic, I asked you to define "closest" and got no meaningful reply. If you are trying to find "geographically" closest postcode then you need to know the locations of the postcodes.SK11 and SL11 may be close alphabetically but a couple of hundred miles apart geographically. The data obtainable from this site (grid coords and lat/long) should help.http://www.jibble.org/ukpostcodes/ Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 19, 2006 Author Share Posted September 19, 2006 Thanks, that is a great help everyone Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 19, 2006 Author Share Posted September 19, 2006 Hi againOkay, what ive done is changed my query to[code]SELECT * FROM users WHERE `post_code` >= '".$_GET['postcode1']."' LIMIT $total[/code]What ive been trying to do, is to get it to work like the followingIf you search forNN6 7YHthen I want it to look forNN6 7YIthenNN6 7YJand continue to count numbers/letters up until 10 have been found.What it is currently doing, im not too sure now, as when I do a search forMK8for example, it should returnMK9but instead it returns results for postcodesS10 4JQthenST16 3BSthenPO5 1JGSo why is it ignoring MK9 if MK8 is searched? Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 19, 2006 Author Share Posted September 19, 2006 Hello againifNN7is searched, it should look forNN8thenNN9thenNN10for example, but ifNN8 7YHis searched, it should look forNN8 7YIthenNN8 7YJthenNN8 7YKand so on.Can this be done? Quote Link to comment Share on other sites More sharing options...
shoz Posted September 19, 2006 Share Posted September 19, 2006 [quote author=CanMan2004 link=topic=108626.msg437484#msg437484 date=1158695337]Hi againOkay, what ive done is changed my query to[code]SELECT * FROM users WHERE `post_code` >= '".$_GET['postcode1']."' LIMIT $total[/code][/quote]The "ORDER BY" is missing[code]ORDER BY post_code ASC LIMIT 10[/code]ASC (ascending) is the default, but I've shown it above for clarity. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.