import_lijst_van_nederlandse_gemeenten_per_provincie
20 november 2010 
<!DOCTYPE html>
<html lang="nl">
	<head>
		<meta charset="utf-8" />
	</head>
	<h1>Import</h1>
	<?PHP
	$mysql_server   = isset($_POST['mysql_server']) ? $_POST['mysql_server'] : "localhost";
	$mysql_username = isset($_POST['mysql_username']) ? $_POST['mysql_username'] : "root";
	$mysql_password = isset($_POST['mysql_password']) ? $_POST['mysql_password'] : NULL;
	$mysql_database = isset($_POST['mysql_database']) ? $_POST['mysql_database'] : NULL;
	$mysql_table    = isset($_POST['mysql_table']) ? $_POST['mysql_table'] : "gemeenten";

	$ch = curl_init();

	curl_setopt($ch, CURLOPT_URL,"http://nl.wikipedia.org/wiki/Nederlandse_gemeenten_per_provincie");
	curl_setopt ($ch, CURLOPT_USERAGENT, "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0)");
	curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);

	$html = curl_exec($ch);

	curl_close($ch);

	$dom = new DOMDocument();

	@$dom->loadHTML($html);

	$xpath = new DOMXPath($dom);

	$hrefs = $xpath->evaluate("/html/body//a");

	$array_gemeenten = array();

	$array_provincies = array(
		1 => "Drenthe",
		2 => "Flevoland",
		3 => "Friesland",
		4 => "Gelderland",
		5 => "Groningen",
		6 => "Limburg",
		7 => "Noord-Brabant",
		8 => "Noord-Holland",
		9 => "Overijssel",
		10 => "Utrecht",
		11 => "Zeeland",
		12 => "Zuid-Holland"
	);

	for ( $i = 0; $i < $hrefs->length; $i++ )
	{
		$href = $hrefs->item($i);

		if ( substr($href->getAttribute('href'), 0, 6) == "/wiki/" )
			$array_gemeenten[$i] = $href->nodeValue;
	}

	reset($array_gemeenten);

	$index_start = key($array_gemeenten);

	end($array_gemeenten);

	$index_eind = key($array_gemeenten);

	if ( isset($_POST['import']) )
	{
		mysql_connect($mysql_server, $mysql_username, $mysql_password) or die(mysql_error());
		mysql_select_db($mysql_database) or die(mysql_error());
		mysql_query("SET NAMES 'utf8'");

		echo '<ul>';

		$provincie_id = null;
		$previous_provincie_id = null;

		foreach ( $array_gemeenten as $key => $value )
		{
			if ( $key >= $_POST['index_start'] && $key <= $_POST['index_eind'])
			{
				if ( in_array($value, $array_provincies) )
				{
					$previous_provincie_id = $provincie_id;

					switch ($value)
					{
						case "Drenthe":
							$provincie_id = 1;
							break;
						case "Flevoland":
							$provincie_id = 2;
							break;
						case "Friesland":
							$provincie_id = 3;
							break;
						case "Gelderland":
							$provincie_id = 4;
							break;
						case "Groningen":
							$provincie_id = 5;
							break;
						case "Limburg":
							$provincie_id = 6;
							break;
						case "Noord-Brabant":
							$provincie_id = 7;
							break;
						case "Noord-Holland":
							$provincie_id = 8;
							break;
						case "Overijssel":
							$provincie_id = 9;
							break;
						case "Utrecht":
							$provincie_id = 10;
							break;
						case "Zeeland":
							$provincie_id = 11;
							break;
						case "Zuid-Holland":
							$provincie_id = 12;
							break;
					}
				}

				if ( ! in_array($value, $array_provincies) OR $previous_provincie_id == $provincie_id )
				{
					$gemeente = $value;

					$sql = sprintf("SELECT * FROM %s WHERE description = '%s' AND provincie_id = %d", mysql_real_escape_string($mysql_table), mysql_real_escape_string($gemeente), $provincie_id);

					$result = mysql_query($sql) or die(mysql_error());

					if ( mysql_num_rows($result) == 0 )
					{
						$sql = sprintf("INSERT INTO %s (description, provincie_id) VALUES ('%s',%d)", mysql_real_escape_string($mysql_table), mysql_real_escape_string($gemeente), $provincie_id);
						mysql_query($sql) or die(mysql_error());
						echo '<li style="color: green">'.$gemeente.'</li>';
					}

					else
						echo '<li style="color: red">'.$gemeente.'</li>';
				}

				else
					echo '<li>' . '<strong>' . $value . '</strong>' . '</li>';
			}
		}

		echo '</ul>';

		mysql_close();
	}
	?>
	<form method="post" action="import_lijst_van_nederlandse_gemeenten_per_provincie.php">
		<fieldset>
			<legend>MySQL</legend>
			<p>
				<label for="mysql_server">Server</label>
				<input type="text" name="$_POST['mysql_server']" id="mysql_server" value="<?PHP echo $mysql_server; ?>" />
			</p>
			<p>
				<label for="mysql_username">Username</label>
				<input type="text" name="mysql_username" id="mysql_username" value="<?PHP echo $mysql_username; ?>" />
			<p>
			</p>
			<p>
				<label for="mysql_password">Password</label>
				<input type="password" name="mysql_password" id="mysql_password" value="<?PHP echo $mysql_password; ?>" />
			</p>
			<p>
				<label for="mysql_database">Database</label>
				<input type="text" name="mysql_database" id="mysql_database" value="<?PHP echo $mysql_database; ?>" />
			</p>
			<p>
				<label for="mysql_table">Tabel</label>
				<input type="text" name="mysql_table" id="mysql_table" value="<?PHP echo $mysql_table; ?>" />
			</p>
		</fieldset>
		<br />
		<fieldset>
			<legend>Index</legend>
			<p>
				<label for="index_start">Start</label>
				<input type="text" name="index_start" id="index_start" value="<?PHP echo $index_start; ?>" />
			</p>
			<p>
				<label for="index_eind">Eind</label>
				<input type="text" name="index_eind" id="index_eind" value="<?PHP echo $index_eind; ?>" />
			</p>
		</fieldset>
		<br />
		<p><input type="submit" name="import" value="Import" /></p>
	</form>
	<h1>Lijst van Nederlandse gemeenten</h1>
	<ul>
	<?PHP
	foreach ( $array_gemeenten as $key => $value )
		if (in_array($value, $array_provincies))
			echo '<li>' . $key . ' ' . '<strong>' . $value . '</strong>' . '</li>';
		else
			echo '<li>' . $key . ' ' . $value . '</li>';
	?>
	</ul>
</html>