Sunday, June 3, 2018

Import CSV to Database Using PHP and Mysqli

Today we will go through the steps to import CSV (comma separated values) to database in PHP using Mysqli. We will be importing some employees data to our employees table.

Import CSV to Database Using PHP and Mysqli
Lets start with our config.php which will hold all the database configuration constants, index.php which contains the actual code to import the data and fetch data from employees table, style.css to apply styles.

config.php

<?php
define("DB_HOST", "DATABASE HOST");  // Your database host
define("DB_NAME", "DATABASE NAME");  // Your database name
define("DB_USER", "USERNAME");  // Username for database 
define("DB_PASSWORD", "PASSWORD"); // Password for database
?>

index.php

<?php
include("config.php");
$dbcon = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die(mysqli_connect_error());
?>
<!DOCTYPE html>
<html>
    <head>
        <title>Import CSV to Database using PHP and Mysqli</title>
        <meta content="text/html; charset=UTF-8" http-equiv="Content-Type"/>
        <link rel="stylesheet" href="css/style.css" />
    </head>
    <body>
        <div class="main-container">
            <div class="section">
                <form name="csv-import" method="POST" enctype="multipart/form-data">
                    <div class="form-row">
                        <div class="col-4">
                            <input type="file" name="csv-file" class="form-control" />
                        </div>
                        <div class="col-4">
                            <input type="checkbox" name="first_row_header" id="first_row_header" />
                            <label for="first_row_header">First Row is Header</label>
                        </div>
                        <div class="col-4 text-right">
                            <button type="submit" class="btn-green">Import</button>
                        </div>
                    </div>
                </form>
            </div>
            <div class="section">
                <?php
                // Check if file was uploaded & there were no errors
                if ($_FILES && $_FILES['csv-file']['error'] == 0) {
                    $extension = pathinfo($_FILES['csv-file']['name'],PATHINFO_EXTENSION);
                    // Check if extension is csv then proceed to import
                    if($extension == 'csv'){
                        // Open file for reading
                        $file = fopen($_FILES['csv-file']['tmp_name'], 'r');
                        // Loop through all rows of file and insert them to database table
                        while (!feof($file)) {
                            // Get current row as recordset
                            $row = fgetcsv($file);
                            if (!empty($row)) {
                                $data = [];
                                $data['fullname'] = htmlentities($row[0]);
                                $data['email'] = htmlentities($row[1]);
                                $data['designation'] = htmlentities($row[2]);
                                $data['gender'] = htmlentities($row[3]);
                                $records[] = $data;
                                mysqli_query($dbcon,"INSERT INTO employees (".implode(",",array_keys($data)).") VALUES ('".implode("','",array_values($data))."')");
                            }
                        }
                    }else{?>
                        <div class="alert alert-red">Uploaded file was not a csv file. Please upload a csv file</div>
                    <?php

                    }
                }
                ?>
                <table class="table">
                    <thead>
                        <tr>
                            <th>Name</th>
                            <th>Email</th>
                            <th>Gender</th>
                            <th>Designation</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        $query = mysqli_query($dbcon, "SELECT * FROM employees");
                        // Loop through all records added to database table and display data
                        while ($record = mysqli_fetch_assoc($query)) {
                            ?>
                            <tr>
                                <td><?php echo $record['fullname']; ?></td>
                                <td><?php echo $record['email']; ?></td>
                                <td><?php echo $record['designation']; ?></td>
                                <td><?php echo $record['gender']; ?></td>
                            </tr>
                            <?php
                        }
                        ?>
                    </tbody>
                </table>
            </div>
        </div>
    </body>
</html>

style.css

*{
    box-sizing: border-box;
}
html,body{
    margin: 0px;
}
body{
    background: #f0f0f0;
    font: normal normal 14px Open Sans,Verdana, Arial;
}
.main-container {
    max-width: 1024px;
    margin: 0px auto;
}
.section:first-child{
    padding: 15px;
    background: #fff;
}
.form-row:before {
    content: "";
    display: table;
}
.form-row {
    margin-left: -10px;
    margin-right: -10px;
    margin-bottom: 10px;
}

.col-4{
    width: 33.33%;
    float: left;
    padding: 0px 10px;
}
.alert-red {
    background: #e65442;
    border: 1px solid #cc3a28;
    width: 100%;
    padding: 10px;
    margin-bottom: 10px;
    color: #fff;
    transition: opacity 0.5s;
}
.btn-green {
    background: #336611;
    border: 1px solid #225500;
    color: #fff;
    display: inline-block;
    padding: 5px 10px;
    cursor: pointer;
    float: right;
}
.table, .table > thead > tr > th,
.table > thead > tr > td,
.table > tbody > tr > th,
.table > tbody > tr > td{
   border: 1px solid #dddddd;
}
.table {
    border: 1px solid #dddddd;
    width: 100%;
    border-collapse: collapse;
    margin: 5px 0px;
    background: #fff;
}
.table td {
    padding: 5px 10px;
}
.table th {
    text-align: left;
    padding: 10px;
}