Gossamer Forum
Home : Products : Gossamer Links : Discussions :

SQL Monitor - Matching Link Zip Codes to Cities Question

Quote Reply
SQL Monitor - Matching Link Zip Codes to Cities Question
I'm trying to add corresponding Zip Codes to my existing city locations that are already in the link's "Description" Field. I created a new "Zip" field to put in each link's Zip code. I also added <%Zip%> to the links template page. It seems to work fine.

My question is - Is there a quick and dirty way to add the actual Zip code of the link automatically to the corresponding link that already has the City name in the Description field using the SQL Monitor. What SQL statement would I use to make this happen?

As an example I would want 44121 to appear in the <%Zip%> field when everytime "South Euclid, Ohio" appears in the Description field.

Is this possible?? Any assistance would be very much appreciated. I have over 35,000 links in my db but am looking for a "quick and dirty" way to accomplish this task.

mgeyman
Quote Reply
Re: [mgeyman] SQL Monitor - Matching Link Zip Codes to Cities Question In reply to
You could use something like the following:

UPDATE Links_Links
SET Zip = '44421'
WHERE (Description = 'South Euclid, Ohio')

However, it would be difficult to automate the process more than the above example, since as you probably know there are multiple zip codes in cities.

Example:

Where I live, there are six zip codes that are represented in the city where I live.

Therefore, the accuracy of inputting zip codes on the fly or even through an SQL statement will be limited.
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [Stealth] SQL Monitor - Matching Link Zip Codes to Cities Question In reply to
Thanks so much. Yes, I do realize in cities with multiple zips, I'll have to go through all the sites in question to get the correct Zip code.



mgeyman
Quote Reply
Re: [mgeyman] SQL Monitor - Matching Link Zip Codes to Cities Question In reply to
Hi,



Did you reverse the process for new sign ups?

IE: Enter zip code and the city comes up

That would be easier for the user (but harder for us)Cool
Quote Reply
Re: [Teambldr] SQL Monitor - Matching Link Zip Codes to Cities Question In reply to
Yes, you are right. I feel due to the problem of multiple zip codes available to each city, it's difficult to manage.
>> Nakul Goyal (SEO, Link Building Expert)
Web Site Promotion
Tips 'n' Tricks
SEO News


Quote Reply
Re: [nakulgoyal] SQL Monitor - Matching Link Zip Codes to Cities Question In reply to
In another thread, I mentioned the HamWeather program http://hamweather.com

You might be able to use their cities/database look up, and insert the correct zip for each city.

For cities with more than one zipcode, use the frist 3 numerals, which usually defines the region, or the zip of the main post office. You can then make an "update this zip" link for any zip in the "general" list, such that someone can enter the correct zip code (similar to a "Bad Link" link).

Then, use the suggestion before, have a person enter their zip, and look up the city.

If you don't need that sort of targeted accuracy, just use the first 3 digits, modify the search script to first seek the entered zip, and if that fails, to seek out the first three digits. IF only ONE link (the main city/post office) has a 3-digit zip, then only that will come up as the "central" location. Again, offer an 'Update this Zip" link when that happens.

Tighter integration of Ham Weather and Links SQL via the postcards.cgi plugin is planned, but down on the list.... waaaaay down on the list <G>


PUGDOG´┐Ż Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [mgeyman] SQL Monitor - Matching Link Zip Codes to Cities Question In reply to
If you have a complete address for your links, you can use the USPS website and screen scrape the correct Zip+4 for each link. You'll have craft your own PERL script to do it - I strongly recommend that you do it in small batches to keep from "abusing" this site. I have attached a small perl program that works to get you started.

- Tom Ransom
Quote Reply
Re: [transom] SQL Monitor - Matching Link Zip Codes to Cities Question In reply to
I agree with Tom Ransom. I think this could work well.
>> Nakul Goyal (SEO, Link Building Expert)
Web Site Promotion
Tips 'n' Tricks
SEO News