Php

How To Import/Export CSV Data Using MySQLi and PHP 7

This tutorial help to import the CSV data into MySQL and export data from MySQL to csv file. The CSV file is used to import and export data for moving/exchange data information between web applications. You can use rest API or web application to import/export CSV data.

The web application data is stored, accessed and exchanged between various components using CSV format. The CSV(comma-separated values) most popular file format to store data in plain text.

You can also check other tutorials of Export Data with PHP

How To Import and Export CSV data in PHP

In this tutorial, I will show you how to import and export data from MySQL database to/from CSV file using PHP. I will not create HTML form to upload CSV file, I’ll provide a PHP script that will load and save CSV file data into PHP.

Create MySQL Database Connection

Let’s create an employees Table in MySQL Database, that ll use for import and export CSV data into the database.
The following SQL creates an employees table with some basic fields in the MySQL database. The employees table holds the employee information which needs to be exported.

The following SQL creates an employees table with some basic fields in the MySQL database. The employees table holds the employee information which needs to be exported.

CREATE TABLE `employees` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `age` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Now, I’ll create a connection.php file and add the below code to this file. The below code helps to connect to the MySQL databases, Also use to select the database using PHP and MySQL.

//DB details
 $dbHost     = 'localhost';
 $dbUsername = 'root';
 $dbPassword = '*';
 $dbName     = 'phpflow';//Create connection and select DB
 $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
//Create connection and select DB
 $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

if($db->connect_error){

     die("Unable to connect database: " . $db->connect_error);

 }

Export to CSV File using PHP

Let’s export data from the database using PHP and MySQL. We’ll create export_csv.php file and write code to fetch data from employees table and save it into the emp.csv file. We’ll store all employee exported data into employee.csv file.

Related Post

This file will have the following steps to export data into MySQL.

  • Fetch data from the employees table.
  • Open the file using fopen() function.
  • Define the header columns and put them into the CSV file.
  • Read one by one each row of the data, and write to the file pointer.
  • We’ll force the browser to download the CSV file using Content-Type and Content-Disposition.

Let’s add the below code into export_csv.php file.

query("SELECT * FROM employees ORDER BY id DESC");

if($query->num_rows > 0){
    $delimiter = ",";
    $filename = "employee.csv";

    //create a file pointer for write
    $f = fopen('php://memory', 'w');

    //set column headers
    $fields = array('ID', 'Name', 'Age', 'Salary');
    fputcsv($f, $fields, $delimiter);

    //write to file
    while($row = $query->fetch_assoc()){
        $lineData = array($row['id'], $row['name'], $row['age'], $row['salary']);
        fputcsv($f, $lineData, $delimiter);
    }
    fseek($f, 0);

    //set headers to download file
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '";');

    fpassthru($f);
}
exit;

Import CSV data into the MySQL Database

Let’s import emp.csv file data into the MySQL database. I will use the employees table to insert data from the CSV file. Create import_csv.php file and added below code.

I am assuming you have file upload HTML form and want to script to store CSV file data into MySQL database.

This file will have the following steps to export data into MySQL.

  • Create a read file pointer using fopen() function..
  • Parse line by line CSV file data using fgetcsv() function.
  • Inserted CSV data into the employees table.

The CSV data is stored in memory using fgetcsv() method. The while loop is used to save data in $emps variable. Once the process has been completed, the data is sorted column-wise and inserted in the employees table.

Conclusion

In this tutorial, We have discussed how to import/export data from and to CSV file using PHP 7 and MySQLi. You can extend the import/export functionality as per your requirements/need. You can also create export and import data functionality easily using JavaScript –Export HTML Table Data to CSV using JavaScript

Recent Posts

Configure and Retrieve S3 Information Using Laravel PHP-AWS-SDK

This tutorial helps integrate a PHP SDK with Laravel. We'll install aws-php-sdk into laravel application and access all aws services… Read More

2 months ago

What is the Purpose of php_eol in PHP?

in this quick PHP tutorial, We'll discuss php_eol with examples. PHP_EOL is a predefined constant in PHP and represents an… Read More

8 months ago

Laravel Table Relationship Methods With Example

This Laravel tutorial helps to understand table Relationships using Elequonte ORM. We'll explore laravel table Relationships usage and best practices… Read More

8 months ago

Exploring the Power of Laravel Eloquent Join?

We'll explore different join methods of Laravel eloquent with examples. The join helps to fetch the data from multiple database… Read More

9 months ago

Quick and Easy Installation of Laravel Valet

in this Laravel tutorial, We'll explore valet, which is a development environment for macOS minimalists. It's a lightweight Laravel development… Read More

9 months ago

What is Laravel Soft Delete and How Does it Work?

I'll go through how to use soft delete in Laravel 10 in this post. The soft deletes are a method… Read More

9 months ago

Categories