Jump to content


Photo

Query with closest


  • Please log in to reply
16 replies to this topic

#1 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 19 September 2006 - 01:40 PM

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

SELECT * FROM users WHERE `post_code` LIKE '%".$postcode."%' LIMIT 10

This then returns all the rows which postcode partially match. I then count the results found and deduct 10 from the total found, using

$num = mysql_num_rows($show);
$total = 10 - $num;

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;

<?
$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']; ?>
<?
}
?>

Is this a better way to do this? Or do I need to explain this better?

Thanks in advance

Dave

#2 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 19 September 2006 - 02:17 PM

Is there a way to speed up the query?

#3 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 19 September 2006 - 02:51 PM

Does this give the desired result?
SELECT * FROM users WHERE `post_code` > '$postcode'  ORDER BY post_code LIMIT 10

You should also put an index on post_code
ALTER TABLE users ADD INDEX(post_code)


#4 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 19 September 2006 - 03:03 PM

It seems to Shoz

So what is the > telling the query to do?

#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 19 September 2006 - 03:27 PM

It seems to Shoz

So what is the > telling the query to do?


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

#6 otuatail

otuatail
  • Members
  • PipPipPip
  • Advanced Member
  • 960 posts

Posted 19 September 2006 - 03:44 PM

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

Des.

#7 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 19 September 2006 - 03:59 PM

Good question

#8 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 19 September 2006 - 04:14 PM

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

Des.

The query is based on the explanation given.

#9 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 19 September 2006 - 04:24 PM

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.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#10 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 19 September 2006 - 04:41 PM

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.

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.

#11 otuatail

otuatail
  • Members
  • PipPipPip
  • Advanced Member
  • 960 posts

Posted 19 September 2006 - 05:09 PM

A=65  a=97 therefore Z > A but a > Z

need to capitalise the data

#12 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 19 September 2006 - 05:40 PM

A=65  a=97 therefore Z > A but a > Z

need to capitalise the data


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.

#13 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,022 posts

Posted 19 September 2006 - 06:21 PM

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/
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#14 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 19 September 2006 - 07:22 PM

Thanks, that is a great help everyone

#15 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 19 September 2006 - 07:48 PM

Hi again

Okay, what ive done is changed my query to

SELECT * FROM users WHERE `post_code` >= '".$_GET['postcode1']."' LIMIT $total

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?

#16 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 19 September 2006 - 08:09 PM

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?

#17 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 19 September 2006 - 08:12 PM

Hi again

Okay, what ive done is changed my query to

SELECT * FROM users WHERE `post_code` >= '".$_GET['postcode1']."' LIMIT $total


The "ORDER BY" is missing

ORDER BY post_code ASC LIMIT 10

ASC (ascending) is the default, but I've shown it above for clarity.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users