Hi,

I've got a database that includes a postal code.

Now, what I'd like is the following:

A visitor types in his postal code, and then the record with the nearest postal code in the database should be returned.

I found a script that does this more or less, but I don't know how to integrate it in dbman.

Can anybody help?

This is the code:


Thanks,

Lex

The code you copy here seems rather strange to me, it has a couple of errors.
What are the conditions that determine whether a postal code is "nearest"?
Do you have a list of postal codes that you want to compare each entry with?
Does this list come from an external source or is it the list of all values for the field "postal_code" in your database?
Assuming that you want the information printed out in search results, you'd have to do whatever needs to be done in sub html_record, where the current postal code for the record will be stored in $rec{'postal_code'} if "postal_code" is the name of the database field which has the code.




kellner

Hi Kellner,

well, the way I see it: Every record in my database has a field, for example postal_code. Now, if a visitor of the site fills in his postal code, I need a tool that compares the visitor's code to the ones in my database. Only the postal code that's most similar (like normal numbers 8015 is nearer to 9000 than 8014), that record should be produced.

I know the way postal codes work doesn't always guarantee this, but in my case, in the Netherlands, and only 33 addresses stored throughout the country it will be adequate.

I would like to know if somebody can tell me how I should do this... What you say about sub html_record is true I guess, only, this calculation has to be done before I print out any result, and I don't know where to put the calculation, and what the calculation should look like.

It's more or less just a sorting routine, the record with the nearest postal_code is the one we're looking for.

The code I inserted seems to do the trick, only, obviously, it's a stand alone code and in this case I don't know how to insert it into dbman.

Lex

For clarification:
1) list of postal codes = all values of field postal_code (or whatever it's called in your db) in your db
2) postal codes will be numeric
3) "nearest" = number with smallest difference. Of the list "5 8 10", 10 will be nearest to 8.

It's not that difficult, the only puzzling case (at least for me) is when there are TWO list elements with identical smallest difference.
Example: list = "123, 127, 230"
postal_code = "125".
Now we have both 123 and 127 with the same distance from 125. Most probably we want to retrieve both values - any ideas, anyone? I suspect there would be a way to do that with references, but my reference skills are insufficient ...


kellner

Hi Kellner,

"postal codes will be numeric..." They won't really, but that's not the big problem. Dutch postal codes look like this:

8531 HK

But as there aren't 2 branches in the same town, just looking at the first 4 digits will be okay.

as for 5 8 10, 10 is nearest to 8... Depending on how you mean this. I would say: 5 8 10, then 7 is nearest to 8, I guess this is clearer, just the way you put in your example (123, 127 etc.)

Hope we can get this to work...

Lex

Hi Lex,

OK, the following code seems to work also when there are two nearest numbers (like e.g. "1" and "3" would both be nearest to "2"). It's clumsy, but perhaps someone else can do a bit of shortening.
Note: the comparison operates ONLY on the numbers in your postal code. I don't know the Dutch postal code system, but, for instance, if you have two codes like "1234 AB" and "1234 HR", both will be considered nearest to "1233 XY" - the letters are ignored here.
- Replace "postal_code" by the real name of the postal code field from your cfg
- I assume postal code to be a string of numbers, followed by ONE space, followed by a string of letters. If there's more than one space, the code will need to be changed a bit
- The code compares the numbers, but prints out also the letters.

1) Add this to your html_record, somewhere after the line "my %rec = @_;"

2) Add this subroutine somewhere in html.pl

Let me know if it works. Cheers,
kellner

Hi Kellner

I'm working on this right now, and seems to be doing what I want! Thanks a lot for that! (still not finished though...)

How should I go about if I would want the ID number to show with the near Postal_code (s) so I can turn it into a link?

maybe this sounds strange, but I'm using links 2 and dbman combined, dbman to search the links database.

And.. (I know this is going to sound stupid) how do I 'transfer' the 'inputnumber' to the program. What does the form for this look like? Do I use the postal_code field, or can I give it a new name, something like 'postal_query', send it to db.cgi and insert an if statement in html.pl, if postal_query exists, than... or is ridiculous?

With other words: how I see it:

on a page there is a input field, that says: insert your postal code here.

by submitting I only get the record(s) back with the nearest postal codes.

I'm pretty new with this, so thanks for ignoring my ignorance!

cheers

Lex

Hi Lex,

glad it works.

If you want to call this *always* via a form, skip the modifications to html_record that I posted last time, and do this:

1) code a form and add it on whatever page you want to use to pull it:

2) In db.cgi, sub main, you'll find a lot of lines beginning with "elsif". Add this one:
This is needed to inform dbman which subroutine to call when the submit button called "view_nearest" is pressed.

3) replace get_nearest_postal_code from my last message with the following code:

sub get_nearest_postal_code {
&html_page_top;# or &html_print_headers - see how
#html_view_success starts and use the same code to print out
# the header and beginning of the page

my $inputnumber = $in{'postal_code'}; # postal_code = name of form field
my (@postal_code, %difference, %letters, @nearestnumber, %id);

open (DB, "<$db_file_name") || &cgierr ("can't open $db_file_name: $!");
my $pos = $db_def{'postal_code'}[0];
my $id_pos = $db_def{'ID'}[0]; # assuming "ID" is the name of your ID field

while (<DB>) {
chomp;
my @line = split/\|/, $_;
push (@postal_code, $line[$pos]);
$id{$line[pos]} = $line[$id_pos];
}

foreach my $postal_code (@postal_code) {
if ($postal_code =~ /(\d+)(\s)(\w+)/) {$postal_code = $1; $letters{$postal_code} = $3;}
$difference{$postal_code} = abs($postal_code-$inputnumber);
}

foreach my $key (sort {$difference{$a} <=> $difference{$b} } keys %difference) {
push (@differencearray, $difference{$key}); }

foreach my $key (keys %difference) {
if ($difference{$key} == $differencearray[0]) {
$key = "$key $letters{$key}";
push (@nearestnumber, $key);} }

foreach my $nearestnumber (@nearestnumber) {
my %rec = &get_record{$id{$nearestnumber}};
&html_record(%rec);
}
}

This is untested, but unless I've skipped a bracket here and there, it should work.
NOTE: this assumes, for a start, that your postal_code values will be unique. If they're not, some changes will have to be made.

Best,
kellner

Hi Kellner,

first of all... Thanks a lot for your help!!!

When I try it, I get an error message;


line 1336 is the second line in this code:

Replace the line with this:
my %rec = &get_record($id{$nearestnumber});
made a mistake with the round brackets, sorry.
kellner

You shouldn't being saying sorry!

Anyway, I made the change, but the result is an empty record; sub html_record is used, but no fields are filled in...

I added this near the end of the script:


And it does work; it produces the nearest Postal_code.

So the trick is in the last stuff...


If I add:


in the first piece of code in this message (next to print "$nearestnumber\n<p>"; ) than it is emtpy...

(going on trying...)

Lex

ok, let's do a simple print checks.

After the line "$id{$line[pos]} = $line[$id_pos];"
add this line:
print "Record ID in Hash: $id{$line[pos]}, Record ID: $line[$id_pos], Postal_code: $line[$pos]<br>";

If the code is working, then this should print a list of all record IDs and the matching postal codes, plus - this is the first item - it should check whether the record id is properly stored in the hash.

Just to make sure: Are you using the field "ID" as the database key? Otherwise, get_record won't work.
kellner

Hi Kellner,

Your line does what it's supposed to do, I get all the different ID's (1 to 30 in this case, 30 records) with all their postal_codes, no problem, and yes, I have the ID field as key...

$db_key = 'ID';

...

But Kellner, as I wrote in my last message, if I add this to your code (near the end):


than the line print "$id{$nearestnumber}"; is empty. Is this not the problem, that in the end there's nothing sent to get_record? The line print "Closest hit to $inputnumber is: $nearestnumber\n<p>"; works just fine.

What do you think?

And... I'm really pushing this I know, but even if your support would stop to continue, I'd be really very thankful... If I add this line to the form <input type=hidden name="Categorie" value="Installatie"> it doesn't do anything. The nearest postal code that is returned remaines the same one as before, even if that record has another value in the 'categorie' field. Is there a way with your code to achieve this however?


Lex

I'll check on the problem with the lacking ID value later.
As for your other question, I don't understand it.
Simply adding a hidden input field to a form doesn't do anything, you have to tell dbman what you want it to do by coding a condition like:

if ($in{'name_of_hidden_input_field'}) {# do stuff}

or

if ($in{'name_of_hidden_input_field'} eq "somevalueorother") {# do stuff}

to the subroutine that the form triggers (for searches: html_view_success, for this example: get_nearest_postal_code).
kellner

ARGH, it was just a typo:
should be:

- "pos" inside the square brackets needs a dollar sign.
Let me know if it works.

kellner

Kellner Kellner....

It works just perfectly !!!



I mean this: THANKS A LOT !!!

Do you think I should put this together, all nice and clear and put it up as a mod? I mean, it amazed me that this hadn't been asked before (or I couldn't find it). It's a good feature for everybody who has a database of, for example, shops.

What do you think?

Thanks a 1000 times!

Lex

Alright, I get your point.

What I wanted to do was, for example: say this is a database of cats and dogs. Now, the visitor of the site wants to know his nearest cat, than the action: get_nearest_postal_code should check only the records that have in the field 'animal' the value 'cat'.

That's better isn't it.

Lex

So this would meaing combining the postal code routine with a search form: dbman searches the database for all records where "category" equals "dog" (or "cat"), and then returns the nearest postal code for each record printed right next to the record output?

Like:
"2 Search result(s) for category "cat".

Cat's name: Fluffy
Age: 5
Postal code: 2456 AB, nearest postal code: 1234 HQ

Cat's name: Puffy
Age: 2
Postal code: 1234 AB, nearest postal code: 2345 CD"

Question: Would you "nearest postal code" mean nearest postal code in general, or only nearest postal code for the same category? Would you only want to know the nearest cats, or the nearest pets?
kellner

no no no

a lot simpler (I hope)

The database is of pets all throughout the country.

I want to find the nearest 'cat'

So I type in my postcode, select 'cat' and hit go.

'cat' is just a value of the field 'pet'

The result is the record that has 'cat' and is nearest to my postal code.

Is it now clearer? Or should I use another example in stead of finding the nearest cat in your country through an online database, that is rather stupid isn't it?

By the way, is your name Birgit and do you live in austria?

(I didn't understand the fast replies at this time of day...)
(but if you're called Birgit, than it's very logical)
(...)

Lex

Erm, yes, my name is Birgit and I happen to live in Austria.
Are you the guy standing on the roof across the street waving frantically? :-)

As for the code - the following modified sub should do it.

Assumptions:
- the "category" field that you want to base your selection on is called "category" in the cfg-file.
If not, replace "$db_def{'category'}" with "$db_def{'whatever_your_category_field_is_called'}".
- the input field in the form that you use to call this routine is called "category". If not, replace
$in{'category'} with "$in{'whatever_your_form_field_is_called'}".

In the form, you can choose whatever form of input field you like, I guess: A dropdown list,
checkboxes, radio buttons, or a text field. Just make sure its name is that in "$in{'whatever_your_form_field_is_called'}".


sub get_nearest_postal_code {
&html_page_top;# or &html_print_headers - see how
#html_view_success starts and use the same code to print out
# the header and beginning of the page

my $inputnumber = $in{'postal_code'}; # postal_code = name of form field
my (@postal_code, %difference, %letters, @nearestnumber, %id);

open (DB, "<$db_file_name") || &cgierr ("can't open $db_file_name: $!");
my $pos = $db_def{'postal_code'}[0];
my $id_pos = $db_def{'ID'}[0]; # assuming "ID" is the name of your ID field
my $category_pos = $db_def{'category'}[0]; # assuming "category" is the name of your category field

while (<DB>) {
chomp;
my @line = split/\|/, $_;
if ($line[$category_pos] eq $in{'category'}) {
push (@postal_code, $line[$pos]);
$id{$line[$pos]} = $line[$id_pos];
}
}

foreach my $postal_code (@postal_code) {
if ($postal_code =~ /(\d+)(\s)(\w+)/) {$postal_code = $1; $letters{$postal_code} = $3;}
$difference{$postal_code} = abs($postal_code-$inputnumber);
}

foreach my $key (sort {$difference{$a} <=> $difference{$b} } keys %difference) {
push (@differencearray, $difference{$key}); }

foreach my $key (keys %difference) {
if ($difference{$key} == $differencearray[0]) {
$key = "$key $letters{$key}";
push (@nearestnumber, $key);} }

foreach my $nearestnumber (@nearestnumber) {
my %rec = &get_record($id{$nearestnumber});
&html_record(%rec);
}
}
kellner

Hi Birgit (or 'Kellner'),

I'm not waving, I'm throwing dead cats out of the window that gave their life for our little research.

Although I can now say the result is very satisfying, I do honor those that on the way of achieving our goals gave their time and energy, like yourself, or even gave their lives, as the earlier mentioned cats did.

Therefor, I am very grateful and very much in debt. If ever I can do anything I'd be very happy to hear from you (not the cats!).

Do you think I should send this stuff in as a mod?

Lex

Hi,

Sorry to be fussy.....I'm not being patronising....

my @line = split/\|/, $_;

Can be written as....

my @line = split/\|/;

no no, your comments are always welcome,
as I'm not particularly good at writing simple code anyway ...
kellner

ok, so the nearest postal code is that for the nearest cat's burial place?
Jokes aside, I think the code is a bit too specific to a particular problem to be written up as a mod - what with the specific postal code format -, and I think people can find it through this forum anyway.
One could turn it into a more general mod, like "nearest numerical value for a database field value", if there's sufficient interest. Is there, she shouted out into the huge virtual space that this forum hopefully is.
kellner

One more question....

I've replaced the category field with a field with checkboxes. It works perfectly, except when no record is found that has all the checkbox items in its field.

In that case, it produces the main db.cgi screen, as if no action has been sent to it.

Where can I insert the stuff; if @postal_code is empty than do this...

I can manage the "do this", but not the if @postal_code is empty...

Thanks for any idea!

Lex