How to Import and Export CSV Files Using PHP and MySQL

by Shahroze Nawaz  November 8, 2016

PHP is widely used for building a wide range of products ranging from web apps to enterprise level applications. The key to efficient PHP code is to follow proper workflows and automate processes. The result is high quality and bug-free code.

In almost all PHP applications, data is stored, accessed and exchanged between various components of the app. To make sure that this exchange and access to data goes smoothly and without any issues, the development team must make sure that the databases and data dumps are in proper format.

how to import and export csv files using php and mysql

Import and export of data to and from databases is a common enough procedure in PHP development. Another important activity is the backup and transfer of databases.

In this article, I will explain how to save tables from CSV files to MySQL and vice versa. You need to signup at Cloudways to launch a server and PHPstack application. follow the below gif:

You can use promo code: PHP15 to get FREE Cloudways hosting credit of $15 on signup.

Create a Database in MySQL

The first step in this tutorial is the creation of a MySQL database. Since Cloudways provides the custom mysql manager in the platform which contains a database for app. you can create tables by running SQL queries. Create a table employeeinfo in database using the following SQL query.

This will create a new table employeeinfo in the database. I will use this table to insert data from the CSV file.

Create MySql Connection in PHP

For importing and exporting database in MySql will make a separate file config.php. Add the following code and replace the database credentials with yours. You can find your db credentials in Application Access details:

Related: How To Connect MySQL Database With PHP Websites

Import CSV to MySQL in PHP

After the database has been created, I next need an HTML file  that could upload CSV file. For this HTML file, I will use HTML File uploader in a simple bootstrap form.

Create a file and name it index.php . This is a simple form for uploading CSV file. This file will also show the results in a simple table on the same page. When the user submits the form,  all records will be saved in the database.

First, I will add Bootstrap CDN to index.php.

Next, in the body tag,  add the following HTML code for the Bootstrap form.

import form

You might notice that I have set an action to functions.php file. In the next step, I will create this file and add code to it. I have also include a method get_all_records() near the end of the file. This method fetches all the records from the database and display the records in the table on the index page.

Next up, I will create functions.php file and add the following code in it.

When the upload button is clicked, the temporary file name will be stored in memory and using the while loop the data is saved in $getData variable. Once the process has been completed, the data is sorted column wise and then finally inserted in the employeeinfo table.

Note that fgetcsv() parses lines from the open file, checking for CSV fields and fopen() opens a file or a URL. This code could be tested by importing a CSV file with test data.

Display the Saved Records

Once the CSV file has been imported, I will display the data through a simple function, get_all_records(), initialized in index.php. Copy this function to function.php.

In this really simple method, I simply selected all the records and displayed these records on the index page through the method. Whenever the user uploads a CSV file, the records will get saved in the table and then displayed on the index page.

Export MySQL to CSV With PHP

Exporting data from  MySQL database to a CSV file is similarly very easy. To demonstrate this, I will use the index.php that I created earlier.

Add the following code to the file.

After adding this HTML markup, the Export button will appear below the table. Now add the following condition in functions.php.

When the Export button is clicked, the headers Content-Type: text/csv with an attachement data.csv is sent.

Since php://output is a write-only stream that allows write access to the output buffer mechanism, I selected all data from table in the next line, and passed it to fputcsv() method. This method formats a line (passed as a fields array) as CSV and write it (terminated by a newline) to the specified file. Finally, the file with all the desired data is downloaded.

Finally, after integrating all the code, you will see the following final shape of application.

cloudways import excel

Conclusion

In this article, I discussed how you could export data from and to CSV files using PHP and MySQL. This is a simple example you can Add more complex logic and validations as per your requirements. If you wish to add to the discussion or would like to ask a question, leave a comment below.

Start Creating Web Apps on Managed Cloud Servers Now!

Easy Web App Deployment for Agencies, Developers and E-Commerce Industry

About Shahroze Nawaz

Shahroze is a PHP Community Manager at Cloudways - A Managed PHP Hosting Platform. He’s always in search of new frameworks and methods to implement them. Besides his coding life, he loves movies and playing soccer with friends. You can email him at shahroze.nawaz@cloudways.com

Stay Connected:

You Might Also Like...