Import CSV to MySQL phpMyAdmin – How to Import and Export CSV Files Using PHP and MySQL?

php import csv to mysql – There are many ways to read CSV files but in this Article, i will use 2 main functions to read CSV files fopen() and fgetcsv() Example with demo.

Import CSV to MySQL phpMyAdmin

Import CSV to MySQL phpMyAdmin. it reads the uploaded CSV file and parses the data.

step 1 : Create Database Table

created in the database

CREATE TABLE `players` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `mobile` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 `is_active` enum('Active','Inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

step 2 : Database Configuration

dbConfig.php

<?php

$dbHost     = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName     = "pakainfo_v1";

$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}

step 3 : CSV File Upload

index.php

<?php
// Load the database configuration file
include_once 'dbConfig.php';

// Get is_active message
if(!empty($_GET['is_active'])){
    switch($_GET['is_active']){
        case 'succ':
            $is_activeType = 'alert-success';
            $is_activeMsg = 'players data has been imported successfully.';
            break;
        case 'err':
            $is_activeType = 'alert-danger';
            $is_activeMsg = 'Some problem occurred, please try again.';
            break;
        case 'invalid_file':
            $is_activeType = 'alert-danger';
            $is_activeMsg = 'Please upload a valid CSV file.';
            break;
        default:
            $is_activeType = '';
            $is_activeMsg = '';
    }
}
?>

<!-- Display is_active message -->
<?php if(!empty($is_activeMsg)){ ?>
<div class="col-xs-12">
    <div class="alert <?php echo $is_activeType; ?>"><?php echo $is_activeMsg; ?></div>
</div>
<?php } ?>

<div class="row">
    <!-- Import link -->
    <div class="col-md-12 head">
        <div class="float-right">
            <a href="javascript:void(0);" class="btn btn-success" onclick="formToggle('importFrm');"><i class="plus"></i> Import</a>
        </div>
    </div>
    <!-- CSV file upload form -->
    <div class="col-md-12" id="importFrm" style="display: none;">
        <form action="importData.php" method="post" enctype="multipart/form-data">
            <input type="file" name="file" />
            <input type="submit" class="btn btn-primary" name="importSubmit" value="IMPORT">
        </form>
    </div>

    <!-- Data list table --> 
    <table class="table table-striped table-bordered">
        <thead class="thead-dark">
            <tr>
                <th>#ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>mobile</th>
                <th>is_active</th>
            </tr>
        </thead>
        <tbody>
        <?php
        // Get member rows
        $result = $db->query("SELECT * FROM players ORDER BY id DESC");
        if($result->num_rows > 0){
            while($row = $result->fetch_assoc()){
        ?>
            <tr>
                <td><?php echo $row['id']; ?></td>
                <td><?php echo $row['name']; ?></td>
                <td><?php echo $row['email']; ?></td>
                <td><?php echo $row['mobile']; ?></td>
                <td><?php echo $row['is_active']; ?></td>
            </tr>
        <?php } }else{ ?>
            <tr><td colspan="5">No member(s) found...</td></tr>
        <?php } ?>
        </tbody>
    </table>
</div>

<script>
function formToggle(ID){
    var element = document.getElementById(ID);
    if(element.style.display === "none"){
        element.style.display = "block";
    }else{
        element.style.display = "none";
    }
}
</script>

Php Import Csv To Mysql

Added Bootstrap library

<link rel="stylesheet" href="assets/bootstrap/bootstrap.min.css">
<link rel="stylesheet" href="assets/css/style.css">

Import CSV Data to Database

importData.php

<?php
include_once 'dbConfig.php';

if(isset($_POST['importSubmit'])){
    
    $csvMimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');
    
    if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvMimes)){
        

        if(is_uploaded_file($_FILES['file']['tmp_name'])){
            

            $csvFile = fopen($_FILES['file']['tmp_name'], 'r');
            

            fgetcsv($csvFile);
            

            while(($line = fgetcsv($csvFile)) !== FALSE){

                $name   = $line[0];
                $email  = $line[1];
                $mobile  = $line[2];
                $is_active = $line[3];
                
                $prevQuery = "SELECT id FROM players WHERE email = '".$line[1]."'";
                $prevResult = $db->query($prevQuery);
                
                if($prevResult->num_rows > 0){
                  
                    $db->query("UPDATE players SET name = '".$name."', mobile = '".$mobile."', is_active = '".$is_active."', updated_at = NOW() WHERE email = '".$email."'");
                }else{
                  
                    $db->query("INSERT INTO players (name, email, mobile, created, updated_at, is_active) VALUES ('".$name."', '".$email."', '".$mobile."', NOW(), NOW(), '".$is_active."')");
                }
            }
            
            fclose($csvFile);
            
            $quser_line = '?is_active=succ';
        }else{
            $quser_line = '?is_active=err';
        }
    }else{
        $quser_line = '?is_active=invalid_file';
    }
}

header("Location: index.php".$quser_line);

I hope you get an idea about Import CSV to MySQL phpMyAdmin.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.