Import data from a CSV file into a MySQL using PHP

To import data from a CSV file into a MySQL database using PHP, you can use the following steps. In this example, I’ll assume you have a CSV file with data that you want to insert into a MySQL table.

Step 1: Create a Database and Table

First, create a MySQL database and a table to hold the data from the CSV file. Here’s an example SQL script to create a sample table:

CREATE DATABASE mydatabase;
USE mydatabase;

CREATE TABLE mytable (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20)
);

Step 2: PHP Script to Import CSV Data

Next, create a PHP script to read the CSV file and insert its data into the MySQL table. Here’s an example:

<?php
// Database configuration
$hostname = 'localhost';
$username = 'your_username';
$password = 'your_password';
$database = 'mydatabase';

// CSV file to import
$csvFile = 'imported_data.csv';

// Connect to MySQL database
$mysqli = new mysqli($hostname, $username, $password, $database);

// Check if the connection was successful
if ($mysqli->connect_error) {
    die('Connection failed: ' . $mysqli->connect_error);
}

// Read the CSV file
if (($handle = fopen($csvFile, 'r')) !== false) {
    while (($data = fgetcsv($handle, 1000, ',')) !== false) {
        $name = $mysqli->real_escape_string($data[0]);
        $email = $mysqli->real_escape_string($data[1]);
        $phone = $mysqli->real_escape_string($data[2]);

        // Insert data into the table
        $sql = "INSERT INTO mytable (name, email, phone) VALUES ('$name', '$email', '$phone')";
        if ($mysqli->query($sql) === false) {
            echo 'Error: ' . $mysqli->error;
        }
    }
    fclose($handle);
    echo 'CSV data imported successfully.';
} else {
    echo 'Unable to open the CSV file.';
}

// Close the MySQL connection
$mysqli->close();
?>

Replace 'your_username', 'your_password', 'mydatabase', and 'imported_data.csv' with your actual MySQL credentials and CSV file path.

This script connects to the MySQL database, reads the CSV file row by row, escapes the data to prevent SQL injection, and inserts it into the MySQL table. Make sure to adapt the table structure and CSV file format to match your specific data.

Before running this script, make sure the MySQL PHP extension (mysqli) is enabled on your server. Also, ensure that the CSV file is correctly formatted and accessible from the script.