Angularjs:从数据库中选择国家，州，城市

Question

我想在我的网站上选择国家、州和城市。我在数据库中有国家，州，城市表。

城市表

CREATE TABLE IF NOT EXISTS `cities` (
  `city_id` int(11) NOT NULL,
  `city_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `state_id` int(11) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:Blocked, 1:Active'
) ENGINE=InnoDB AUTO_INCREMENT=6178 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

国家表

CREATE TABLE IF NOT EXISTS `countries` (
  `country_id` int(11) NOT NULL,
  `country_name` varchar(30) CHARACTER SET utf8 NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:Blocked, 1:Active'
) ENGINE=InnoDB AUTO_INCREMENT=240 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

舞台桌

CREATE TABLE IF NOT EXISTS `states` (
  `state_id` int(11) NOT NULL,
  `state_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `country_id` int(11) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:Blocked, 1:Active'
) ENGINE=InnoDB AUTO_INCREMENT=1652 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

如何将数据库中的数据转换为此数据库。

 $scope.countries = {
        'usa': {
            'San Francisco': ['SOMA', 'Richmond', 'Sunset'],
            'Los Angeles': ['Burbank', 'Hollywood']
        },
        'canada': {
            'People dont live here': ['igloo', 'cave']
        }
    };

或者如果你有更好的方法可以做到这一点。请给我看看。

谢谢！

Answer 1

考虑以下数据集...

DROP TABLE IF EXISTS cities;
CREATE TABLE `cities` (
   `city_id` int(11) NOT NULL,
   `city_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
   `state_id` int(11) NOT NULL,
   `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:Blocked, 1:Active'
) ENGINE=InnoDB AUTO_INCREMENT=6178 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS countries;
CREATE TABLE `countries` (
   `country_id` int(11) NOT NULL,
   `country_name` varchar(30) CHARACTER SET utf8 NOT NULL,
   `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:Blocked, 1:Active'
 ) ENGINE=InnoDB AUTO_INCREMENT=240 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS states;
CREATE TABLE `states` (
   `state_id` int(11) NOT NULL,
   `state_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
   `country_id` int(11) NOT NULL,
   `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:Blocked, 1:Active'
) ENGINE=InnoDB AUTO_INCREMENT=1652 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO cities VALUES
 (1,'San Francisco',1,1),
 (2,'Los Angeles',1,1),
 (3,'Winnipeg',2,1),
 (4,'Toronto',3,1);
INSERT INTO states VALUES
 (1,'California',1,1),
 (2,'Manitoba',2,1),
 (3,'Ontario',2,1);
INSERT INTO countries VALUES
 (1,'USA',1),
 (2,'Canada',1);
SELECT x.country_id
     , x.country_name
     , y.state_id
     , y.state_name
     , z.city_id
     , z.city_name 
  FROM countries x 
  JOIN states y 
    ON y.country_id = x.country_id 
  JOIN cities z 
    ON z.state_id = y.state_id;
+------------+--------------+----------+------------+---------+---------------+
| country_id | country_name | state_id | state_name | city_id | city_name     |
+------------+--------------+----------+------------+---------+---------------+
|          1 | USA          |        1 | California |       1 | San Francisco |
|          1 | USA          |        1 | California |       2 | Los Angeles   |
|          2 | Canada       |        2 | Manitoba   |       3 | Winnipeg      |
|          2 | Canada       |        3 | Ontario    |       4 | Toronto       |
+------------+--------------+----------+------------+---------+---------------+

以下代码将输出所需的结果...

<?php
    require('path/to/connection/stateme.nts');
    $query = "
    SELECT x.country_id
         , x.country_name
         , y.state_id
         , y.state_name
         , z.city_id
         , z.city_name
      FROM countries x
      JOIN states y
        ON y.country_id = x.country_id
      JOIN cities z
        ON z.state_id = y.state_id;
    ";
    $result = mysqli_query($db,$query) or die(mysqli_error($db));
    $my_array = array();
    while($row = mysqli_fetch_assoc($result))
        {
            $my_array[] = $row;
        }
    print_r($my_array);
    $new_array = array();
    foreach($my_array as $row){
    $new_array[$row['country_name']][$row['state_name']][]=$row['city_name'];
    }
    $my_json = json_encode($new_array);
    print $my_json;
    ?>

这输出...

{"USA":{"California":["San Francisco","Los Angeles"]},"Canada":{"Manitoba":["Winnipeg"],"Ontario":["Toronto"]}}