Q&A
> そのデータを取得し県ごとの市町村の数を表示したいのですが、なかなかうまくいきません。
うまくいきませんとは、どうなるのでしょうか？
どのよううな状況が発生するのかを 発生している内容を具体的に記載していただけないでしょうか。
前提・実現したいこと
jsonファイルのデータをPDOを使ってデータテーブルにINSERTした後、そのデータを取得し県ごとの市町村の数を表示したいのです。
postcodeの先頭1-2文字目とprefecture_idがマッチしているようなので、マッチしていたら変数にcitycodeを代入して、代入された件数を数えるという処理を書いてみたのですが、件数がおかしくなります。どなたか解決方法ご存知でしょうか？
テーブル名 | カラム名 | json.file ( )内はjsonファイル内の対象の値を意味します
-
large_areaテーブル
カラム名: name(エリア名は自分で入れました), prefecture_name(name), prefecture_id(id) -
prefectureテーブル カラム名 : prefecture_id(id), name(name)
-
cityテーブル
カラム名：name(city.city) , citycode(city.citycode)
json.file
1[{ 2 "01": { 3 "id": "01", 4 "name": "北海道", 5 "short": "北海道", 6 "kana": "ホッカイドウ", 7 "en": "hokkaido", 8 "city":[ 9 {"citycode":"0010001","city":"札幌市中央区"}, 10 {"citycode":"0010002","city":"札幌市北区"}, 11 {"citycode":"0010003","city":"札幌市東区"}, 12 {"citycode":"0010004","city":"札幌市白石区"}, 13 {"citycode":"0010005","city":"札幌市豊平区"}, 14 {"citycode":"0010006","city":"札幌市南区"}, 15 {"citycode":"0010007","city":"札幌市西区"}, 16 {"citycode":"0010008","city":"札幌市厚別区"}, 17 {"citycode":"0010009","city":"札幌市手稲区"}, 18 {"citycode":"0010010","city":"札幌市清田区"}, 19 {"citycode":"0010011","city":"函館市"}, 20 {"citycode":"0010012","city":"小樽市"}, 21 {"citycode":"0010013","city":"旭川市"}, 22 {"citycode":"0010014","city":"室蘭市"}, 23 {"citycode":"0010015","city":"釧路市"}, 24 {"citycode":"0010016","city":"帯広市"}, 25 {"citycode":"0010017","city":"北見市"}, 26 {"citycode":"0010018","city":"夕張市"}, 27 {"citycode":"0010019","city":"岩見沢市"}, 28 {"citycode":"0010020","city":"網走市"}, 29 {"citycode":"0010021","city":"留萌市"}, 30 {"citycode":"0010022","city":"苫小牧市"}, 31 {"citycode":"0010023","city":"稚内市"}, 32 {"citycode":"0010024","city":"美唄市"}, 33 {"citycode":"0010025","city":"芦別市"}, 34 {"citycode":"0010026","city":"江別市"}, 35 {"citycode":"0010027","city":"赤平市"}, 36 {"citycode":"0010028","city":"紋別市"}, 37 {"citycode":"0010029","city":"士別市"}, 38 {"citycode":"0010030","city":"名寄市"}, 39 {"citycode":"0010031","city":"三笠市"}, 40 {"citycode":"0010032","city":"根室市"}, 41 {"citycode":"0010033","city":"千歳市"}, 42 {"citycode":"0010034","city":"滝川市"}, 43 {"citycode":"0010035","city":"砂川市"}, 44 {"citycode":"0010036","city":"歌志内市"}, 45 {"citycode":"0010037","city":"深川市"}, 46 {"citycode":"0010038","city":"富良野市"}, 47 {"citycode":"0010039","city":"登別市"}, 48 {"citycode":"0010040","city":"恵庭市"}, 49 {"citycode":"0010041","city":"伊達市"}, 50 {"citycode":"0010042","city":"北広島市"}, 51 {"citycode":"0010043","city":"石狩市"}, 52 {"citycode":"0010044","city":"北斗市"}, 53 {"citycode":"0010045","city":"石狩郡"}, 54 {"citycode":"0010046","city":"松前郡"}, 55 {"citycode":"0010047","city":"上磯郡"}, 56 {"citycode":"0010048","city":"亀田郡"}, 57 {"citycode":"0010049","city":"茅部郡"}, 58 {"citycode":"0010050","city":"二海郡"}, 59 {"citycode":"0010051","city":"山越郡"}, 60 {"citycode":"0010052","city":"檜山郡"}, 61 {"citycode":"0010053","city":"爾志郡"}, 62 {"citycode":"0010054","city":"奥尻郡"}, 63 {"citycode":"0010055","city":"瀬棚郡"}, 64 {"citycode":"0010056","city":"久遠郡"}, 65 {"citycode":"0010057","city":"島牧郡"}, 66 {"citycode":"0010058","city":"寿都郡"}, 67 {"citycode":"0010059","city":"磯谷郡"}, 68 {"citycode":"0010060","city":"虻田郡"}, 69 {"citycode":"0010061","city":"岩内郡"}, 70 {"citycode":"0010062","city":"古宇郡"}, 71 {"citycode":"0010063","city":"積丹郡"}, 72 {"citycode":"0010064","city":"古平郡"}, 73 {"citycode":"0010065","city":"余市郡"}, 74 {"citycode":"0010066","city":"空知郡"}, 75 {"citycode":"0010067","city":"夕張郡"}, 76 {"citycode":"0010068","city":"樺戸郡"}, 77 {"citycode":"0010069","city":"雨竜郡"}, 78 {"citycode":"0010070","city":"上川郡"}, 79 {"citycode":"0010071","city":"勇払郡"}, 80 {"citycode":"0010072","city":"中川郡"}, 81 {"citycode":"0010073","city":"増毛郡"}, 82 {"citycode":"0010074","city":"留萌郡"}, 83 {"citycode":"0010075","city":"苫前郡"}, 84 {"citycode":"0010076","city":"天塩郡"}, 85 {"citycode":"0010077","city":"宗谷郡"}, 86 {"citycode":"0010078","city":"枝幸郡"}, 87 {"citycode":"0010079","city":"礼文郡"}, 88 {"citycode":"0010080","city":"利尻郡"}, 89 {"citycode":"0010081","city":"網走郡"}, 90 {"citycode":"0010082","city":"斜里郡"}, 91 {"citycode":"0010083","city":"常呂郡"}, 92 {"citycode":"0010084","city":"紋別郡"}, 93 {"citycode":"0010085","city":"虻田郡"}, 94 {"citycode":"0010086","city":"有珠郡"}, 95 {"citycode":"0010087","city":"白老郡"}, 96 {"citycode":"0010088","city":"沙流郡"}, 97 {"citycode":"0010089","city":"新冠郡"}, 98 {"citycode":"0010090","city":"浦河郡"}, 99 {"citycode":"0010091","city":"様似郡"}, 100 {"citycode":"0010092","city":"幌泉郡"}, 101 {"citycode":"0010093","city":"日高郡"}, 102 {"citycode":"0010094","city":"河東郡"}, 103 {"citycode":"0010095","city":"河西郡"}, 104 {"citycode":"0010096","city":"広尾郡"}, 105 {"citycode":"0010097","city":"足寄郡"}, 106 {"citycode":"0010098","city":"十勝郡"}, 107 {"citycode":"0010099","city":"釧路郡"}, 108 {"citycode":"0010100","city":"厚岸郡"}, 109 {"citycode":"0010101","city":"川上郡"}, 110 {"citycode":"0010102","city":"阿寒郡"}, 111 {"citycode":"0010103","city":"白糠郡"}, 112 {"citycode":"0010104","city":"野付郡"}, 113 {"citycode":"0010105","city":"標津郡"}, 114 {"citycode":"0010106","city":"目梨郡"} 115 ] 116 }, 117 "02": { 118 "id": "02", 119 "name": "青森県", 120 "short": "青森", 121 "kana": "アオモリケン", 122 "en": "aomori", 123 "city":[ 124 {"citycode":"0020001","city":"青森市"}, 125 {"citycode":"0020002","city":"弘前市"}, 126 {"citycode":"0020003","city":"八戸市"}, 127 {"citycode":"0020004","city":"黒石市"}, 128 {"citycode":"0020005","city":"五所川原市"}, 129 {"citycode":"0020006","city":"十和田市"}, 130 {"citycode":"0020007","city":"三沢市"}, 131 {"citycode":"0020008","city":"むつ市"}, 132 {"citycode":"0020009","city":"つがる市"}, 133 {"citycode":"0020010","city":"平川市"}, 134 {"citycode":"0020011","city":"東津軽郡"}, 135 {"citycode":"0020012","city":"西津軽郡"}, 136 {"citycode":"0020013","city":"中津軽郡"}, 137 {"citycode":"0020014","city":"南津軽郡"}, 138 {"citycode":"0020015","city":"北津軽郡"}, 139 {"citycode":"0020016","city":"上北郡"}, 140 {"citycode":"0020017","city":"下北郡"}, 141 {"citycode":"0020018","city":"三戸郡"} 142 ] 143 }, 144 "03": { 145 "id": "03", 146 "name": "岩手県", 147 "short": "岩手", 148 "kana": "イワテケン", 149 "en": "iwate", 150 "city":[ 151 {"citycode":"0030001","city":"盛岡市"}, 152 {"citycode":"0030002","city":"宮古市"}, 153 {"citycode":"0030003","city":"大船渡市"}, 154 {"citycode":"0030004","city":"花巻市"}, 155 {"citycode":"0030005","city":"北上市"}, 156 {"citycode":"0030006","city":"久慈市"}, 157 {"citycode":"0030007","city":"遠野市"}, 158 {"citycode":"0030008","city":"一関市"}, 159 {"citycode":"0030009","city":"陸前高田市"}, 160 {"citycode":"0030010","city":"釜石市"}, 161 {"citycode":"0030011","city":"二戸市"}, 162 {"citycode":"0030012","city":"八幡平市"}, 163 {"citycode":"0030013","city":"奥州市"}, 164 {"citycode":"0030014","city":"滝沢市"}, 165 {"citycode":"0030015","city":"岩手郡"}, 166 {"citycode":"0030016","city":"紫波郡"}, 167 {"citycode":"0030017","city":"和賀郡"}, 168 {"citycode":"0030018","city":"胆沢郡"}, 169 {"citycode":"0030019","city":"西磐井郡"}, 170 {"citycode":"0030020","city":"気仙郡"}, 171 {"citycode":"0030021","city":"上閉伊郡"}, 172 {"citycode":"0030022","city":"下閉伊郡"}, 173 {"citycode":"0030023","city":"九戸郡"}, 174 {"citycode":"0030024","city":"二戸郡"} 175 ] 176 }, 177 178 以下省略 ※沖縄(47)まであります
該当のソースコード
php
1<?php 2//jsonファイル読み込み 3$json = file_get_contents("sample2.json"); 4$json = mb_convert_encoding($json, 'UTF-8', 'ASCII,JIS,UTF-8,EUC-JP,SJIS-WIN'); 5$json = json_decode($json,true); 6 7 //db接続 8$dsn = 'mysql:host=mzn_db;dbname=mzn; charset=utf8'; 9$user = 'user'; 10$password = 'pass'; 11 12try { 13 $dbh = new PDO($dsn, $user, $password); 14 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,] ; 15 print ('接続に成功しました。<br>'); 16 print "<br>"; 17 18 foreach ($json as $key => $val) { 19 foreach($val as $key2 => $val2){ 20 $name = $val2['name']; 21 $id = $val2['id']; 22 23 $sql = "INSERT INTO prefecture (name, prefecture_id) VALUES (:prefecture_name, :id)"; 24 $stmt = $dbh -> prepare($sql); 25 $stmt->bindValue(':prefecture_name', $name); 26 $stmt->bindValue(':id', $id); 27 $stmt ->execute(); 28 29 //県ごとの区の数を表示 30 $sql5 = "SELECT * FROM prefecture"; 31 $res1 = $dbh -> query($sql5); 32 $data1 = $res1 -> fetchAll(); 33 34 $sql6 = "SELECT * FROM city"; 35 $res2 = $dbh -> query($sql6); 36 $data2 = $res2 -> fetchAll(); 37 38 $num_of_city = []; 39 foreach($data1 as $prefecture_data){ 40 echo $prefecture_data['name'] . "</br>"; 41 echo "</br>"; 42 foreach($data2 as $city_info){ 43 if(substr($city_info['citycode'], 1, 2) == $prefecture_data['prefecture_id']){ 44 $num_of_city [] = $city_info['citycode']; 45 } 46 } 47 echo count($num_of_city); 48 } 49 50} catch (PDOException $e) { 51 print('エラーが発生しました:' . $e->getMessage()); 52 die(); 53}
・INSERTの仕方がわからない
・INSERTしようとしたがエラーになる
・INSERTはできたが想定した集計作業ができない
状況を説明してください
CHERRYさん、yambejpさん
大変失礼しました。具体的にどんな状況が起こっているのか記載しました。
ご指摘ありがとうございました
回答1件
0
結局何が聞きたいかわかりません
DBの構造を模索しているならこう
SQL
1create table prefecture ( 2id char(2) primary key, 3name varchar(10), 4short varchar(10), 5kana varchar(20), 6en varchar(40) 7); 8insert into prefecture values 9('01','北海道','北海道','ホッカイドウ','hokkaido'), 10('02','青森県','青森','アオモリケン','aomori'), 11('03','岩手県','岩手','イワテケン','iwate'); 12 13create table city( 14citycode char(7) primary key, 15pid char(2), 16city varchar(20) 17); 18insert into city values 19('0010001','01','札幌市中央区'), 20('0010002','01','札幌市北区'), 21('0010003','01','札幌市東区'), 22('0010004','01','札幌市白石区'), 23('0020001','02','青森市'), 24('0020002','02','弘前市'), 25('0020003','02','八戸市'), 26('0030001','03','盛岡市'), 27('0030002','03','宮古市'); 28 29select * from prefecture as t1 30inner join (select pid,count(*) as cnt from city group by pid) as t2 31on t1.id=t2.pid
投稿2022/01/19 01:23
総合スコア114747
あなたの回答
tips
プレビュー
