|
技術文章 > 萬維網發展 > 語言 > PHP > 使用PHP處理資料庫資料轉換
|
使用PHP處理資料庫資料轉換
|
|
撰/JHKChan
前言市面上有各種各樣的資料庫(Database)，但其儲存格式不一，當大家需要將資料作轉換時，有些資料庫或會有專用程式幫助，但過程都令人望而生畏，現在讓我們一起探討以PHP解決的方法。 第一幕後功臣——CSV檔案在深入探討前，我們要先談一談轉換過程中的幕後功臣——CSV檔案。CSV的全寫為Comma-Separated Values，即是檔案內所儲存的資料均以逗號(,)作分隔，如以下例子： "看場人","vancelee@itzone.info","58406827"
"JHKChan","jhkchan@itzone.info","25292705"
"ahliu","ahliu@itzone.info","75640100"
"JcMc","jcmc@itzone.info","45939939"
"鬼","casper@itzone.info","17813197"
...
其實CSV檔案就是這個樣子，plain text的，就這樣隔開每欄(field)，而某些網上程式(如用Perl寫的)也有時懶於使用專業資料庫而使用這種格式，只不過大家不知道它有這名字罷了。可是，CSV檔案基本上可以讓任何資料庫讀取，而平時使用Microsoft Excel(有裝Office時它是預設的關聯程式)或者Access編輯更是方便不過！因此，各種資料庫作資料轉換時，CSV檔案就是中介媒體。 第二幕後功臣——SQL現今的資料庫均基本上支援SQL(結構查詢語言，Structured Query Language)，它是一很方便的查詢語言，兩三句便能處理到資料庫的日常事務，如建立、加入、刪除、更新資料庫或者表格(table)。同樣原理，我們可以用SQL將資料轉換為CSV檔案、或者將CSV檔案內的資料輸入資料庫內。 擷取資料並儲存為CSV檔案的SQL如下(以下假設使用MySQL)： SELECT * INTO OUTFILE "(csv file)" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n" FROM (table)
輸入資料至資料庫的則是： LOAD
DATA INFILE '(csv file)' REPLACE INTO TABLE (table) FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
PHP原始碼(擷取資料) 1 <?
2 /*
3 File Name: csv.php
4 Version: 0.2
5 Creator: JHKChan <jhkchan@itzone.info>
6 History: v0.2 csv file deleted after loading (by sudo) (16/10/2002)
7 automatically for downloading
8 obtain field names automatically
9 generalized to be suitable for any db and table
10 v0.1 firstly created (15/10/2002)
11 create csv file from MySQL
12 */
13 //Global settings
14 $root_path = "/home/somewhere"; /root path in linux for web downloading
15 $filename = "db.csv"; //input temporary filename for creating csv file
16 $db = "database"; //input suitable database
17 $table = "table"; //input suitable table
18 $username = "username"; //input suitable username
19 $passwd = "passwd"; //input suitable passwd
20
21 //clear previous csv file
22 $tmp = shell_exec("sudo rm $root_path$filename");
23
24 //creating csv file
25 $dbd = mysql_connect("localhost", $username, $passwd);
26 mysql_select_db($db, $dbd);
27 $query = "SELECT * INTO OUTFILE '$root_path$filename' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n' FROM $table";
28 mysql_query($query, $dbd);
29
30 //Adding header to csv file
31 $fd = fopen($root_path.$filename, "r");
32 $contents = fread($fd, filesize($filename));
33 fclose($fd);
34
35 //$new_header needs to be changed for different tables
36 $query = "SELECT * FROM $table";
37 $result = mysql_query($query, $dbd);
38 $fields_num = mysql_num_fields($result);
39 $new_header = mysql_field_name($result, 0);
40 for($i = 1; $i < $fields_num; $i++) {
41 $new_header .= ',' . mysql_field_name($result, $i);
42 }
43 $fd = fopen($root_path.$filename, "w");
44 fwrite($fd, $new_header . "\r\n");
45 fwrite($fd, $contents);
46 fclose($fd);
47
48 mysql_close($dbd);
49
50 //prompt for downloading
51 header("Content-Type: application/octet-stream");
52 header("Content-Disposition: attachment; filename=$filename");
53 header("Content-Length: " . filesize($filename));
54 readfile($filename);
55
56 //clear generated csv file
57 $tmp = shell_exec("sudo rm $filename");
58 ?>
原始碼分析於第13-19行為基本設定，需要root path是因為SQL內的檔案處理必須以本機的形式執行，即是/home/itzone.info/php/這樣，建議這個位置是可以經Web形式擷取，因為稍後於第50-54行會讓使用者下載CSV檔案。PHP亦需要於該目錄有寫入權限，以便產生CSV檔案(上例為db.csv)。第22行和第57行是需要使用本機的shell執行，使用者亦需事前安裝sudo(此一程式可讓普通使用者擁有root的權限)，因為SQL產生出來的檔案的擁有者為root，但基於保安理由，如果要被產生出來的CSV檔案已經存在，SQL是不會覆寫(overwrite)該檔的，因此我們必須事前刪除(第22行)，而第57行是防止其他人盜取資料而需要刪除。第30-46行是於產生出來的CSV檔案加上欄名，以便之後在Excel編輯時作參考之用，而將CSV檔案的資料寫入資料庫的SQL的最後部分為"IGNORE 1 LINES"(lines竟然是plural form…)，便是為了跳過這些欄名。最後便是第50-54行，Browser會彈出下載方塊，讓大家下載CSV檔案。 後記以上只是實現了資料由資料庫擷取出來的部分，但其實寫入資料庫部分的最重要SQL語句已提供給大家，只要大家發揮一下創意，整個資料庫資料轉換過程都可被簡化，而phpMyAdmin可讓使用者擷取資料亦是同樣原理。
發表日期：2004-06-21
|
|
|
< 回到分類選單
> 回應此文章
|
|
隨 機 文 章 
