CSV File Import Export In MySQL Table Using Php

In this post, we will discuss how to import and export mysql data in csv file format. CSV stands for “comma separated values”. Each line of this file contains data records and it can be one or more fields. This is usefull when we want to add huge data into mysql database instead of adding data one by one.
Php has provided two functions to make this task very simpler.
1) fgetcsv
2) fputcsv
Lets see how this functions can complete our task in below tutorial.

Database – Create one mysql table

Use following sql query to make sql table.

--
-- Table structure for table `posts`
--

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) NOT NULL,
  `post_name` text NOT NULL,
  `description` text NOT NULL,
  `status` text NOT NULL,
  `date` date NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `posts`
--

INSERT INTO `posts` (`id`, `post_name`, `description`, `status`, `date`, `created`) VALUES
(1, 'Post 1', 'Post 1 description bla bla...', 'publish', '2018-07-11', '2018-07-22 15:33:10'),
(2, 'Post 2', 'Post 2 description bla bla...', 'publish', '2018-07-13', '2018-07-22 15:33:54'),
(3, 'Post 3', 'Post 3 description bla bla...', 'draft', '2018-07-15', '2018-07-22 15:33:54');

Csv Import/Export – index.php

<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = 'posts';

// Create connection
$con = mysqli_connect($servername, $username, $password, $db);

// Check connection
if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}

$msg = 0;
if (isset($_POST['import'])) {
    $fileName = $_FILES["file"]["tmp_name"];
    if ($_FILES["file"]["size"] > 0) {
        $file = fopen($fileName, "r");
        $i = 0;
        while (($column = fgetcsv($file)) !== FALSE) {
            if ($i > 0) {
                if (!empty($column[0])) {
                    $insertdate = date("Y-m-d", strtotime(str_replace('/', '-', $column[3])));
                    $sql = "INSERT into posts (post_name,description,status,date) 
                   values ('" . $column[0] . "','" . $column[1] . "','" . $column[2] . "','" . $insertdate . "')";
                    $result = mysqli_query($con, $sql);
                    if (isset($result)) {
                        $msg++;
                    }
                }
            }
            $i++;
        }
    }
}
if (isset($_POST['export'])) {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=data.csv');
    $output = fopen("php://output", "w");
    fputcsv($output, array('ID', 'Post Name', 'Description', 'Status', 'Date', 'Entry Time'));
    $query = "SELECT * from posts ORDER BY id DESC";
    $result = mysqli_query($con, $query);
    while ($row = mysqli_fetch_assoc($result)) {
        fputcsv($output, $row);
    }
    fclose($output);
    exit();
}
?>
<!DOCTYPE html>
<html>
    <head>
        <link href="style.css" rel="stylesheet" type="text/css"/>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
        <script src="jquery.js" type="text/javascript"></script>
    </head>
    <body>
        <div class="csv_section">
            <div class="export_section">
                <a href="sample.csv">DOWNLOAD SAMPLE</a>
            </div>
            <div class="import_section">
                <form class="form-horizontal" action="" method="post" name="uploadCSV" enctype="multipart/form-data">
                    <div class="input-row" style="margin-top: 8px;">
                        <label class="col-md-4 control-label">Choose CSV File</label> <input
                            type="file" name="file" id="file" accept=".csv">
                        <button type="submit" id="submit" name="export" class="btn-submit">EXPORT CSV</button>
                        <button type="submit" id="submit" name="import" class="btn-submit">IMPORT CSV</button>
                    </div>
                    <div id="response"></div>
                </form>
            </div>
        </div>
        <?php
        if ($msg > 0) {
            ?>
            <div class="msg">CSV data us imported successfully.</div>
            <?php
        }
        ?>
        <div class = "show_records">
            <?php
            $sql = "SELECT * from posts ORDER BY id DESC";
            $records = mysqli_query($con, $sql);
            $rowcount = mysqli_num_rows($records);
            if ($rowcount > 0) {
                ?>
                <h2 class="cl">All Imported List <span style="float:right;">Total : <?php echo $rowcount; ?></span></h2>
                <table id='joblisttable' style="float:left;">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Post Name</th>
                            <th>Description</th>
                            <th>Status</th>
                            <th>Date</th>
                            <th>Entry Time</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        while ($row = mysqli_fetch_object($records)) {
                            ?>
                            <tr class="line-content">
                                <td><?php echo $row->id; ?></td>
                                <td><?php echo $row->post_name; ?></td>
                                <td><?php echo $row->description; ?></td>
                                <td><?php echo $row->status; ?></td>
                                <td><?php echo date('Y-m-d H:i:s', strtotime($row->date)); ?></td>
                                <td><?php echo date('Y-m-d H:i:s', strtotime($row->created)); ?></td>
                            </tr>
                            <?php
                        }
                        ?>
                    </tbody>
                </table>
                <ul class="pagin"></ul>
            <?php } ?>
        </div>
    </body>
</html>

Pagination – jquery.js

jQuery(document).ready(function ($) {
    //Pagination
    var pageSize = 10;
    var pageCount = $(".line-content").length / pageSize;
    for (var i = 0; i < pageCount; i++) {
        $(".pagin").append('<li><a href="#">' + (i + 1) + '</a></li> ');
    }
    $(".pagin li").first().find("a").addClass("current")
    showPage = function (page) {
        $(".line-content").hide();
        $(".line-content").each(function (n) {
            if (n >= pageSize * (page - 1) && n < pageSize * page)
                $(this).show();
        });
    }
    showPage(1);
    $(".pagin li a").click(function () {
        $(".pagin li a").removeClass("current");
        $(this).addClass("current");
        showPage(parseInt($(this).text()))
    });
});

Some styling is required – style.css

.csv_section {
    margin-bottom: 15px;
    width: 99%;
    float: left;
    border: 3px solid #428bca;
    margin-top: 20px;
    background: white;
    box-sizing: border-box;
}
.export_section {
    width: 25%;
    float: left;
    padding: 20px;
    box-sizing: border-box;
    background: #428bca;
}
.export_section a {
    width: 100%;
    float: left;
    padding: 15px;
    background: white;
    border: 0;
    font-size: 19px;
    color: #428bca;
    font-weight: 800;
    cursor:pointer;
    box-sizing: border-box;
    text-align: center;
    text-decoration: none;
}
.import_section {
    width: 75%;
    float: left;
    padding: 20px;
    box-sizing: border-box;
    background: white;
}
.import_section label {
    font-size: 19px;
}
.import_section input#file {
    padding: 10px;
}
.import_section button[type='submit'] {
    border: 2px solid #428bca;
    background: white;
    font-size: 16px;
    padding: 5px 15px;
    color: #428bca;
    font-weight: 800;
    float: right;
    cursor:pointer;
    margin-left: 5px;
}

/* show records css */

table#joblisttable {
    float: left;
    width: 99%;
    border-spacing: 0px !important;
    border-collapse: unset;
}
#joblisttable thead {
    background: #428bca;
    color: white;
    font-size: 16px;
    border: 1px solid #428bca;
}
table#joblisttable th {
    height: 28px;
    text-align: left;
    padding: 3px 5px;
}
table#joblisttable td {
    background: white;
    padding: 7px;
    border-bottom: 1px solid #f1f1f1;
}
h2.cl {
    width: 99%;
    float: left;
}
.current {
    color: white;
    background: #428bca !important;
}
.current:hover, .current:focus{
    color:white;
}
.pagin li {
    display: inline-block;
}
ul.pagin {
    width: 100%;
    float: left;
    margin-top: 15px;
    box-sizing: border-box;
    padding: 0;
    text-align: center;
}
ul.pagin li a {
    padding: 5px 10px;
    float: left;
    background: white;
    text-decoration: none;
}
.show_records{
    width:100%;float:left;
}
.msg {
    padding: 5px 15px;
    border: 1px solid #ddd;
    box-sizing: border-box;
    background: white;
    color: blue;
    text-align: center;
    width: fit-content;
    margin: 0 auto;
    overflow: hidden;
    margin-top: 15px;
}

Conclusion :

As you can see, to csv file operations like import/export in mysql using php is not a difficult task we just need to use some cool functions provided by php itself. In this tutorial, we have also covered one small pagination on data using jquery. In future posts, we will see this jquery or php pagination in more details. So stay connected with my blog to get useful stuff in simple manner, and i will be more happy to get some feedback from you in comment section, and also share this post if you like and if it is useful for others. Thanks. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *