PHP/JavaScript – Ort anhand Postleitzahl automatisch ausfüllen

Wer kennt das nicht: Bitte geben Sie Ihre Adresse ein.
Und immer wieder muss man zusätzlich zu der PLZ auch noch den Namen der Stadt eingeben.
Eigentlich unnötig, da die Information nur hinterlegt werden müsste.
Die Information gibt es kostenlos auf OpenGeoDB ( http://opengeodb.giswiki.org/wiki/OpenGeoDB_Downloads )

Und so geht es:

1. Datenbank Tabelle anlegen:

Mit folgendem kleinem PHP script kann man die Daten von OpenGeoDB in eine lokale MySQL Tabelle importieren.

import_plz.php

 
< ?php
/*
This small PHP script will create a zip/plz Table with the data from OpenGeoDB

Michael Leinich ( leinich.net ) 2011 May
Version 1.0.0

Instruction :
Go to http://opengeodb.giswiki.org/wiki/OpenGeoDB_Downloads
and download PLZ.tab
Copy PLZ.tab into the same folder as import_plz.php
Adjust MySQL 
open 
*/

// MySQL Connection Values
define("DB_HOST", "localhost");
define("DB_USER", "username");
define("DB_PASS", "passwort");
define("DB_NAME", "databasename");
define("DB_TABLE", "de_plz");


// where to find PLZ.tab from OpenGeoDB
$plz_tab_file = "./PLZ.tab";

// Connecting
$link = mysql_connect( DB_HOST, DB_USER, DB_PASS );
if (!$link) { die('DB Connect failed: ' . mysql_error()); }

// Select DB
$db_selected = mysql_select_db( DB_NAME, $link );
if (!$db_selected) {
	// Try to create DB
	echo '

DB Create with name ' . DB_NAME . ''; $sql = "CREATE DATABASE " . DB_NAME; $result = mysql_query( $sql ); echo 'MESSAGE: ' . mysql_error() . '\n'; $db_selected = mysql_select_db( DB_NAME, $link ); if (!$db_selected) { die( 'DB Create failed: ' . mysql_error() . '\n' ); } } // Creating Table if Table not exists $sql = "CREATE TABLE IF NOT EXISTS " . DB_NAME . "." . DB_TABLE . " ( loc_id INT( 5 ) NOT NULL , plz CHAR( 5 ) NOT NULL , lon FLOAT( 15, 13 ) NOT NULL , lat FLOAT( 15, 13 ) NOT NULL , ort VARCHAR( 30 ) NOT NULL , PRIMARY KEY ( plz ) , UNIQUE ( loc_id ) ) ENGINE = MYISAM COMMENT = 'German ZIP PLZ list'"; $result = mysql_query($sql); if ($result != 1) { die( 'Table Create failed: ' . mysql_error() . '\n' ); } // reading file PLZ.tab line by line echo "Reading file " . $plz_tab_file . "\n"; if ( $filehandle = fopen ( $plz_tab_file , "r" ) ) { $count = 0; while (!feof( $filehandle )) { $buffer = fgets( $filehandle ); // Split String on indicator TAB $values = explode( "\t" , $buffer ); // five values needed if (count( $values ) == 5) { $result = mysql_query( "INSERT INTO " . DB_NAME . "." . DB_TABLE . "(loc_id, plz, lon, lat, ort) VALUES ( '".$values[0]."', '".$values[1]."', '".$values[2]."', '".$values[3]."', '".$values[4]."')"); if ($result != 1) { die( 'Failed on ' . $count . 'th insert query: ' . mysql_error() . '\n' ); } $count++; } } fclose ( $filehandle ); } else { die( 'Failed to open ' . $plz_tab_file . '\n' ); } echo "Imported " . $count . " lines"; echo "FINISHED"; ?>

2. Datenabfrage

mit folgendem Script kann man die Daten aus der Datenbank abfragen:

getort.php

 
< ?php
/*
This small PHP script return the ort value of a plz

Michael Leinich ( leinich.net ) 2011 May
Version 1.0.0

Instruction :
1. Adjust MySQL connection values
2. Copy this php script (getort.php) on your webserver
3. Done
*/

// MySQL Connection Values
define("DB_HOST", "localhost");
define("DB_USER", "username");
define("DB_PASS", "passwort");
define("DB_NAME", "databasename");
define("DB_TABLE", "de_plz");

// Default value if 
$returnvalue = "ERROR";
$notvalid = "ungültige PLZ";

if (!isset($_GET['plz'])) { die('ERROR: Missing value'); }
// ToDo: if request outside domain, stop here

$plz = mysql_real_escape_string($_GET['plz']);

// query DB if a 5 char long is passed
if (strlen($plz) == 5){
	$link = mysql_connect( DB_HOST, DB_USER, DB_PASS );
		if (!$link) { die('ERROR: DB Connect failed: ' . mysql_error()); }
	$db_selected = mysql_select_db( DB_NAME, $link );
		if (!$db_selected) { die('ERROR: DB Select failed: ' . mysql_error()); }
	$sql = "SELECT ort FROM " . DB_NAME . "." . DB_TABLE . " WHERE plz = '" . $plz . "'";
	$result = mysql_query($sql);
	if ( mysql_num_rows($result) == 1) {
		$value = mysql_fetch_array($result);
		$returnvalue = $value['ort'];
	} else {
		$returnvalue = $notvalid;
	}
}
echo $returnvalue;
?>

3. Formular

Beim ausfüllen des Formularfeldes plz wird anschließend das Formularfeld ort befüllt

<html><head>	
<title>Sample Form</title>	
<script type="text/javascript">		
function getort(plz) {
			// check if ort is already filled
			if (document.getElementById("ort").value!="") { return false; }
			var xmlhttp = new XMLHttpRequest();
			xmlhttp.open("GET", "getort.php?plz=" + plz.value, true);
			xmlhttp.onreadystatechange=function() {	
			if (xmlhttp.readyState==4) {
					document.getElementById("ort").value=xmlhttp.responseText;
				}
			}
			xmlhttp.send(null);
		}	
</script>
</head>
<body>	
<p>Postleitzahl: <input type="text" maxlength="5" size="5" name="plz" id="plz" onblur="getort(this)"/></p>
<p>Ort: <input type="text" name="ort" id="ort" /></p>
</body>
</html>

Viel Spaß!