Counties / states fix for osCommerce
osCommerce site of one of our UK clients suffered from the end customers not being able to register if they entered / selected certain counties on the Account Registration page.
From the dropdown list of over 50 counties there were 3 “haunted” ones, that the web site simply refused to accept. In the drop down of UK counties the end customer could select any but one of those three. And if the customer selected any of those three counties - the web site would propose him or her to do it again, and again, and again.
After looking into the problem, we first didn’t find any particular reasons for this strange behaviour. All zones were set up correctly in osCommerce database, and the list of countries and counties looked perfect in osCommerce Administration panel. Still the web site stubbornly continued to decline end customers who lived in the 3 specific counties: London, Gloucestershire, and Renfrewshire.
So after a brainstorming session and a couple of coffee cups we came up with an interesting theory: those counties were declined by the account registration script which names were parts of names of some other counties in the list: Londonderry, South Gloucestershire, East Renfrewshire (don’t ask why we have so many counties with funny names here! 
)
The task now was to understand why would osCommerce do it to our good client and how to fix that osCommerce bug.
The answer to the “why” question was pretty simple - for some reason the SQL routine that checks whether a certain zone exists in the database contained a bug that returned more than 1 record when the end customers entered the shorter county name.
The answer to the “how” question was even easier - to fix an issue like our client had with their web site one has to edit the create_account.php file. There, in the line #135 (or where it has this SQL routine: select distinct zone_id from) one has to remove all the % from the SQL command.
So, instead of the following piece of code:
$zone_query = tep_db_query("select distinct zone_id from " . TABLE_ZONES . " where zone_country_id = '" . (int)$country . "' and (zone_name like '" . tep_db_input($state) . “%’ or zone_code like ‘%” . tep_db_input($state) . “%’)”);
the #135 line would look like this:
$zone_query = tep_db_query("select distinct zone_id from " . TABLE_ZONES . " where zone_country_id = '" . (int)$country . "' and (zone_name like '" . tep_db_input($state) . “‘ or zone_code like ‘” . tep_db_input($state) . “‘)“);
P.S. Not only the create_account.php file needs to be fixed, but also all scripts responsible for address management functions, where the end customer has a facility to enter a new address into the database.