user-friendly city and state search logic

[mwpclark]

Hello

I have been writing searchable directories for years, and generally I have used a form that has a dropdown/select field for states, including an option for "all states", and a separate text field for city. The raw data being searched is always broken down into separate city and state fields within the database. I am using php, mysql and oracle.

However I am becoming aware that many people will enter something like "Salem MA" or "Salem, MA" or "Salem Mass" in the city field, as they are accustomed to doing with google, etc. This cannot be standardized, the query depends on what the viewer enters, and people enter what they enter.

When you go to google maps for example, and type in fresno ca, it automatically knows what the viewer wants. Of course google has hundreds of programmers working on this stuff and I am just one guy...

I am thinking about a logical progression as follows:

Let's say the form has $state select box including "all states", and $city text field.

If $state is not specified AND if $city contains a space, grab the following the final space and identify as $end

1) If $end equals 2 letters, check states db to see if $end matches state abbreviation

else

2) If $end is longer than 2 letters, check states db to see if $end LIKE state name

then

3) If there is no match for 1 or 2, search city database to see if $city is LIKE city name. For example, Los Angeles would match.

Let's say there is a typo, Los Angeles CZ, or Los Angeles Calf -- these would fail 1, 2 and 3. Chop off the final $end from $city, and Los Angeles would match.

Am I taking this too far?

Next, search a geo database, look for proximity

I have worked with chained select fields before, where the viewer first selects state then the city select field is populated with a list of cities in the state, but this is not user-friendly -- too many people don't get it.

Any suggestions much appreciated....

Cheers,
Mike

 

[vacunita]

If you really want to improve user friendliness, you could try your hand at an ajax driven textbox/dropdown. As the user types, the dropdown fills in with potential results. This means the user can then click on one of the potential results from the preliminary searches like Google does.

Since the textbox is for city, I'd limit the search to just the cities table. If you already have a state dropdown, you can even limit the search for cities within that state, if your DB is properly normalized.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[spamjim]

Where does the postal/zip code fit into this?

You can also look up a zip code table to verify city and state. Or you can simply ask the user to enter their zip code and your web page can automatically determine the city and state.

If you need postal codes, SQL inserts for a few countries (US, IN, AU, UK, DE...) can be extracted from a Drupal module:

https://drupal.org/project/location

Verifying the relation of what the user submitted as the zip code to what was submitted as city/state is also a good way to trim out bogus entries.

 

[mwpclark]

Interesting suggestions. I am currently working on an ajax city search based on

http://www.w3schools.com/php/php_ajax_livesearch.asp

I am thinking about a "location OR zip" form, then the zip code proximity search could come into play.

Also I have worked up this code, which works in test mode, though the final *else* clauses could possibly be consolidated:

<?php
$input = $_GET['input'];
$instring = trim(preg_replace("/\s+/"," ",$input));
if (preg_match ('/[ ]/i', $instring)) {
	$word_array = explode(" ", $instring);
	$num = count($word_array);
	echo $num;
	echo '<br>';
	$sname = end($word_array);
	echo $sname;
	echo '<br>';
	echo strlen(end($word_array));
	$length = strlen(end($word_array));
	$state_abb = strtoupper($sname);
	if ($length == "2") {
		$state_array = array("AL","AK","AS","AZ","AR","CA","CO","CT","DE","DC","FM","FL","GA","GU","HI","ID","IL","IN","IA","KS","KY","LA","ME","MH","MD","MA","MI","MN","MS","MO","MT","NE","NV","NH","NJ","NM","NY","NC","ND","MP","OH","OK","OR","PW","PA","PR","RI","SC","SD","TN","TX","UT","VT","VI","VA","WA","WV","WI","WY");
		$result = in_array($state_abb, $state_array); 
		echo '<br>';
		if ($result == true){
			mysql_connect("localhost", "xxx", "xxx") or die(mysql_error());
			mysql_select_db("zip") or die(mysql_error());
			$result_st = mysql_query("SELECT distinct state_name from zip_code where state_prefix = '$state_abb'") or die(mysql_error());  
			while($row = mysql_fetch_array($result_st)){
				$state_name=$row['state_name'];
				echo $state_name;
				echo '<br>';
				$city_name = substr($instring, 0, -3);
				echo $city_name;
				echo '<br>';
				echo '<br>Cities:<br>';
				mysql_connect("localhost", "xxx", "xxx") or die(mysql_error());
				mysql_select_db("fed") or die(mysql_error());
				$result_ci = mysql_query("SELECT distinct city, state from ho where state = '$state_abb' and city like '%$city_name%'") or die(mysql_error());  
				$num_results = (mysql_num_rows($result_ci));
				if ($num_results == 0) {
					echo 'No cities found matching '.$city_name.', '.$state_abb;
				}
				else {
					while($row = mysql_fetch_array($result_ci)){
						$city_result = $row['city'];
						$state_result = $row['state'];
						$city_cap = ucwords(strtolower($city_result));
						echo $city_cap.', '.$state_result.'<br>';
					}
				}
			}
		}
		else {
			mysql_connect("localhost", "xxx", "xxx") or die(mysql_error());
			mysql_select_db("fed") or die(mysql_error());
			$result_ci = mysql_query("SELECT distinct city, state from ho where city like '%$instring%'") or die(mysql_error());  
				$num_results = (mysql_num_rows($result_ci));
				if ($num_results == 0) {
					echo 'No cities found matching '.$instring;
				}
				else {
					while($row = mysql_fetch_array($result_ci)){
					$city_result = $row['city'];
					$state_result = $row['state'];
						$city_cap = ucwords(strtolower($city_result));
						echo $city_cap.', '.$state_result.'<br>';
				}
			}
		}
	}
	else {
		mysql_connect("localhost", "xxx", "xxx") or die(mysql_error());
		mysql_select_db("fed") or die(mysql_error());
		$result_ci = mysql_query("SELECT distinct city, state from ho where city like '%$instring%'") or die(mysql_error());  
			$num_results = (mysql_num_rows($result_ci));
			if ($num_results == 0) {
				echo 'No cities found matching '.$instring;
			}
			else {
				while($row = mysql_fetch_array($result_ci)){
				$city_result = $row['city'];
				$state_result = $row['state'];
					$city_cap = ucwords(strtolower($city_result));
					echo $city_cap.', '.$state_result.'<br>';
			}
		}
	}
}
else {
	mysql_connect("localhost", "xxx", "xxx") or die(mysql_error());
	mysql_select_db("fed") or die(mysql_error());
	$result_ci = mysql_query("SELECT distinct city, state from ho where city like '%$instring%'") or die(mysql_error());  
	$num_results = (mysql_num_rows($result_ci));
	if ($num_results == 0) {
		echo 'No cities found matching '.$instring;
	}
	else {
		while($row = mysql_fetch_array($result_ci)){
			$city_result = $row['city'];
			$state_result = $row['state'];
			$city_cap = ucwords(strtolower($city_result));
			echo $city_cap.', '.$state_result.'<br>';
		}
	}
}

?>