Import CSV to MySQL and Export from MySQL to CSV in PHP

Import CSV to MySQL and Export from MySQL to CSV in PHP

CSV is a simple text file that can be read by Notepad, supported by many software and applications. It is very helpful for exporting/importing data, backup or save database to an external CSV file. More than that, if you have a lot of data and you need to insert them one by one, it may take a lot of time, so structure them in a CSV file and import them in one click. By following this tutorial, you’ll know how to import CSV to MySQL and export from MySQL to CSV in PHP.

Download source code

1. Demonstration

2. Structure

/css : contain the CSS files
/js : contain the JavaScript files
/images : contain all images

2. Database

Create a new database named “csv_import_export”, and import the SQL script in the main folder after downloading the source file, or copy/paste the script bellow:

--
-- Table structure for table `members`
--

CREATE TABLE IF NOT EXISTS `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `full_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `mobile` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

--
-- Dumping data for table `members`
--

INSERT INTO `members` (`id`, `full_name`, `email`, `mobile`, `age`) VALUES
(1, 'Ali Aboussebaba', 'bwd@gmail.com', '0622334455', 27),
(2, 'Paul Xavier', 'paul@domain.com', '0655443321', 22),
(3, 'Rachid Kabbour', 'rachid@domain.com', '0667543298', 25),
(4, 'Christien Davinci', 'christien@domain.com', '0643213489', 32),
(5, 'Vladimir Richo', 'vladimir@domain.com', '0678563412', 40),
(6, 'Omar Alami', 'omar@domain.com', '0698654367', 28);

3. Files

index.php

This is the main file, it will be displayed on the first view:

<?php
// including the config file
include('config.php');
$pdo = connect();
// select all members
$sql = 'SELECT * FROM members ORDER BY id ASC';
$query = $pdo->prepare($sql);
$query->execute();
$list = $query->fetchAll();
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Import CSV to MySQL and Export from MySQL to CSV in PHP</title>
<link rel="stylesheet" href="css/style.css" />
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/script.js"></script>
</head>

<body>
    <div class="container">
        <div class="header">
            <img src="images/BeWebDeveloper.png" alt="BeWebDeveloper" />
        </div><!-- header -->
        <h1 class="main_title">Import CSV to MySQL and Export from MySQL to CSV in PHP</h1>
        <div class="content">
            <fieldset class="field_container align_right">
                <legend> <img src="images/arrow.gif"> Operations</legend>
                <span class="import" onclick="show_popup('popup_upload')">Import CSV to MySQL</span>
                <a href="export.php" class="export">Export from MySQL to CSV</a>
            </fieldset>
            <fieldset class="field_container">
                <legend> <img src="images/arrow.gif"> Members list </legend>
                <div id="list_container">
                    <table class="table_list" cellspacing="2" cellpadding="0">
                        <tr class="bg_h">
                            <th>Full name</th>
                            <th>Email</th>
                            <th>Mobile</th>
                            <th>Age</th>
                        </tr>
                        <?php
                            $bg = 'bg_1';
                            foreach ($list as $rs) {
                                ?>
                                <tr class="<?php echo $bg; ?>">
                                    <td><?php echo $rs['full_name']; ?></td>
                                    <td><?php echo $rs['email']; ?></td>
                                    <td><?php echo $rs['mobile']; ?></td>
                                    <td><?php echo $rs['age']; ?></td>
                                </tr>
                                <?php
                                if ($bg == 'bg_1') {
                                    $bg = 'bg_2';
                                } else {
                                    $bg = 'bg_1';
                                }
                            }
                        ?>
                    </table>
                </div><!-- list_container -->
            </fieldset>
        </div><!-- content -->
        <div class="footer">
            Powered by <a href="http://www.bewebdeveloper.com/">bewebdeveloper.com</a>
        </div><!-- footer -->
    </div><!-- container -->

    <!-- The popup for upload a csv file -->
    <div id="popup_upload">
        <div class="form_upload">
            <span class="close" onclick="close_popup('popup_upload')">x</span>
            <h2>Upload CSV file</h2>
            <form action="import.php" method="post" enctype="multipart/form-data">
                <input type="file" name="csv_file" id="csv_file" class="file_input">
                <input type="submit" value="Upload file" id="upload_btn">
            </form>
        </div>
    </div>
</body>
</html>

config.php

This is the config file. It contains a single function for connection to the database using PDO.

<?php
// PDO connect *********
function connect() {
    $host = 'localhost';
    $db_name = 'csv_import_export';
    $db_user = 'root';
    $db_password = '';
    return new PDO('mysql:host='.$host.';dbname='.$db_name, $db_user, $db_password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
}
?>

export.php

This is the script for exporting the list of members, it will get the list of all members from the database and export them.

<?php
// including the config file
include('config.php');
$pdo = connect();

// set headers to force download on csv format
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=members.csv');

// we initialize the output with the headers
$output = "id,full_name,email,mobile,age\n";
// select all members
$sql = 'SELECT * FROM members ORDER BY id ASC';
$query = $pdo->prepare($sql);
$query->execute();
$list = $query->fetchAll();
foreach ($list as $rs) {
    // add new row
    $output .= $rs['id'].",".$rs['full_name'].",".$rs['email'].",".$rs['mobile'].",".$rs['age']."\n";
}
// export the output
echo $output;
exit;
?>

import.php

This is the script for exporting the list of members, it will get the list of all members from the database and export them.

<?php 
// including the config file
include('config.php');
$pdo = connect();

$csv_file =  $_FILES['csv_file']['tmp_name'];
if (is_file($csv_file)) {
    $input = fopen($csv_file, 'a+');
    // if the csv file contain the table header leave this line
    $row = fgetcsv($input, 1024, ','); // here you got the header
    while ($row = fgetcsv($input, 1024, ',')) {
        // insert into the database
        $sql = 'INSERT INTO members(full_name, email, mobile, age) VALUES(:full_name, :email, :mobile, :age)';
        $query = $pdo->prepare($sql);
        $query->bindParam(':full_name', $row[1], PDO::PARAM_STR);
        $query->bindParam(':email', $row[2], PDO::PARAM_STR);
        $query->bindParam(':mobile', $row[3], PDO::PARAM_STR);
        $query->bindParam(':age', $row[4], PDO::PARAM_INT);
        $query->execute();
    }
}

// redirect to the index page
header('location: index.php');
?>

script.js

This is the JavaScript file

// show_popup : show the popup
function show_popup(id) {
    // show the popup
    $('#'+id).show();
}

// close_popup : close the popup
function close_popup(id) {
    // hide the popup
    $('#'+id).hide();
}

style.css

This is the CSS file, it contain the design styles

* {
    margin: 0;
    padding: 0;
}
body {
    padding: 10px;
    background: #eaeaea;
    text-align: center;
    font-family: arial;
    font-size: 12px;
    color: #333333;
}
.container {
    width: 1000px;
    height: auto;
    background: #ffffff;
    border: 1px solid #cccccc;
    border-radius: 10px;
    margin: auto;
    text-align: left;
}
.header {
    padding: 10px;
}
.main_title {
    background: #cccccc;
    color: #ffffff;
    padding: 10px;
    font-size: 20px;
    line-height: 20px;
}
.content {
    padding: 10px;
    min-height: 100px;
}
.footer {
    padding: 10px;
    text-align: right;
}
.footer a {
    color: #999999;
    text-decoration: none;
}
.footer a:hover {
    text-decoration: underline;
}
/* Operations ************/
.align_right {
    text-align: right;
}
.import {
    display: inline-block;
    background: url('../images/import.png') no-repeat left;
    background-size: auto 100%;
    line-height: 20px;
    padding-left: 25px;
    color: #666666;
    font-size: 14px;
    font-weight: bold;
    text-decoration: none;
    margin-right: 20px;
    cursor: pointer;
}
.export {
    display: inline-block;
    background: url('../images/export.png') no-repeat left;
    background-size: auto 100%;
    line-height: 20px;
    padding-left: 25px;
    color: #666666;
    font-size: 14px;
    font-weight: bold;
    text-decoration: none;
}
.import:hover, .export:hover {
    text-decoration: underline;
    color: #333333;
}
.field_container {
    padding: 10px;
    margin: 0 0 10px 0;
    border: 1px solid #cccccc;
    border-radius: 10px;
}
.field_container legend {
    padding: 0 5px 0 5px;
    font-size: 14px;
    font-weight: bold;
}
/* members list ************/
.table_list {
    width: 100%;
    border: 0;
}
.table_list td, .table_list th {
    padding: 2px;
}
.delete_m {
    color: #666666;
    text-decoration: none;
    font-weight: bold;
}
.delete_m:hover {
    color: #999999;
}
.delete_m img {
    height: 12px;
}
.bg_h {
    background: #8d8b8b;
    color: #ffffff;
    text-align: center;
}
.bg_1 {
    background: #e3e2e2;
    text-align: center;
}
.bg_2 {
    background: #c0bfbf;
    text-align: center;
}
#list_container {
    text-align: center;
}
/* popup --------------------------*/
#popup_upload {
    position: fixed;
    width: 100%;
    height: 100%;
    top: 0;
    left: 0;
    background: rgba(0, 0 ,0, 0.7);
    z-index: 99;
    text-align: center;
    display: none;
    overflow: auto;
}
.form_upload {
    width: 300px;
    height: 140px;
    border: 1px solid #999999;
    border-radius: 10px;
    background: #ffffff;
    color: #666666;
    margin: auto;
    margin-top: 160px;
    padding: 10px;
    text-align: left;
    position: relative;
}
.form_upload h2 {
    border-bottom: 1px solid #999999;
    padding: 0 0 5px 0;
    margin: 0 0 20px 0;
}
.file_input {
    width: 97%;
    background: #eaeaea;
    border: 1px solid #999999;
    border-radius: 5px;
    color: #333333;
    padding: 1%;
    margin: 0 0 20px 0;
}
#upload_btn {
    background: #cccccc;
    color: #333333;
    border: 1px solid #999999;
    border-radius: 10px;
    float: right;
    line-height: 20px;
    font-size: 14px;
    font-weight: bold;
    font-family: arial;
    display: block;
    padding: 5px;
    cursor: pointer;
}
#upload_btn:hover {
    background: #eaeaea;
}
.close {
    position: absolute;
    display: block;
    right: 10px;
    cursor: pointer;
    font-size: 20px;
    line-height: 16px;
    width: 18px;
    height: 18px;
    border: 1px solid #cccccc;
    border-radius: 5px;
    background: #F0F0F0;
    text-align: center;
    font-weight: bold;
}
.close:hover {
    background: #cccccc;
}
CSV, Export CSV, Import CSV, MySQL, PHP
comments powered by Disqus

Social Profiles

bewebdeveloper on facebook bewebdeveloper on twitter bewebdeveloper on google plus bewebdeveloper on vk bewebdeveloper on youtube bewebdeveloper on tumblr bewebdeveloper rss

Subscribe to our Newsletter

Facebook

Twitter

Google+

HTML Tutorials
CSS Tutorials

Advertising