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
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.
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); }
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.
This file will have the following steps to export data into MySQL.
fopen()
function.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;
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.
fopen()
function..fgetcsv()
function.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.
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
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
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
This Laravel tutorial helps to understand table Relationships using Elequonte ORM. We'll explore laravel table Relationships usage and best practices… Read More
We'll explore different join methods of Laravel eloquent with examples. The join helps to fetch the data from multiple database… Read More
in this Laravel tutorial, We'll explore valet, which is a development environment for macOS minimalists. It's a lightweight Laravel development… Read More
I'll go through how to use soft delete in Laravel 10 in this post. The soft deletes are a method… Read More