地址整理 php mysql xml 匯入匯出

2012-06-15

4154
0
PHP

摘要:地址整理 php mysql xml 匯入匯出

要將輸入住址為了讓使用者懶惰使用下拉式選單動態一步一步產生

若有後端語言+資料庫處理那就去郵局下載xml或xls檔 excel檔匯入進去在處理

當我下載完光開起XML我就開到傻甚至開到掛幾次更別說給人產生懶惰愉快的下拉式選單

所以我就著手將XML塞到SQL

PS.我選擇 XML原因是 objective c php asp.net javascript..........等都可以解析

將php.ini中更改最大等待時間原本30秒若效能不高請拉長時間

第一步將六萬筆的資料塞到SQL

$doc = new DOMDocument();
	$doc->load( '234.xml' );
	$zip32 = $doc->getElementsByTagName( "zip32" );
	foreach( $zip32 as $zip )
	{
		
		$city = $zip->getElementsByTagName( "City" );
		$ct = $city->item(0)->nodeValue;
  
		$area = $zip->getElementsByTagName( "Area" );
		$ae = $area->item(0)->nodeValue;
	  
		$road = $zip->getElementsByTagName( "Road" );
		$ra = $road->item(0)->nodeValue;
		$sql = "INSERT INTO acc( city, area, road )VALUES ( '".$ct."','".$ae."','".$ra."')";
		mysql_query($sql);
	  echo " $ct-$ae-$ra
";
	}

然後在開始拆解個縣市/鄉鎮區/路

第二步會整理所有城市出來自動開啟資料表

$sql="SELECT id,city FROM acc GROUP BY city ORDER BY id ASC";
	$qey=mysql_query($sql);
	while($row=mysql_fetch_assoc($qey))
	{
		$city[]=$row['city'];
	}
	if(count($city)>0)
	{
		$sql="CREATE TABLE citys (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,city VARCHAR( 10 ) NULL) ENGINE = MYISAM";
		mysql_query($sql);
		foreach($city as $ct)
		{
			$sql = "INSERT INTO citys(city)VALUES('$ct')";
			mysql_query($sql);
		}
	}
	unset($city);

第三步就是開始整理塞資料摟

資料表命名規則區鄉鎮 area+城市id, 路街 area+城市id+road+區id

$sql="SELECT * FROM citys ORDER BY id ASC";
	$qey=mysql_query($sql);
	while($row=mysql_fetch_assoc($qey))
	{
		$city[]=array('id'=>$row['id'],'city'=>$row['city']);
	}
	
	foreach($city as $cy)//城市
	{
		$sql="SELECT * FROM  acc  WHERE city='".$cy['city']."' GROUP BY area ORDER BY id ASC";
		$qey1=mysql_query($sql);
		
		while($row2=mysql_fetch_assoc($qey1))
		{
			$areas[]=$row2['area'];
		}
		
		foreach($areas as $as)//區
		{
			$sql="CREATE TABLE area".$cy['id']."(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,area VARCHAR( 10 ) NULL) ENGINE = MYISAM";
			mysql_query($sql);
			$sql="INSERT INTO area".$cy['id']."(area)VALUES('$as')";
			mysql_query($sql);
			
			$sql="SELECT * FROM area".$cy['id'];
			$qey2=mysql_query($sql);
			
			while($row3=mysql_fetch_assoc($qey2))
			{
				$areas2[]=array('id'=>$row3['id'],'area'=>$row3['area']);
			}
			
			foreach($areas2 as $as2)
			{
				$sql="CREATE TABLE area".$cy['id']."road".$as2['id']."(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,road VARCHAR( 30 ) NULL) ENGINE = MYISAM";
				mysql_query($sql);		
			}
			unset($areas2);
		}
		unset($areas);
		
		$sql="SELECT * FROM area".$cy['id'];
			$qey2=mysql_query($sql);
			
			while($row3=mysql_fetch_assoc($qey2))
			{
				$areas2[]=array('id'=>$row3['id'],'area'=>$row3['area']);
			}
		foreach($areas2 as $as2)
		{
			$sql="SELECT * FROM acc WHERE city='".$cy['city']."'AND area='".$as2['area']."' GROUP BY road ORDER BY id ASC ";
			$qey3=mysql_query($sql);
				
			while($row4=mysql_fetch_assoc($qey3))
			{
			    $road[]=$row4['road'];
			}	
				//print_r($road);
				
				foreach($road as $rd)
				{
				    $sql="INSERT INTO area".$cy['id']."road".$as2['id']." (road)VALUES('$rd')";
				    mysql_query($sql);
				}
				unset($road);
				
		}
		unset($areas2);
		
	}

	echo "end";

結束

這樣就可以給前端透過ajax方法來撈摟

但是我想想有時又有人不要後端語言又想懶惰怎辦所以將資料庫轉出XML給前端自己玩摟

偏偏我sql語法超弱都用phpmyadmin 都用點點點當匯出整個資料庫時是將全部寫在一個XML裡面

那還搞屁阿我需求一個table一張XML 難道只能乖乖點300次以上嗎

於是我有開始找方法摟.........................續

以下就是我的方法

$sql="SHOW TABLES";
	$qry=mysql_query($sql);
	while($row=mysql_fetch_assoc($qry))
	{
		$tables[]=$row;
	}
	foreach($tables as $table)
	{
		$sql="describe ".$table["Tables_in_acc"];
		$qry=mysql_query($sql);
		while($row2=mysql_fetch_assoc($qry))
		{
			$tbfd[]=$row2['Field'];
		}
		$sql="SELECT * FROM ".$table["Tables_in_acc"];
		$qry=mysql_query($sql);
		$dom = new DomDocument('1.0');
		$dom->encoding = 'UTF-8';
		${$table["Tables_in_acc"]} = $dom->appendChild($dom->createElement($table["Tables_in_acc"]));
		while($row3=mysql_fetch_assoc($qry))
		{
			$zip = ${$table["Tables_in_acc"]}->appendChild($dom->createElement('zip'));
			foreach($tbfd as $td)
			{
				${$td}=$zip->appendChild($dom->createElement($td));
				${$td}->appendChild($dom->createTextNode($row3[$td]));	
			}			
		}
		$dom->formatOutput = true;
		$test1 = $dom->saveXML();	
		$dom -> save('./acc/'.$table["Tables_in_acc"].'.xml'); 	
		unset($tbfd);
		unset($dom);	
	}

回首頁