如何使用PHP和MySQL匯入和匯出CSV檔案

PHP 廣泛用於構建從網路應用程式到企業級應用程式的各種產品。高效 PHP 程式碼的關鍵在於遵循正確的工作流程並實現流程自動化。其結果是程式碼質量高、無錯誤。

在幾乎所有的 PHP 應用程式中，資料都是在應用程式的各個元件之間儲存、訪問和交換的。為確保資料交換和訪問順利進行且不出現任何問題，開發團隊必須確保資料庫和資料轉儲採用正確的格式。

在 PHP 開發中，將資料匯入和匯出資料庫是一個非常常見的程式。另一項重要工作是備份和轉移資料庫。

在本文中，我將介紹如何從 mysql 資料表中匯出 csv 檔案。

在MySQL中建立資料庫

本教程的第一步是建立 MySQL 資料庫。部分伺服器提供商在其平臺中提供了自定義的 mysql 管理器，其中包含一個應用程式資料庫，因此您可以透過執行 SQL 查詢來建立表格。使用以下 SQL 查詢在資料庫中建立表格 `employeeinfo`.

CREATE TABLE employeeinfo(
emp_id VARCHAR(50) UNSIGNED PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date VARCHAR(50)
)

這將在資料庫中建立一個新表 `employeeinfo`。我將使用該表插入 CSV 檔案中的資料。

在PHP中建立MySql連線

為匯入和匯出 MySql 資料庫，將建立一個單獨的檔案 `config.php`。新增以下程式碼，並將資料庫憑據替換為您的資料庫憑據。您可以在應用程式訪問詳情（或者Mysql伺服器詳情）中找到您的資料庫憑據：

<?php
function getdb(){
$servername = "localhost";
$username = "huscqxzwaw";
$password = "2WWKxxxxHr";
$db = "huscqxzwaw";
try {
   
    $conn = mysqli_connect($servername, $username, $password, $db);
     //echo "Connected successfully"; 
    }
catch(exception $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
    return $conn;
}
?>

如何使用PHP將CSV匯入MySQL？

建立資料庫後，我們接下來需要一個可以上傳 CSV 檔案的 HTML 檔案。讓我們按照簡單的步驟開始吧。

第 1 步：建立資料庫和表

首先，需要在 MySQL 中建立一個資料庫和一個表，用於儲存 CSV 資料。下面是一個建立名為 employeeinfo 的表的 SQL 查詢示例：

CREATE TABLE employeeinfo (
    emp_id INT(11) NOT NULL,
    firstname VARCHAR(255) NOT NULL,
    lastname VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    reg_date DATE NOT NULL,
    PRIMARY KEY (emp_id)
);

第 2 步：設計匯入表單

現在，我們需要建立一個 HTML 表單來上傳 CSV 檔案。該表單將允許使用者從自己的電腦中選擇 CSV 檔案並將其上傳到伺服器。

<form action="index.php" method="post" enctype="multipart/form-data">
    <input type="file" name="file" accept=".csv">
    <button type="submit" name="Import">Import CSV</button>
</form>

第 3 步：處理檔案上傳和驗證

在 PHP 指令碼中處理檔案上傳，並驗證上傳的檔案是否為 CSV 檔案。

if (isset($_POST["Import"])) {
    $filename = $_FILES["file"]["tmp_name"];
    
    // Check if the file is a CSV file
    if (pathinfo($_FILES["file"]["name"], PATHINFO_EXTENSION) != "csv") {
        echo "Please upload a CSV file.";
        exit;
    }
    
    // Proceed with importing the CSV file
    importCSV($filename);
}

Step 4：將 CSV 資料匯入 MySQL

建立一個名為 importCSV() 的函式來處理 CSV 匯入。該函式將讀取 CSV 檔案，並使用準備語句將資料插入 MySQL 資料庫。

functions.php（匯入部分）以下是如何安全地實現 importCSV() 函式：

function importCSV($filename) {
    // Get a secure database connection
    $conn = getConnection(); // Ensure this function returns a secure connection
    
    // Open the CSV file
    $file = fopen($filename, "r");
    
    // Skip the header row (if it exists)
    fgetcsv($file, 10000, ",");  // Adjust delimiter if needed
    
    // Prepare the SQL query
    $sql = "INSERT INTO employeeinfo (emp_id, firstname, lastname, email, reg_date) VALUES (?, ?, ?, ?, ?)";
    $stmt = $conn->prepare($sql);
    
    if ($stmt === false) {
        die("Error preparing statement: " . $conn->error); // Handle prepare error
    }
    
    // Bind parameters
    $stmt->bind_param("sssss", $emp_id, $firstname, $lastname, $email, $reg_date);
    
    while (($getData = fgetcsv($file, 10000, ",")) !== FALSE) {
        // Validate data (example validation)
        if (count($getData) != 5) {
            error_log("Invalid CSV row: " . implode(",", $getData));
            continue; // Skip to the next row
        }
        
        // Assign values
        $emp_id = $getData[0];
        $firstname = $getData[1];
        $lastname = $getData[2];
        $email = $getData[3];
        $reg_date = $getData[4];
        
        // Execute the query
        if (!$stmt->execute()) {
            error_log("Error inserting row: " . $stmt->error);
        }
    }
    
    // Close the statement and connection
    $stmt->close();
    fclose($file);
    $conn->close();
    
    echo "<script type=\"text/javascript\">
            alert(\"CSV File has been successfully Imported.\");
            window.location = \"index.php\"
          </script>";
}
// Example of a secure getConnection() function
function getConnection(): mysqli
{
    $servername = $_ENV["DB_HOST"] ?? "localhost"; // Default to localhost if not set
    $username = $_ENV["DB_USER"] ?? "your_db_user";   // Provide reasonable defaults
    $password = $_ENV["DB_PASSWORD"] ?? "your_db_password";
    $dbname = $_ENV["DB_NAME"] ?? "your_db_name";
    try {
        $conn = new mysqli($servername, $username, $password, $dbname);
        if ($conn->connect_error) {
            throw new Exception("Connection failed: " . $conn->connect_error);
        }
        return $conn;
    } catch (Exception $e) {
        error_log("Database connection error: " . $e->getMessage()); // Log the error
        die("Failed to connect to the database.  Check your configuration."); // Fatal error, stop execution
    }
}

替代方案：使用MySQL的LOAD DATA INFILE

對於大型 CSV 檔案，可以考慮使用 MySQL 的 LOAD DATA INFILE 命令，這比使用 PHP 逐行處理檔案要快得多：

$loadSQL = "LOAD DATA LOCAL INFILE '$filename' INTO TABLE employeeinfo FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS";
mysqli_query($conn, $loadSQL);

如果從遠端伺服器執行查詢，此方法需要使用 LOCAL 關鍵字。確保 MySQL 使用者擁有使用 LOAD DATA INFILE 的必要許可權。

顯示儲存的記錄

匯入 CSV 檔案後，我將透過在 `index.php` 中初始化的簡單函式 `get_all_records()` 顯示資料。將此函式複製到 `function.php` 中。

function get_all_records(){
    $con = getdb();
    $Sql = "SELECT * FROM employeeinfo";
    $result = mysqli_query($con, $Sql);  
    if (mysqli_num_rows($result) > 0) {
     echo "<div class='table-responsive'><table id='myTable' class='table table-striped table-bordered'>
             <thead><tr><th>EMP ID</th>
                          <th>First Name</th>
                          <th>Last Name</th>
                          <th>Email</th>
                          <th>Registration Date</th>
                        </tr></thead><tbody>";
     while($row = mysqli_fetch_assoc($result)) {
         echo "<tr><td>" . $row['emp_id']."</td>
                   <td>" . $row['firstname']."</td>
                   <td>" . $row['lastname']."</td>
                   <td>" . $row['email']."</td>
                   <td>" . $row['reg_date']."</td></tr>";        
     }
    
     echo "</tbody></table></div>";
     
} else {
     echo "you have no records";
}
}

在這個非常簡單的方法中，我只是選擇了所有記錄，並透過該方法將這些記錄顯示在索引頁面上。每當使用者上傳 CSV 檔案時，記錄就會儲存在表格中，然後顯示在索引頁面上。

用PHP將MySQL匯出為CSV

將資料從 MySQL 資料庫匯出到 CSV 檔案同樣非常簡單。為了演示這一點，我將使用之前建立的 index.php 。

在檔案中新增以下程式碼。

<div>
            <form class="form-horizontal" action="functions.php" method="post" name="upload_excel"   
                      enctype="multipart/form-data">
                  <div class="form-group">
                            <div class="col-md-4 col-md-offset-4">
                                <input type="submit" name="Export" class="btn btn-success" value="export to excel"/>
                            </div>
                   </div>                    
            </form>           
 </div>

新增 HTML 標記後，表格下方會出現 Export 按鈕。現在在 functions.php 中新增以下條件。

if(isset($_POST["Export"])){
     
      header('Content-Type: text/csv; charset=utf-8');  
      header('Content-Disposition: attachment; filename=data.csv');  
      $output = fopen("php://output", "w");  
      fputcsv($output, array('ID', 'First Name', 'Last Name', 'Email', 'Joining Date'));  
      $query = "SELECT * from employeeinfo ORDER BY emp_id DESC";  
      $result = mysqli_query($con, $query);  
      while($row = mysqli_fetch_assoc($result))  
      {  
           fputcsv($output, $row);  
      }  
      fclose($output);  
 }

點選 `Export` 按鈕後，將傳送包含附件 `data.csv` 的標題 `Content-Type: text/csv`。

由於 `php://output` 是隻寫流，允許對輸出緩衝機制進行寫訪問，因此我從下一行的表格中選取了所有資料，並將其傳遞給了 `fputcsv()` 方法。該方法將一行資料（以欄位陣列形式傳遞）格式化為 CSV 格式，並將其寫入指定檔案（以換行結束）。最後，下載包含所有所需資料的檔案。

最後，在整合所有程式碼後，您將看到以下應用程式的最終形態。

小結

在本文中，我討論瞭如何使用 PHP 和 MySQL 從 CSV 檔案匯出資料並將資料匯入 CSV 檔案。這是一個簡單的示例，您可以根據自己的要求新增更復雜的邏輯和驗證。您還可以建立測試用例來驗證程式碼，並使用 PHP 持續整合工具與 GitHub 整合。如果您想參與討論或提問，請在下面留言。

常見問題

如何使用 PHP 和 MySQL 匯入和匯出 CSV？

要在 PHP 和 MySQL 中匯入和匯出 CSV 檔案，首先要使用 PHP 的 is_uploaded_file() 函式驗證上傳的檔案。然後，使用 fopen() 開啟檔案，並使用 fgetcsv() 讀取檔案內容。解析資料後，根據唯一識別符號（如電子郵件）將資料插入或更新到 MySQL 資料庫中。

有沒有專門用於匯入/匯出 CSV 資料的 PHP 庫或函式？

有，PHP 提供了幾種處理 CSV 檔案的工具。內建的 fgetcsv() 函式可以讀取 CSV 行，而 League\Csv 等庫可以簡化 CSV 資料的管理，PhpSpreadsheet 支援各種檔案格式。此外，ParseCsv 是一個輕量級選項，可用於 CSV 特定任務。

使用 PHP 和 MySQL 匯入或匯出大型 CSV 檔案時有什麼限制或注意事項嗎？

在處理大型 CSV 檔案時，主要的考慮因素包括記憶體使用量、執行時間和處理大塊資料的能力。重要的是要驗證和消毒資料，確保正確的編碼和格式，並實施錯誤處理，以便更順利地進行處理。

在匯入和匯出 CSV 資料方面，有哪些方法可以替代 PHP 和 MySQL？

在處理 CSV 檔案方面，PHP 和 MySQL 的替代工具包括用於強大資料處理的 Python 和 pandas、用於統計任務的 R 和用於 Apache Commons CSV 的 Java。awk和sed等命令列工具提供了文字處理功能，而 Excel 或 Google Sheets 則為小型資料集提供了簡便的介面。

如何使用 PHP 和 MySQL 匯入和匯出 CSV 檔案？

要匯入和匯出 CSV 檔案，需要使用 is_uploaded_file() 驗證上傳的檔案，並使用 fopen() 和 fgetcsv() 讀取檔案。解析後，將資料插入或更新到 MySQL，確保正確處理較大的檔案，包括分塊和記憶體管理。

如何使用 PHP 將 CSV 匯入 MySQL？

要使用 PHP 將 CSV 匯入 MySQL，請使用 fopen() 開啟 CSV，然後使用 fgetcsv() 讀取每一行，並使用 SQL 查詢將資料插入 MySQL 資料庫。確保正確處理檔案路徑和錯誤，以提高匯入效率。

如何將 CSV 檔案匯出到 MySQL？

將 CSV 檔案匯入 MySQL 需要準備 CSV，在 MySQL 中建立表格，並使用 LOAD DATA INFILE 等 SQL 命令匯入檔案。確保列對映正確，與資料庫結構保持一致。