Querying postal addresses and phone numbers

[gkelley091565]

Hello,

I am looking to find the best way to verify that a user-entered postal address and/or telephone number exists in a MySQL "customer" database.

I am looking for hints, tips, tricks, or just outright PHP code to 1.) extract user-entered "junk" from the form, and 2.) match a record in the MySQL database against user-entered form data.

I am in the beginning/concept stage, so any pointers on how to tackle this problem will be greatly appreciated!

Thanks all,

gkelley091565

[_RL_Ian]

You could create a table of valid zipcodes. Here is an SQL file with all the US zipcodes. Then compare entered data to the master zipcode table to see if what they entered matches.

I Got this from the ajax zipcode tutorial

[gkelley091565]

Ian,

Thanks for your response and SQL to generate US zipcodes, but I should have made myself more clear.

I am capturing city, state, and zip code in separate fields/variables. And querying against those are not a problem.....

I am looking for efficient ways to query the "street address" portion of the postal address. Like, "123 Main St.", "123 Main Street", "123 Main", "2334 W. 53rd St.", "2334 West 53 Street", etc. etc.

I am thinking of FULLTEXT indexing the "address" column then writing PHP code to perform Natural Language search against it like -- SELECT * FROM customers WHERE MATCH(address) AGAINST('$user_entered_address');

Am I heading in the right direction here? No of a better way?

Again, sorry for not making myself clear in my initial post! :-(

Gene Kelley
[e-mail]

[_RL_Ian]

gkelley091565 wrote:

Ian,

Thanks for your response and SQL to generate US zipcodes, but I should have made myself more clear.

I am capturing city, state, and zip code in separate fields/variables. And querying against those are not a problem.....

I am looking for efficient ways to query the "street address" portion of the postal address. Like, "123 Main St.", "123 Main Street", "123 Main", "2334 W. 53rd St.", "2334 West 53 Street", etc. etc.

I am thinking of FULLTEXT indexing the "address" column then writing PHP code to perform Natural Language search against it like -- SELECT * FROM customers WHERE MATCH(address) AGAINST('$user_entered_address');

Am I heading in the right direction here? No of a better way?

Again, sorry for not making myself clear in my initial post! :-(

Gene Kelley
[e-mail]

Are you just trying to validate the format (i.e. that it is like [123] [streetname] [road, ave, etc...] or are you trying to see if it's actually a REAL address?

[gkelley091565]

Ian,

I will be validating it against records in an existing "customers" table to one of 5 different criteria for the users to enter in to verify that they are who they say they are.

Thank you,

Gene

[_RL_Ian]

gkelley091565 wrote:

Ian,

I will be validating it against records in an existing "customers" table to one of 5 different criteria for the users to enter in to verify that they are who they say they are.

Thank you,

Gene

OK, we're on the same page now.

I don't see a problem with indexing the address field, if that's the criteria you need to validate against. The only problem would be if two customers had the same address. Or if one customer used to live in a location where the next customer currently lives.

One thing you could do is have them enter their e-mail address or username along with the address information. That way when you run your query it narrows down their information based on what addtional input.