Skip to main content

Import CSV data into MySQL using PHP

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.
Labels:

Comments

Post a Comment

Popular posts from this blog

WordPress custom menu page, fetch data from custom MySQL table and export to CSV

 To create a custom menu page in WordPress, retrieve custom table data from MySQL, and display it with the ability to export to CSV/Excel, you can follow these steps: 1. Create a custom table in your WordPress database to store your data. You can use the $wpdb global variable to interact with custom tables in WordPress. Here's an example of creating a custom table: <?php global $wpdb ; $table_name = $wpdb -> prefix . 'custom_data' ; $sql = "CREATE TABLE IF NOT EXISTS $table_name ( id INT(11) NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, phone VARCHAR(20) NOT NULL, PRIMARY KEY (id) ) $charset_collate ;" ; require_once (ABSPATH . 'wp-admin/includes/upgrade.php' ); dbDelta( $sql ); ?> 2. Add the following code to your theme's functions.php file or create a custom plugin file to define the custom menu page: <?php // Add menu page function custom_menu_page () {
Post a Comment
Read more

WordPress migrations need an overhaul. Here’s why.

 WordPress migration is the bare necessity of running an active website. All WordPress customers need to deal with the aggravations with migrating their site beginning with one web host onto the next web host. It is known by the web society that WordPress migration is a overwhelming undertaking. This is clear with the by and large wide number of instructional exercises and articles concerning it. Even more importantly, the expenses incurred in this system are a wide sum. In the 21st century, we would look for our prerequisites to be fulfilled intuitively for a comprehensive customer endeavour. For the particular strategies to stay reasonable to this day and age, it is fundamental for the required virtual processes to be quick, i.e. they ought to be simple for the customer. WordPress has profitable strength of 14 years on the web. Even so, after this time, migration must be done manually. This is genuinely tiresome. You will be responsible for content creation an
Post a Comment
Read more

How to compare any changed data of HTML table among different rows

 In a HTML table compare every column data of every row. if any column data has changed value with respect to previous row then highlight that data.  We can achieve this using JavaScript. Here's a basic example of how you could implement this functionality: This code compares each cell of each row with the corresponding cell of the previous row. If the content of a cell is different from the content of the corresponding cell in the previous row, it adds a changed class to highlight the change. You can customise the appearance of the changed cells by modifying the CSS class .changed.
Post a Comment
Read more