PEI Civic Address Lookup with AJAX

From rukapedia
Jump to: navigation, search

What the Script Does

This is a PHP script that allows easy lookup of Prince Edward Island civic address data in a MySQL database. It uses AJAX to do "realtime" searching of the data while an address is being typed in, and is intended to be a more intuitive, easier to use replace for systems that require multi-field civic address entry (street number, street name, community name, etc.).

Theory of Operation

A Prince Edward Island civic address consists of four parts: street number, street name, community name and county. For example:

95 ROCHFORD ST, CHARLOTTETOWN, QUEENS COUNTY

In theory there are no duplicate addresses: every combination of those four values is unique. Because Prince Edward Island only has 65,000-odd civic addresses, a query that combines a street number and the first letter of the street name produces few enough street addresses that it's reasonable to display these in a drop-down select list.

For example, the combination of 3 for street number and A for start of a street name produces only 20 results across the province:

mysql> select street_no,street_nm from civicaddress where street_no = '3' and street_nm like 'A%';
+-----------+-------------------+
| street_no | street_nm         |
+-----------+-------------------+
|         3 | AYLWARD DR        |
|         3 | ARCONA DR         |
|         3 | ABBEY DR          |
|         3 | ADMIRAL ST        |
|         3 | ALEXANDER DR      |
|         3 | ASHBURN CR        |
|         3 | ASH DR            |
|         3 | ACORN DR          |
|         3 | AMBER AV          |
|         3 | ARBOUR LN         |
|         3 | ALCAN DR          |
|         3 | ANDREW LN         |
|         3 | ANNIVERSARY AV    |
|         3 | ABELS CAPE LN     |
|         3 | ANNEAR DR         |
|         3 | ANTOINETTE DR     |
|         3 | ANDERSON RD       |
|         3 | ARTS RD           |
|         3 | ALBION ST         |
|         3 | ARTHUR HOLLAND DR |
+-----------+-------------------+
20 rows in set (0.00 sec)

For higher street numbers, there is often only one street name returned for a given street name start letter:

mysql> select street_no,street_nm from civicaddress where street_no = '1360' and street_nm like 'K%';
+-----------+-----------------------+
| street_no | street_nm             |
+-----------+-----------------------+
|      1360 | KINGSTON RD - RTE 235 |
+-----------+-----------------------+
1 row in set (0.00 sec)

As a result, it's possible to easily look up a civic address without identifying the community name or county. This script leverages this fact to enable an AJAX-driven "real time" lookup of PEI civic address data, displaying possible addresses matches as a user types in an address.

System Requirements

Download or Browse the Source Code

The source code is kept in a Subversion repository:

Install and Run

  1. Ensure that you've created a PEI civic address data table in a web-accessible MySQL server.
  2. Edit the file settings/PEICivicAddressData.inc, adding your MySQL username and password, and selecting the database name and table name for the civic address data.
  3. Put the PEICivicAddressLookup.php, PEICivicAddressLookupAJAX.php in a web-accessible directory, and the PEICivicAddressData.inc file under a settings sub-directory.
  4. Grab the script.aculo.us JavaScript files and put them in a web-accessible directory; set the location of this directory in the PEICivicAddressLookup.php script by setting the $JAVASCRIPT variable.

Testing

You should now be able to point a web browser at the PEICivicAddressLookup.php on your server and, if everything is working as planned, as you type in a civic address you should see matching addresses displayed in a drop-down select list. Note that the AJAX goodness only starts to work after you've typed at least the street number and one character of the street name.

Screen Shot of Lookup in Action

Debugging

If the script doesn't work as expected, you can change this line in PEICivicAddressLookupAJAX.php:

$lookfor = $_POST['civicaddress'];

to

$lookfor = $_GET['civicaddress'];

You can then call the script directly, adding a civicaddress argument, like:

.../PEICivicAddressLookupAJAX.php?civicaddress=95+ROCHFORD

This will allow you to see if the "look up the civic address" code is working properly.