How do i import and export csv using php and mysql?

Import and Export feature is very useful for the data management section. The Import functionality allows the user to upload and insert multiple data in the database. Using the Import feature, the bulk data can be inserted in the database on a single click. The export functionality allows the user to download the table data list and save in a file for offline use. Using the Export feature, multiple records can be downloaded in a file format.

Mostly, the CSV file format is used to import and export data in the web application. CSV [comma-separated values] file stores the data in plain text format and helps to move data between programs. The import and export functionality can be easily implemented with a CSV file using PHP and MySQL. Import CSV file data in database / Export data to CSV file both can be integrated with PHP and MySQL. In this tutorial, we will show you how to import and export CSV file data in database using PHP and MySQL.

In the example import and export script, the following functionality will be implemented.

• Fetch the member’s data from the database and listed in the webpage.
• Import CSV file data into MySQL database using PHP.
• Export data to CSV using PHP and MySQL.

Create Database Table

To store the member’s data, a table needs to be created in the database. The following SQL creates a members table with some basic fields in the MySQL database.

CREATE TABLE `members` [
 `id` int[11] NOT NULL AUTO_INCREMENT,
 `name` varchar[50] COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar[50] COLLATE utf8_unicode_ci NOT NULL,
 `phone` varchar[15] COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 `status` enum['Active','Inactive'] COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
 PRIMARY KEY [`id`]
] ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CSV File Format

Based on the database table structure, the CSV file should have these fields – Name, Email, Phone, and Status. To import CSV file data in the database, the CSV file format will similar to the following screen.

When the data export to CSV file, the downloaded format will look like the following screen.

Database Configuration [dbConfig.php]

The dbConfig.php is used to connect the database. Specify the database host [$dbHost], username [$dbUsername], password [$dbPassword], and name [$dbName] as per your MySQL database credentials.

    
                
                
                
                
            
        
            No member[s] found...
        
        
    




function formToggle[ID]{
    var element = document.getElementById[ID];
    if[element.style.display === "none"]{
        element.style.display = "block";
    }else{
        element.style.display = "none";
    }
}

This example code uses Bootstrap 4 library to styling the HTML Table, Form, and Links. You can omit it to include if you don’t want to use Bootstrap structure. Otherwise, include the Bootstrap library file and custom stylesheet file [if any].

Import CSV Data to Database [importData.php]

The importData.php file handles the CSV file upload and data import process with PHP and MySQL.

• Validate the submitted file whether a valid CSV file.
• Check the CSV file upload status using PHP is_uploaded_file[] function.
• Open the CSV file using PHP fopen[] function.
• Parse data from the CSV file using PHP fgetcsv[] function.
• Insert or Update data into the database based on the member’s email.