资源连接:http://pan.baidu.com/s/1bocGOB9
效果展示:
执行代码
代码如下public function sqlAction($type){
$conn = $this->get("database_connection");
switch ($type) {
case 1://向省市区表插入数据省市区信息
//插入省
$info_sheng = $conn->fetchAll("SELECT * FROM db_yhm_city WHERE class_type = 1");
foreach ($info_sheng as $index => $value) {
$conn->insert("area", array("parent_id" => 0, "name" => $value["class_name"], "status" => 1));
$conn->insert("area_path", array("area_id" => $conn->lastInsertId(), "level" => 0));
}
//插入市
$info_shi = $conn->fetchAll("SELECT * FROM db_yhm_city WHERE class_type = 2");
foreach ($info_shi as $index => $value) {
$name = $conn->fetchColumn("SELECT class_name FROM db_yhm_city WHERE class_id = ?", array($value["class_parent_id"]));
$id = $conn->fetchColumn("SELECT id FROM area WHERE name = ?", array($name));
$conn->insert("area", array("parent_id" => $id, "name" => $value["class_name"], "status" => 1));
$conn->insert("area_path", array("area_id" => $conn->lastInsertId(), "path_id" => $id, "level" => 1));
}
//插入市区县
$info_qu = $conn->fetchAll("SELECT * FROM db_yhm_city WHERE class_type = 3");
foreach ($info_qu as $index => $value) {
$name = $conn->fetchColumn("SELECT class_name FROM db_yhm_city WHERE class_id = ?", array($value["class_parent_id"]));
$id = $conn->fetchAll("SELECT id FROM area WHERE name = ?", array($name));
if(count($id) == 2){
$id[0] = $id[1];
}
$conn->insert("area", array("parent_id" => $id[0]["id"], "name" => $value["class_name"], "status" => 1));
$conn->insert("area_path", array("area_id" => $conn->lastInsertId(), "path_id" => $id[0]["id"], "level" => 2));
}
break;
case 2://向省市区表插入车牌号信息
$conn->executeUpdate("update area set short_name = """);
// 插入省
$info_sheng = $conn->fetchAll("SELECT * FROM auto_belonging WHERE provinceid = 0");
foreach ($info_sheng as $index => $value) {
$conn->executeUpdate("update area set short_name = "".$value["citycode"]."" where parent_id = 0 AND name LIKE "".substr($value["city"],0,6)."%"");
}
// 插入市(对天津上海和北京特殊处理)
echo "
";
echo "
";
echo "
";
$info_shi_o = $conn->fetchAll("select path_id FROM area_path WHERE level = 1 GROUP BY path_id");
foreach($info_shi_o as $index => $value){
$q_name = $conn->fetchColumn("select short_name FROM area WHERE id = ?",array($value["path_id"]));
if($q_name != "京" && $q_name != "津" && $q_name != "沪" && !empty($q_name)){
$q_id = $conn->fetchColumn("SELECT cityid FROM auto_belonging WHERE citycode = ?",array($q_name));
$q_code = $conn->fetchAll("SELECT * FROM auto_belonging WHERE provinceid = ?",array($q_id));
foreach($q_code as $i => $v){
if(!empty($v["city"])&&!empty(substr($v["citycode"], 3))) {
echo "
";
echo "
".$v["city"]."
>";
// echo "
"."update area set short_name = "" . mb_substr($v["citycode"], 1, 1, "utf-8") . "" WHERE parent_id <> 0 and name LIKE "%" . mb_substr($v["city"], 0, 2, "utf-8") . "%";"."
>";
echo "
"."update area set short_name = "" . mb_substr($v["citycode"], 1, 1,"utf-8") . "" WHERE parent_id <> 0 and name LIKE "%" . mb_substr($v["city"],0,2,"utf-8") . "%"" . "
";
$rows = $conn->executeUpdate("update area set short_name = "" . mb_substr($v["citycode"], 1, 1,"utf-8") . "" WHERE parent_id <> 0 and name LIKE "%" . mb_substr($v["city"],0,2,"utf-8") . "%"");
if($rows < 1){ $rows = $conn->executeUpdate("update area set short_name = "" . mb_substr($v["citycode"],1,1, "utf-8")."" WHERE parent_id <> 0 and name LIKE "%" . mb_substr($v["city"],0,2, "utf-8") . "%"");
if($rows < 1){
echo "
"."update area set short_name = "" . mb_substr($v["citycode"],1,1,"utf-8") . "" WHERE parent_id <> 0 and name LIKE "%" . mb_substr($v["city"], 0, 2, "utf-8") . "%";"."
>";
}
}
for($i = 0; $i<1000 ;$i++){
for($j = 0; $j<1000; $j++){
}
}
echo "
";
}
}
}
}
echo "
";
//说明:香港台湾澳门三个特别区的车牌规则有别于大陆,目前解决方式是有关于这三个地区的车牌信息全部留空
//首先各个省市的车牌号颜色略有差异,但主体为蓝色。
//北京[京]------直辖市
//B(出租车)
//A C E F H J K(市内一般车辆)
//G(远郊区县) 北京地区之间的车辆没有明显区别。“京“字后面的字母不代表地区
//北京的普通牌照一般是蓝色。黑牌白字的是截止今年五一前外外资企业所申请的车牌。
//红色的是使领馆的(第一个字“使”为红字,其余为黑色)。你说的“京B KD264”,这只是一个排列问题
//,先是京+字母十数字,当这个区间号用完后,将采用京+字母+字母+数字,甚至会有京+字母+字母+字母+数字。
//你所说的“北”是北京军区的,白牌,第一个汉字是红色,其余是黑色。各个军区都由军区第一个字开头。
//北京军区的车是“北”,“京”是驻京部队中除了军委(车牌为“军”)、空军(车牌为“空”)、海军(车牌为“海”)
//之外其他部门(比如驻京的研究所、院校、医院等)的车辆。 京A80,81,82,83,88是国务院,国管局,钓鱼台等牛人的车
//处理方法;在第二层存储全部的区别(读取的时候特别处理)
/************************************************************************/
//北京-天津-上海
$berjing_str = $tianjing_str = $shanghai_str = "";
$biejing = $conn->fetchAll("SELECT citycode FROM auto_belonging WHERE provinceid = 397");
$tianjing = $conn->fetchAll("SELECT citycode FROM auto_belonging WHERE provinceid = 411");
$shanghai = $conn->fetchAll("SELECT citycode FROM auto_belonging WHERE provinceid = 420");
foreach($biejing as $index => $value){
$berjing_str = $berjing_str.substr($value["citycode"],3);
}
$conn->update("area",array("short_name" => $berjing_str),array("id" => 51));
foreach($tianjing as $index => $value){
$tianjing_str = $tianjing_str.substr($value["citycode"],3);
}
$conn->update("area",array("short_name" => $tianjing_str),array("id" => 342));
foreach($shanghai as $index => $value){
$shanghai_str = $shanghai_str.substr($value["citycode"],3);
}
$conn->update("area",array("short_name" => $shanghai_str),array("id" => 320));
/*************************************************************************/
// 将县级的清空
$area = $conn->fetchAll("select area_id FROM area_path WHERE level = 2 ");
foreach($area as $index => $value){
$conn ->update("area",array( "short_name" => ""),array("id" => $value["area_id"]));
for($i = 0; $i<1000 ;$i++){
for($j = 0; $j<1000; $j++){ } } } break; } return new JsonResponse(array("data" =>array()));
}
【symfony2】symfony+mysql自动生成全国省市区表及车牌表
http://m.bbyears.com/jiaocheng/101809.html
推荐访问:symfony symfony3 symfony2中文手册 symfony4 symfony2安装