用PHP实现XML数据导入到Mysql
2013-09-14 16:00 杨泽川 阅读(629) 评论(0) 编辑 收藏 举报今天试图将之前用ASP写的XML导入MySQL的程序重新用PHP实现一遍
虽然之前我是学PHP的，但是没有接触到这块函数，所以对XML操作不是很熟练。
先附上XML代码：
<root> <area id="Main" name="直辖市" en="Main"> <area id="Beijing" name="北京" en="Beijing"> <city id="101010100" name="北京" en="Beijing" /> <city id="101010200" name="海淀" en="Haidian" /> <city id="101010300" name="朝阳" en="Chaoyang" /> <city id="101010400" name="顺义" en="Shunyi" /> <city id="101010500" name="怀柔" en="Huairou" /> <city id="101010600" name="通州" en="Tongzhou" /> <city id="101010700" name="昌平" en="Changping" /> <city id="101010800" name="延庆" en="Yanqing" /> <city id="101010900" name="丰台" en="Fengtai" /> <city id="101011000" name="石景山" en="Shijingshan" /> <city id="101011100" name="大兴" en="Daxing" /> <city id="101011200" name="房山" en="Fangshan" /> <city id="101011300" name="密云" en="Miyun" /> <city id="101011400" name="门头沟" en="Mentougou" /> <city id="101011500" name="平谷" en="Pinggu" /> </area> <area id="Shanghai" name="上海" en="Shanghai"> <city id="101020100" name="上海" en="Shanghai" /> <city id="101020200" name="闵行" en="Minhang" /> <city id="101020300" name="宝山" en="Baoshan" /> <city id="101020500" name="嘉定" en="Jiading" /> <city id="101020600" name="南汇" en="Nanhui" /> <city id="101020700" name="金山" en="Jinshan" /> <city id="101020800" name="青浦" en="Qingpu" /> <city id="101020900" name="松江" en="Songjiang" /> <city id="101021000" name="奉贤" en="Fengxian" /> <city id="101021100" name="崇明" en="Chongming" /> <city id="101021200" name="徐家汇" en="Xujiahui" /> <city id="101021300" name="浦东" en="Pudong" /> </area> <area id="Tianjin" name="天津" en="Tianjin"> <city id="101030100" name="天津" en="Tianjin" /> <city id="101030200" name="武清" en="Wuqing" /> <city id="101030300" name="宝坻" en="Baodi" /> <city id="101030400" name="东丽" en="Dongli" /> <city id="101030500" name="西青" en="Xiqing" /> <city id="101030600" name="北辰" en="Beichen" /> <city id="101030700" name="宁河" en="Ninghe" /> <city id="101030800" name="汉沽" en="Hangu" /> <city id="101030900" name="静海" en="Jinghai" /> <city id="101031000" name="津南" en="Jinnan" /> <city id="101031100" name="塘沽" en="Tanggu" /> <city id="101031200" name="大港" en="Dagang" /> <city id="101031400" name="蓟县" en="Jixian" /> </area> <area id="Chongqing" name="重庆" en="Chongqing"> <city id="101040100" name="重庆" en="Chongqing" /> <city id="101040200" name="永川" en="Yongchuan" /> <city id="101040300" name="合川" en="Hechuan" /> <city id="101040400" name="南川" en="Nanchuan" /> <city id="101040500" name="江津" en="Jiangjin" /> <city id="101040600" name="万盛" en="Wansheng" /> <city id="101040700" name="渝北" en="Yubei" /> <city id="101040800" name="北碚" en="Beibei" /> <city id="101040900" name="巴南" en="Banan" /> <city id="101041000" name="长寿" en="Changshou" /> <city id="101041100" name="黔江" en="Qianjiang" /> <city id="101041300" name="万州" en="Wanzhou" /> <city id="101041400" name="涪陵" en="Fuling" /> <city id="101041500" name="开县" en="Kaixian" /> <city id="101041600" name="城口" en="Chengkou" /> <city id="101041700" name="云阳" en="Yunyang" /> <city id="101041800" name="巫溪" en="Wuxi" /> <city id="101041900" name="奉节" en="Fengjie" /> <city id="101042000" name="巫山" en="Wushan" /> <city id="101042100" name="潼南" en="Tongnan" /> <city id="101042200" name="垫江" en="Dianjiang" /> <city id="101042300" name="梁平" en="Liangping" /> <city id="101042400" name="忠县" en="Zhongxian" /> <city id="101042500" name="石柱" en="Shizhu" /> <city id="101042600" name="大足" en="Dazu" /> <city id="101042700" name="荣昌" en="Rongchang" /> <city id="101042800" name="铜梁" en="Tongliang" /> <city id="101042900" name="璧山" en="Bishan" /> <city id="101043000" name="丰都" en="Fengdu" /> <city id="101043100" name="武隆" en="Wulong" /> <city id="101043200" name="彭水" en="Pengshui" /> <city id="101043300" name="綦江" en="Qijiang" /> <city id="101043400" name="酉阳" en="Youyang" /> <city id="101043600" name="秀山" en="Xiushan" /> </area> </area> </root>
开始处理数据，首先就是加载XML文档：
$xml=simplexml_load_file("./city.xml");//root
获得的是根节点root。
然后开始对数据进行循环
foreach($xml->children() AS $root)//provin { $CityId=''; $Provin=''; $ProvinEN=''; $City=''; $CityEN=''; if($root->attributes()=="Main") { foreach($root->children() AS $children1)//city { foreach($children1->attributes() AS $attr=>$val) { if($attr=="name"){ $Provin=$val; echo "省".$attr."='".$val."'"."<br>"; } if($attr=="en"){ $ProvinEN=$val; echo "省".$attr."='".$val."'"."<br>"; } } foreach($children1->children()->attributes() AS $attr=>$val) { if($attr=="id"){ $CityId=$val; echo $attr."='".$val."'"."<br>";} if($attr=="name"){ $City=$val; echo $attr."='".$val."'"."<br>";} if($attr=="en"){ $CityEN=$val; echo $attr."='".$val."'"."<br>";} } $query="INSERT INTO citydb VALUES ('" . $CityId . "','" . $Provin . "','" . $City . "','" . $ProvinEN . "','" . $CityEN . "')"; $result=$db->query($query); echo $query; } } else { foreach($root->attributes() AS $attr=>$val) { if($attr=="name"){ $Provin=$val; echo "省".$attr."='".$val."'"."<br>"; } if($attr=="en"){ $ProvinEN=$val; echo "省".$attr."='".$val."'"."<br>"; } } foreach($root->children() AS $children1)//city { foreach($children1->children()->attributes() AS $attr=>$val) { if($attr=="id"){ $CityId=$val; echo $attr."='".$val."'"."<br>";} if($attr=="name"){ $City=$val; echo $attr."='".$val."'"."<br>";} if($attr=="en"){ $CityEN=$val; echo $attr."='".$val."'"."<br>";} } $query="INSERT INTO citydb VALUES ('" . $CityId . "','" . $Provin . "','" . $City . "','" . $ProvinEN . "','" . $CityEN . "')"; $result=$db->query($query); echo $query; } } }
实现后，在phpmyadmin中发现了乱码
在网上找了一番之后，找到解决方法如下
@ $db=new mysqli('localhost','root','123456','test'); $db->set_charset("utf8");//设置编码
