PHP CSV匯入資料庫

duDaoRuConfig.php頁面

<?php$db_name="wordpress";//如需更改資料庫配置在此更改即可$conn = mysql_connect("localhost", "root", "root");mysql_select_db($db_name, $conn);mysql_query("set names 'UTF-8'");//解析csv檔案，返回二維數組，第一維是一共有多少行csv資料，第二維是鍵名為csv列名，值為當前行當前列的csv資料值function input_csv($csv_file) {    $result_arr = array ();    $i = 0;    while ($data_line = fgetcsv($csv_file, 10000)) {        if($i == 0){            $GLOBALS['csv_key_name_arr'] = $data_line;            $i++;            continue;        }        foreach($GLOBALS['csv_key_name_arr'] as $csv_key_num=>$csv_key_name){            $result_arr[$i][$csv_key_name] = $data_line[$csv_key_num];        }        $i++;    }    return $result_arr;}?><script type="text/javascript" src="jquery-1.8.2.js"></script>

doDaoRu.php

<form action="doDaoRu2.php" method="post" enctype="multipart/form-data">    <input type="file" name="csv_file" size="50" maxlength="100000" /><br/>    <input type="submit" value="submit"/></form>

doDaoRu2.php

<?phpinclude_once("duDaoRuConfig.php");$dir = "./upload/";if (is_dir($dir) == false) {    mkdir($dir, 0777);//在頁面目錄下要建立upload檔案夾用來儲存上傳csv檔案}//1，儲存csv檔案$csv_filename = $_FILES["csv_file"]["name"];move_uploaded_file($_FILES["csv_file"]["tmp_name"], "./upload/" . $_FILES["csv_file"]["name"]);//2，擷取所有表名$selAllTableName_str = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '$db_name'";$allTableName_que = mysql_query($selAllTableName_str);//3，擷取csv檔案資料的所有列名$csv_key_name_arr = array();//4，以csv列名為鍵名擷取csv所有資料$csv_file = fopen('upload/'.$csv_filename, 'r');$result_arr = input_csv($csv_file);fclose($csv_file);?><form action="doDaoRu3.php" method="post">    列名:    <select class="table_name_sel" name="table_name_sel">        <option> </option>        <?php        while($tableName_row = mysql_fetch_array($allTableName_que)){//可選擇所有表名        ?>        <option><?php echo $tableName_row['table_name']?></option>        <?php        }        ?>    </select>    <br/>    <br/>    <br/>    <?php    foreach($csv_key_name_arr as $csv_key_name)//羅列csv所有列名，並選擇要匯入到的對應表名，或不匯入該csv列    {    ?>    <span>        <input type="hidden" name="<?php echo $csv_key_name;?>" class="csv_key_name_hid" value=""/><?php echo $csv_key_name;?>        <select class='table_column_name_sel'>            <option> </option>        </select>    </span>    <?php    }    ?>    <input type="hidden" name="csv_filename_hid" value="<?php echo $csv_filename;?>"/>    <input type="submit" value="submit"/></form><script type="text/javascript">$(".table_column_name_sel").change(function(){//當為csv列名選擇對應表列名時，為該csv隱藏欄位值賦選擇的表列名    $(this).parent().find("input").val($(this).val());})$(".table_name_sel").change(function(){    $(".csv_key_name_hid").val("");    var tableName = $(this).val();    var ajaxAddUrl = "doDaoRuAjax.php";    //window.location = ajaxAddUrl+"?tableName="+tableName;    $.post(ajaxAddUrl,{'tableName':tableName},function(jieShou){        $(".table_column_name_sel option").remove();        $(".table_column_name_sel").append("<option> </option>");        $.each(jieShou,function(i,n){            $(".table_column_name_sel").append("<option>"+n+"</option>");        })    },"json");});</script>

doDaoRu3.php

<?phpinclude_once("duDaoRuConfig.php");$csv_filename = $_POST['csv_filename_hid'];$tableName = $_POST['table_name_sel'];//儲存需要儲存的csv資料的列與表列的關聯$table_real_column_name_arr = array();//3，儲存csv檔案資料的所有列名$csv_key_name_arr = array();//擷取csv所有列名及解析資料$csv_file = fopen('upload/'.$csv_filename, 'r');$result_arr = input_csv($csv_file);//csv資料條數（已扣除第一行列名）$result_arr_len = count($result_arr);//將需要儲存的csv列與表的列關聯起來foreach($csv_key_name_arr as $csv_key_name){    if($_POST[$csv_key_name]){//判斷前頁該csv列已賦表列名        $table_real_column_name_arr[$csv_key_name] = $_POST[$csv_key_name];    }}//使用批插入，拼湊所有需插入的資料到一個長字串的sql語句中$all_insert_data_value_str = "";for ($i = 1; $i <= $result_arr_len; $i++) { //迴圈擷取各欄位值    $csv_line_data_value = "";    $j = 1;    foreach($table_real_column_name_arr as $csv_real_key_name=>$table_real_column_name){        if($j == count($table_real_column_name_arr)){            $csv_line_data_value .= " ' ".$result_arr[$i][$csv_real_key_name]."' ";        }else{            $csv_line_data_value .= " '".$result_arr[$i][$csv_real_key_name]."', ";        }        $j++;    }    $all_insert_data_value_str .= " ($csv_line_data_value) ,";}$all_insert_data_value_str = substr($all_insert_data_value_str,0,-1); //去掉最後一個逗號//拼湊所有需插入值的欄位名到一個長字串的sql語句中$all_insert_column_name_str = "";$i = 1;foreach($table_real_column_name_arr as $csv_real_key_name=>$table_real_column_name){    if($i == count($table_real_column_name_arr)){        $all_insert_column_name_str .= " $table_real_column_name ";    }else{        $all_insert_column_name_str .= " $table_real_column_name , ";    }    $i++;}//問題，1文本中如有斷行符號換行會導致字串中有逸出字元匯入失敗，2中文亂碼//執行批插入，csv匯入完成$query = mysql_query("insert into $tableName ($all_insert_column_name_str) values $all_insert_data_value_str");//批量插入資料表中fclose($csv_file);if($query){    echo '匯入成功！';}else{    echo '匯入失敗！';}?>

doDaoRuAjax.php

<?phpinclude_once("duDaoRuConfig.php");$talbeName = $_POST['tableName'];//查詢該表所有列$selAllColumns_str = "SHOW COLUMNS FROM $talbeName";$allColumnsName_que = mysql_query($selAllColumns_str);$allColumnsName_arr = array();$i = 0;while($allColumnsName_row = mysql_fetch_array($allColumnsName_que)){    $allColumnsName_arr[$i] = $allColumnsName_row['Field'];    $i++;}ob_end_clean();echo json_encode($allColumnsName_arr);?>