如何使用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 命令导入文件。确保列映射正确，与数据库结构保持一致。