Wavesdream

Import CSV data into MySQL using PHP

Written by

Sanjay Bhowmick

in

Here’s a detailed example of how to import data from a CSV file into a MySQL database using PHP. The script processes each row one by one, displays a success message for each successfully inserted row, and stops the process if any error occurs, showing the error message.

Prerequisites:

  1. Ensure you have a MySQL database and table set up to store the CSV data.
  2. Adjust the database connection details and table schema as needed.

Database Setup:

Assume you have a MySQL table named csv_import with columns id, name, and email.

CREATE TABLE csv_import (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

Here’s a PHP script that handles the CSV import process:

<?php
// Database connection details
$servername = "your_servername";
$username = "your_username";
$password = "your_password";
$dbname = "your_dbname";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

if (isset($_POST['submit'])) {
    $csvFile = $_FILES['csv']['tmp_name'];

    if (is_file($csvFile)) {
        // Open the CSV file
        if (($handle = fopen($csvFile, "r")) !== FALSE) {
            $rowNumber = 0;

            // Process each row of the CSV file
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                $rowNumber++;

                // Skip the header row
                if ($rowNumber == 1) {
                    continue;
                }

                $name = $conn->real_escape_string($data[0]);
                $email = $conn->real_escape_string($data[1]);

                // Insert the data into the database
                $sql = "INSERT INTO csv_import (name, email) VALUES ('$name', '$email')";
                
                if ($conn->query($sql) === TRUE) {
                    echo "Row $rowNumber inserted successfully.<br>";
                } else {
                    echo "Error inserting row $rowNumber: " . $conn->error . "<br>";
                    break;
                }
            }

            fclose($handle);
        } else {
            echo "Error opening the CSV file.";
        }
    } else {
        echo "Invalid file.";
    }
}
$conn->close();
?>
Here the HTML to upload CSV file

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CSV Import</title>
</head>
<body>
    <form action="" method="post" enctype="multipart/form-data">
        <label for="csv">Choose CSV file:</label>
        <input type="file" name="csv" id="csv" required>
        <button type="submit" name="submit">Import CSV</button>
    </form>
</body>
</html>

Explanation:

  1. Database Connection: Establish a connection to the MySQL database using the mysqli extension.
  2. Form Handling: The script checks if the form is submitted and processes the uploaded CSV file.
  3. CSV File Processing: It opens the CSV file and processes each row one by one using a while loop.
    It skips the header row.
    It escapes the data using $conn->real_escape_string to prevent SQL injection.
    It inserts the data into the csv_import table.
    It shows a success message for each inserted row.
    If any error occurs, it shows an error message and stops the process.
  4. HTML Form: The form allows the user to upload a CSV file.

More posts