Jump to content

Archived

This topic is now archived and is closed to further replies.

CanMan2004

Query with closest

Recommended Posts

Hi all

I 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 explain

My 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 if

NN7 6TH

is searched, and only 5 results are found, it would look for

NN7 6TI

then

NN7 6TJ

until 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 for

NN7 5TH

then the next postcode it checks is

NN7 5TI

then

NN7 5TJ

then

NN7 5TK

and 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";
?>
<?php
function 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 advance

Dave

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
[quote author=CanMan2004 link=topic=108626.msg437264#msg437264 date=1158678236]
It seems to Shoz

So 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 btw

NN7 11A

or

NN7 120

Share this post


Link to post
Share on other sites
How can you do a > on text YO30 Rbd is greater than AB10 6QW  2 totaly difrent parts of the country?

Des.

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
string comparisons are done alphabetically. 

a < b because 'a' comes first in the alphabet. 
alex < andrew because 'l' comes before 'n' in the alphabet
YO30 Rbd > AB10 6QW becaue 'Y' comes after 'A' in the alphabet

just think about it like alphabetizing names, only you are using random letters and numbers.  Even throwing in numbers it's still the same

Y0B3C < 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 C

and 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.

Share this post


Link to post
Share on other sites
[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 alphabet
YO30 Rbd > AB10 6QW becaue 'Y' comes after 'A' in the alphabet

just think about it like alphabetizing names, only you are using random letters and numbers.  Even throwing in numbers it's still the same

Y0B3C < 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 C

and 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.

Share this post


Link to post
Share on other sites
[quote author=otuatail link=topic=108626.msg437360#msg437360 date=1158685748]
A=65  a=97 therefore Z > A but a > Z

need 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.

Share this post


Link to post
Share on other sites
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/

Share this post


Link to post
Share on other sites
Hi again

Okay, 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 following

If you search for

NN6 7YH

then I want it to look for

NN6 7YI

then

NN6 7YJ

and 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 for

MK8

for example, it should return

MK9

but instead it returns results for postcodes

S10 4JQ

then

ST16 3BS

then

PO5 1JG

So why is it ignoring MK9 if MK8 is searched?

Share this post


Link to post
Share on other sites
Hello again

if

NN7

is searched, it should look for

NN8

then

NN9

then

NN10

for example, but if

NN8 7YH

is searched, it should look for

NN8 7YI

then

NN8 7YJ

then

NN8 7YK

and so on.

Can this be done?

Share this post


Link to post
Share on other sites
[quote author=CanMan2004 link=topic=108626.msg437484#msg437484 date=1158695337]
Hi again

Okay, 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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.