komt ie :
Een selectie data voor de landen :
DROP TABLE IF EXISTS `iso3166_1`;
CREATE TABLE `iso3166_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`numeric` int(11) DEFAULT NULL,
`Alpha2Code` varchar(4) DEFAULT NULL,
`Alpha3Code` varchar(4) DEFAULT NULL,
`CountryName` varchar(128) DEFAULT NULL,
`TopLevelDomain` varchar(5) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `numeric` (`numeric`),
KEY `Alpha2Code` (`Alpha2Code`),
KEY `CountryName` (`CountryName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `iso3166_1` (`id`, `numeric`, `Alpha2Code`, `Alpha3Code`, `CountryName`, `TopLevelDomain`) VALUES
(1, 20, 'ad', 'and', 'Andorra', '.ad'),
(2, 4, 'af', 'afg', 'Afghanistan', '.af'),
(3, 28, 'ag', 'atg', 'Antigua and Barbuda', '.ag'),
(4, 660, 'ai', 'aia', 'Anguilla', '.ai'),
(5, 8, 'al', 'alb', 'Albania', '.al');
en de regios :
DROP TABLE IF EXISTS `iso3166_2`;
CREATE TABLE `iso3166_2` (
`country_code` char(2) DEFAULT NULL,
`subdivision_name` varchar(128) DEFAULT NULL,
`subdivision_code` varchar(10) DEFAULT NULL,
KEY `idx_country_code` (`country_code`),
KEY `idx_region_name` (`subdivision_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `iso3166_2` (`country_code`, `subdivision_name`, `subdivision_code`) VALUES
('AD', 'Andorra la Vella', 'AD-07'),
('AD', 'Canillo', 'AD-02'),
('AD', 'Encamp', 'AD-03'),
('AD', 'Escaldes-Engordany', 'AD-08'),
('AD', 'La Massana', 'AD-04'),
('AD', 'Ordino', 'AD-05'),
('AD', 'Sant Julia de Loria', 'AD-06'),
('AE', '\'Ajman', 'AE-AJ'),
('AE', 'Abu Zaby', 'AE-AZ'),
('AE', 'Al Fujayrah', 'AE-FU'),
('AE', 'Ash Shariqah', 'AE-SH'),
('AE', 'Dubayy', 'AE-DU'),
('AE', 'Ra\'s al Khaymah', 'AE-RK'),
('AE', 'Umm al Qaywayn', 'AE-UQ'),
('AF', 'Baghlan', 'AF-BGL'),
('AF', 'Balkh', 'AF-BAL'),
('AF', 'Bamyan', 'AF-BAM'),
('AF', 'Faryab', 'AF-FYB'),
('AF', 'Helmand', 'AF-HEL'),
('AF', 'Herat', 'AF-HER'),
('AF', 'Kabul', 'AF-KAB'),
('AF', 'Kandahar', 'AF-KAN'),
('AF', 'Khost', 'AF-KHO'),
('AF', 'Kunduz', 'AF-KDZ'),
('AF', 'Logar', 'AF-LOG'),
('AF', 'Nangarhar', 'AF-NAN'),
('AF', 'Nimroz', 'AF-NIM'),
('AF', 'Paktika', 'AF-PKA'),
('AF', 'Paktiya', 'AF-PIA'),
('AF', 'Parwan', 'AF-PAR'),
('AF', 'Takhar', 'AF-TAK'),
('AF', 'Uruzgan', 'AF-URU'),
('AG', 'Redonda', 'AG-11'),
('AG', 'Saint George', 'AG-03'),
('AG', 'Saint John', 'AG-04'),
('AG', 'Saint Mary', 'AG-05'),
('AG', 'Saint Paul', 'AG-06'),
('AG', 'Saint Peter', 'AG-07'),
('AG', 'Saint Philip', 'AG-08'),
('AI', 'Anguilla', '-'),
('AL', 'Berat', 'AL-01'),
('AL', 'Diber', 'AL-09'),
('AL', 'Durres', 'AL-02'),
('AL', 'Elbasan', 'AL-03'),
('AL', 'Fier', 'AL-04'),
('AL', 'Gjirokaster', 'AL-05'),
('AL', 'Korce', 'AL-06'),
('AL', 'Kukes', 'AL-07'),
('AL', 'Lezhe', 'AL-08'),
('AL', 'Shkoder', 'AL-10'),
('AL', 'Tirane', 'AL-11'),
('AL', 'Vlore', 'AL-12');
Een procedure die je in php aanroept :
DELIMITER ;;
DROP PROCEDURE IF EXISTS `iso3166`;;
CREATE PROCEDURE `iso3166`(IN `van` char(3), IN `tot` char(3))
SELECT
UPPER(Alpha2Code) AS Code,
CountryName AS Name,
subdivision_code AS SubCode,
subdivision_name AS Subdivision
FROM
iso3166_1
LEFT JOIN
iso3166_2
ON
Alpha2Code = country_code
WHERE
Alpha2Code >= van AND Alpha2Code <= tot
ORDER BY
Alpha2Code, Name, Subdivision;;
DELIMITER ;
En de webpagina :
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>ISO3166 List</title>
<meta name="viewport" content="user-scalable=1, width=device-width, initial-scale=1.0, maximum-scale=2.0, minimum-scale=1.0" />
<meta http-equiv="Content-type" content="text/html; charset=utf-8" />
</head>
<body>
<?php
//db details
$dbHost = 'localhost';
$dbUsername = 'user';
$dbPassword = 'password';
$dbName = 'wereld';
//Connect and select the database
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
$db->set_charset("utf8mb4");
$db->query("SET NAMES utf8mb4 COLLATE utf8mb4_general_ci");
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}
?>
<?php
$Alpha2CodeFrom = strtoupper( filter_var( $_GET['van'], FILTER_SANITIZE_STRING ) );
$Alpha2CodeTo = strtoupper( filter_var( $_GET['tot'], FILTER_SANITIZE_STRING ) );
if ( $Alpha2CodeFrom > $Alpha2CodeTo )
{
$Alpha2CodeTemp = $Alpha2CodeFrom;
$Alpha2CodeFrom = $Alpha2CodeTo;
$Alpha2CodeTo = $Alpha2CodeTemp;
}
$query = "CALL iso3166( '" . $Alpha2CodeFrom . "', '" . $Alpha2CodeTo . "')";
if ( $result = $db->query($query) )
{
if ( $result->num_rows > 0 )
{
$check_previous = "";
while( $row = $result->fetch_object() )
{
$shortname = $row->Name;
if ( $shortname <> $check_previous )
{
echo '<b>' . $shortname . '</b><br />';
$check_previous = $shortname;
}
// Put Districts (with Parent Codes) in County
echo ' ' . $row->Subdivision . ' - <i>District</i><br />';
if ( !empty( $row->SubCode ) )
{
$generated = $row->SubCode;
echo ' Generated : ' . $generated . '<br />';
}
}
}
}
?>
</div>
</body>
</html>
De pagina roep je aan als volgt :
pagina.php?van=af&tot=af
[size=xsmall]
Toevoeging op 08/04/2019 11:42:45:[/size]
Werkend voorbeeld :
http://adoptive.2kool4u.net/dynamic_select/iso3166.php?van=af&tot=am
