php 全国省市代码,symfony-php-mysql 自动生成全国省市区表及车牌表

效果展示：

执行代码

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 '

name

';

echo '

sql

';

$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()));

}

转载时请注明出处及相应链接，本文永久地址：https://blog.yayuanzi.com/15574.html

微信打赏

支付宝打赏

感谢您对作者Jay的打赏，我们会更加努力！    如果您想成为作者，请点我