How to Import a CSV File into a MySQL Database with PHP

Importing data from CSV files into a database is one of the most common tasks when managing web applications, blogs, or shops. Although we can use phpMyAdmin, our own PHP script allows for automation and better control over the data.

Process Logic

The script works in a few simple steps:

• Connection: We establish a connection to the MySQL server using the mysqli class.
• Reading: We read the file line by line using the fgetcsv() function.
• Security: We use prepared statements (Prepared Statements), which ensure that special characters in the data are stored safely.

Improved Code (Recommended)

The version below uses security protocols that prevent SQL injection and process large files faster.

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "your_database_name";

// Create connection
$connection = new mysqli($server, $username, $password, $database);

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

$csvFile = 'data.csv';

// Check if the file exists
if (!file_exists($csvFile)) {
    die("Error: CSV file does not exist.");
}

// Open the CSV file for reading
if (($handle = fopen($csvFile, "r")) !== FALSE) {
    // Skip the first row (header with column names)
    fgetcsv($handle, 10000, ",");

    // Prepare the statement (Prepared Statement)
    $stmt = $connection->prepare("INSERT INTO names (name, link) VALUES (?, ?)");

    // Read each line of the CSV file
    while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) {
        $name = $data[0];
        $link_path = $data[1];

        // Bind parameters and execute
        $stmt->bind_param("ss", $name, $link_path);
        if (!$stmt->execute()) {
            echo "Error on line: " . $stmt->error . "<br>";
        }
    }

    $stmt->close();
    fclose($handle);
}

$connection->close();
echo "Import completed successfully.";
?>