Loading a CSV File into a MySQL Database using PHP

Importing data from CSV files into a database is one of the most common tasks when managing web applications, blogs, or online stores. While phpMyAdmin is an option, a custom PHP script allows for automation and better control over the data.

Process Logic

The script operates in a few simple steps:

• Connection: Establish a connection with the MySQL server using the mysqli class.
• Reading: Use the fgetcsv() function to read the file line by line.
• Security: Implement Prepared Statements to ensure that special characters in the data are stored safely and efficiently.

Improved Code (Recommended)

The following version uses security protocols to prevent SQL injection and processes large files more efficiently.

<?php
$streznik = "localhost";
$uporabnik = "root";
$geslo = "";
$baza_podatkov = "your_database_name";

// Create connection
$povezava = new mysqli($streznik, $uporabnik, $geslo, $baza_podatkov);

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

$csvFile = 'data.csv';

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

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

    // Prepare the SQL statement
    $stmt = $povezava->prepare("INSERT INTO imena (ime, povezava) VALUES (?, ?)");

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

        // Bind parameters and execute
        $stmt->bind_param("ss", $ime, $povezava_pot);
        if (!$stmt->execute()) {
            echo "Error at line: " . $stmt->error . "&lt;br&gt;";
        }
    }

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

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