Jump to content
Sign in to follow this  
PopTheTop

Customer Mismatched State & Zipcode Report v2.0

Recommended Posts

Customer Mismatched State & Zipcode Report v2.0

 

This will look through the database and report the names / id of customers whose zipcode does not match up with the State in their Address Book. It will search for both so either the zipcode is wrong or the State is wrong. If there are no incorrect entries found, nothing will be reported.

 

This way, you can notify the customer and let them know that they need to update their account info. It may be the shipping address or billing address

 

This is compatible with the Admin DHTML Menus contribution HERE


L8r,

PopTheTop

 

Published osC Contributions:

- eCheck Payment Module v3.1

- Reviews in Product Display v2.0

- Fancier Invoice & Packingslip v6.1

- Admin Notes / Customer Notes v2.2

- Customer Zip & State Validation v2.2

- Search Box with Dropdown Category Menu v1.0

 

Pop your camper's top today!

It's a popup thing...

You wouldn't understand

Share this post


Link to post
Share on other sites

Share this post


Link to post
Share on other sites

On mine, I added Puerto Rico (between PA and RI). See below. I also put mine under tools instead of reports, and changed the name to Zip Code Validator (mostly because it is shorter and displays on a single line).

 

"PA" => array ("1500019699"),

"PR" => array ("0060000999"),

"RI" => array ("0280002999", "0637906379"),

 

Very nice integration. Now if someone could do the coding so the admin can just click on the name of the customer to go to the account detail and send an email or edit, that would be even better.

Share this post


Link to post
Share on other sites

I also changed the page heading to:

 

<td class="pageHeading">Address Book Entries With Mismatched State and Zip</td>

 

I like that look (one line instead of two) better. Just my personal preference.

Share this post


Link to post
Share on other sites

I see what you guys mean, I changed it to display Zip Code Validator and added the version number as well.

 

I have aslo added Puerto Rico, thanks [/b]baddog[/b]!

 

Also, thanks to jnew for making the newest version so that you no longer have to edit your database info among a few other updates. Below is the latest updates to date...

 

##########################################################

HISTORY

##########################################################

 

Nov 16, 2004 - Dave (baddog)

Added Puerto Rico

Shortened the name so that it gets displayed on 1 line now

Added the version number at the top

 

Nov 16, 2004 - John P. New (jnew)

User no longer has to modify database info in zipcode_validate.php.

If all states and zip codes match in the customer's database, report now returns a message stating such.

If no US addresses in the customer's database are found, report now returns a message stating such.

Hey, thanks DooD!

 

Nov 15, 2004 - Scott Moore (PopTheTop)

Updated for osC v2.2 MS2

Full re-write of original contribution to integrate with osC Admin section.

Added link in Admin / Reports to run this contribution.

Layout of report is now much nicer.

Made it compatible with the Admin DHTML Menus contribution.

 

Aug 31, 2004 - Doug Edmunds

Original contribution and code and idea by: Doug Edmunds.

http://www.oscommerce.com/community/contributions,2404

What a great start!


L8r,

PopTheTop

 

Published osC Contributions:

- eCheck Payment Module v3.1

- Reviews in Product Display v2.0

- Fancier Invoice & Packingslip v6.1

- Admin Notes / Customer Notes v2.2

- Customer Zip & State Validation v2.2

- Search Box with Dropdown Category Menu v1.0

 

Pop your camper's top today!

It's a popup thing...

You wouldn't understand

Share this post


Link to post
Share on other sites

I'm getting this error when I click on the "Customer Zip/State Mismatch" option, do you guys have any idea why? I can see the grid that say that no mismatch had been found, but I know is not working because I had a bad zipcode setup for testing that should show up.

 

I beleive this error is not letting the script finish validation.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/myborinq/public_html/ecommerce/os/catalog/admin/zipcode_validate.php on line 176

Thanks in advance

 

I see what you guys mean, I changed it to display Zip Code Validator and added the version number as well.

 

I have aslo added Puerto Rico, thanks [/b]baddog[/b]!

 

Also, thanks to jnew for making the newest version so that you no longer have to edit your database info among a few other updates. Below is the latest updates to date...

 

##########################################################

HISTORY

##########################################################

 

Nov 16, 2004 - Dave (baddog)

Added Puerto Rico

Shortened the name so that it gets displayed on 1 line now

Added the version number at the top

 

Nov 16, 2004 - John P. New (jnew)

User no longer has to modify database info in zipcode_validate.php.

If all states and zip codes match in the customer's database, report now returns a message stating such.

If no US addresses in the customer's database are found, report now returns a message stating such.

Hey, thanks DooD!

 

Nov 15, 2004 - Scott Moore (PopTheTop)

Updated for osC v2.2 MS2

Full re-write of original contribution to integrate with osC Admin section.

Added link in Admin / Reports to run this contribution.

Layout of report is now much nicer.

Made it compatible with the Admin DHTML Menus contribution.

 

Aug 31, 2004 - Doug Edmunds

Original contribution and code and idea by: Doug Edmunds.

http://www.oscommerce.com/community/contributions,2404

What a great start!

Share this post


Link to post
Share on other sites

You got me on that one. I do not know enough about PHP to help, sorry. No one else has had that problem. Are you very certain you did EVERYTHING in the instructions exactly? And did you make any changes?


L8r,

PopTheTop

 

Published osC Contributions:

- eCheck Payment Module v3.1

- Reviews in Product Display v2.0

- Fancier Invoice & Packingslip v6.1

- Admin Notes / Customer Notes v2.2

- Customer Zip & State Validation v2.2

- Search Box with Dropdown Category Menu v1.0

 

Pop your camper's top today!

It's a popup thing...

You wouldn't understand

Share this post


Link to post
Share on other sites

Any crazy coders out there want a challenge????

 

Take this contrib and turn it into a mod that will automatically populate the customer's 'state' field when the customer inputs their zipcode.

 

This will save the customer a step and prevent typos and mismatches altogether.

 

Any takers?

:-j.

Share this post


Link to post
Share on other sites

I'm almost 100% sure I follow the instructions step by step. But I will try again.

 

Thanks

 

You got me on that one. I do not know enough about PHP to help, sorry. No one else has had that problem. Are you very certain you did EVERYTHING in the instructions exactly? And did you make any changes?

Share this post


Link to post
Share on other sites

Hey everyone. I followed the directions. I uploaded zipcode_validate.php to catalog/*admin*/zipcode_validate.php

 

I then added the code in reports.php

 

I get the following error:

Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /homepages/15/***0/htdocs/catalog/***/zipcode_validate.php on line 185

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /homepages/15/***/htdocs/catalog/***/zipcode_validate.php on line 186

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /homepages/15/***/htdocs/catalog/***/zipcode_validate.php on line 190

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /homepages/15/***/htdocs/catalog/***/zipcode_validate.php on line 193

 

Is there another way to call the database inorder to achieve the results?

 

I have the following:

Server OS: Linux 2.4.28-grsec-20050113a

HTTP Server: Apache/1.3.29 (Unix)

PHP Version: 4.3.10 (Zend: 1.3.0)

 

 

My guess is that it doesnt like the following: [Line 185]

	$mysql_access = mysql_connect("localhost", DB_SERVER_USERNAME, DB_SERVER_PASSWORD);

 

Really would like to get this going. Thanks guys.

Share this post


Link to post
Share on other sites
Hey everyone.  I followed the directions.  I uploaded zipcode_validate.php to catalog/*admin*/zipcode_validate.php

 

I then added the code in reports.php

 

I get the following error:

Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /homepages/15/***0/htdocs/catalog/***/zipcode_validate.php on line 185

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /homepages/15/***/htdocs/catalog/***/zipcode_validate.php on line 186

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /homepages/15/***/htdocs/catalog/***/zipcode_validate.php on line 190

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /homepages/15/***/htdocs/catalog/***/zipcode_validate.php on line 193

 

Is there another way to call the database inorder to achieve the results?

 

I have the following:

Server OS: Linux 2.4.28-grsec-20050113a

HTTP Server: Apache/1.3.29 (Unix)

PHP Version: 4.3.10 (Zend: 1.3.0)

My guess is that it doesnt like the following: [Line 185]

	$mysql_access = mysql_connect("localhost", DB_SERVER_USERNAME, DB_SERVER_PASSWORD);

 

Really would like to get this going.  Thanks guys.

Have you looked at this?

Share this post


Link to post
Share on other sites

This contribution tested locally works. On my local server I have:

Apache 1.3.27 - PHP 4.3.3 - MySQL 4.0.15 - PhpMyAdmin 2.5.3

 

However my webhost is using:

Server OS: Linux 2.4.28-grsec-20050113a

HTTP Server: Apache/1.3.29 (Unix)

PHP Version: 4.3.10 (Zend: 1.3.0)

 

It however does NOT work on my webhost / site setup. And I do not know how to work around it.

Share this post


Link to post
Share on other sites

I have installed your great contribution and it works great for customers who have an account.

If customer using PWA places an order, it will newer show mismatch. Please help me on this.

Thanks.

Share this post


Link to post
Share on other sites

This is a great contribution! However, it's amzing how many people can't fill in their account information correctly.

 

I would really love to see this modified so it checks for a mismatch at the account creation level and returns the customer to the account creation screen with an error message directing them to fix their oversight.

Share this post


Link to post
Share on other sites

Okay, I have tried to install this now five times to no avail. The link doesn't show and no access to file.

 

I uploaded zipcode.validate.php to ./catalog/admin/

I made sure the ownership and permissions were correct and the same as the other report files.

I adjusted the code in ./catalog/admin/includes/boxes/reports.php with maintaining the two // at the beginning of both lines.

 

The link does not show up and trying to access zipcode_validate.php directly responds with an error "404 file not found".

 

What am I doing wrong? I have complete control over my server.

Please help. Thank you.

Bruce

 

 

Server Host: xxxxxxxxxx.com Database Host: localhost (127.0.0.1)

Server OS: FreeBSD 4.11-RELEASE Database: MySQL 5.0.16

Server Date: 02/12/2006 18:46:22 Datebase Date: 02/12/2006 18:46:22

Server Up Time: 6:46PM up 80 days, 1:22, 0 users, load averages: 1.21, 1.25, 1.21

HTTP Server: Apache/2.0.55 (FreeBSD) PHP/5.1.1 mod_ssl/2.0.55 OpenSSL/0.9.7d

PHP Version: 5.1.1 (Zend: 2.1.0)

Share this post


Link to post
Share on other sites

Hi! Wondering if you ever figured this out. I am dealing with the same thing.

Thanx.

Bruce

==

 

This contribution tested locally works. On my local server I have:

Apache 1.3.27 - PHP 4.3.3 - MySQL 4.0.15 - PhpMyAdmin 2.5.3

 

However my webhost is using:

Server OS: Linux 2.4.28-grsec-20050113a

HTTP Server: Apache/1.3.29 (Unix)

PHP Version: 4.3.10 (Zend: 1.3.0)

 

It however does NOT work on my webhost / site setup. And I do not know how to work around it.

Share this post


Link to post
Share on other sites

Can someone give a brief suggestion as to how to correct this contribution to work with APO and FPO Military addresses? (state codes are AE, AP, AA, etc)

 

I think I can pull out the ranges of zip codes from the Post office website.

 

I'm willing to post the corrected file when I'm done.

 

Kent

Share this post


Link to post
Share on other sites
Can someone give a brief suggestion as to how to correct this contribution to work with APO and FPO Military addresses? (state codes are AE, AP, AA, etc)

 

I think I can pull out the ranges of zip codes from the Post office website.

 

I'm willing to post the corrected file when I'm done.

 

Kent

 

 

OK, I figured out how to add these states, and made sure that all the USPS states, territories, etc., are all in the data with their correct zipcodes. I also removed a lot of errors that had crept into the ranges -- it almost seemed like someone had added zipcode ranges just because they had an address with that zipcode, and didn't bother to check to see if the zipcode was valid! I checked the data against the zipcode lookup on usps.com.

 

Anyway, I'm uploading the new file as version 2.2.1 (I didn't go to 2.3 because all I did was update the data).

 

 

Future Suggestions

Here are my suggestions for future development, should anyone be interested:

  • Move zip code ranges to either the zones table or a new table in the database. This way the zones admin page can be used to keep the ranges up-to-date.
  • Open up this to non-US postal codes. I'll bet most countries postal code systems work similarly to the US system -- i.e., with ranges that can be entered for a state or province. If the code is written so that zones without a range are ignored (or so a warning is given when the zone has no range), then it would be possible to validate foreign codes also. The information needed for these ranges is available on the web at the websites of the postal services of the country, and at sites like mapquest (although it can be tedious to compile this information).
  • Look at validating addresses against USPS system. The USPS already has a system in place for validating not just that the zip code is in the right place, but that the zip code is correct for the address given. USPS charges for some information, but I believe a basic level of information is available for free. It wouldn't suprise me if other countries also supply this information.

Share this post


Link to post
Share on other sites

I installed Customer Mismatched State & Zipcode Report v2.2 on my OSC v2.2 but in the admin control panel it says no mismatched customer addresses found.

 

I did test with a false zipcode&state match in the customer shipping address, for example: zipcode->12345, state->CO. But it says in admin reports, there is no mismatched addresses. I want to know in the file admin/zipcode_validate.php file, find the line

"CO" => array ("8000081699"),

Does this cover all the zipcodes in Colorado? or how do you define the range of zipcodes in one state?

 

Thanks and regards

Share this post


Link to post
Share on other sites

Ok never mind, I found the fix. I changed my country ID from 223 to 1, so did I change the SQL in zipcode_validate.php. Now the report is correct. Nice work :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×