Practical PHP Techniques for Data Import and Export with Code Examples

Introduction to PHP Data Import and Export

Data import and export are common and essential tasks in web development. Whether importing data from external files into a database or exporting database data to CSV or Excel files, it requires proper use of PHP's file handling and database functions. This article systematically explains how to achieve these functions and provides concrete PHP code examples to help you master these techniques quickly.

Implementing Database Data Import

Database import typically refers to importing external data files such as CSV into database tables. The following code example demonstrates the basic import process:

<?php
// Include database configuration file
require_once 'config.php';

// Connect to the database
$conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

// Check database connection
if ($conn->connect_error) {
    die("Database connection failed: " . $conn->connect_error);
}

// Read external CSV file
$csvFile = 'data.csv';
$handle = fopen($csvFile, 'r');

// Read CSV file line by line and insert into database
while (($data = fgetcsv($handle, 1000, ',')) !== false) {
    $sql = "INSERT INTO `tablename` (`column1`, `column2`, `column3`) VALUES ('" 
            . $data[0] . "', '" . $data[1] . "', '" . $data[2] . "')";
    if ($conn->query($sql) === false) {
        echo "Data import failed: " . $conn->error;
    }
}

// Close file handle and database connection
fclose($handle);
$conn->close();
?>

Code explanation: First, the database configuration is included and the connection is established. Then, the CSV file is opened with fopen, data is read line by line using fgetcsv, and SQL statements are constructed to insert data into the database. Finally, resources are closed to complete the import.

Implementing Database Data Export

Data export usually means exporting database table data into a CSV file for further processing or backup. The following example shows how to implement export functionality:

<?php
// Include database configuration file
require_once 'config.php';

// Connect to the database
$conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

// Check database connection
if ($conn->connect_error) {
    die("Database connection failed: " . $conn->connect_error);
}

// Query data from the database
$sql = "SELECT * FROM `tablename`";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Create CSV file and write header
    $filename = 'data.csv';
    $handle = fopen($filename, 'w');
    fputcsv($handle, array('Column 1', 'Column 2', 'Column 3'));

    // Write data rows
    while ($row = $result->fetch_assoc()) {
        fputcsv($handle, array($row['column1'], $row['column2'], $row['column3']));
    }

    // Close file handle
    fclose($handle);

    echo "Data export successful!";
} else {
    echo "No data found in the database!";
}

// Close database connection
$conn->close();
?>

Code description: The code queries data from the database, and if results exist, creates a CSV file, writes the header and data rows, and closes resources. A success message is shown upon completion.

Conclusion

This article introduced how to implement data import and export functionality using PHP by combining file and database operations. The provided simple and clear reference code can help developers handle data migration and backup tasks efficiently in real projects, improving development productivity.