php import csv to mysql – Import CSV File Data into MySQL Database using PHP

php import csv to mysql: Import CSV File into MySQL using PHP.Create a Database in MySQL – Create MySql Connection in PHP – Import CSV to MySQL in PHP – Display the Saved Records – Export MySQL to CSV With PHP.

php import csv to mysql

List of the Steps to import CSV file into MySql database using PHP.

step 1: Create Database Table

CREATE TABLE `friends` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`email_address` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`mobile` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`join_at` 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

connect_db.php

<!--?php $dbHost = "localhost"; $dbUsername = "root"; $dbPassword = "root"; $dbName = "pakainfo_v1"; $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); if ($db->connect_error) {<br ?--> die("Connection failed: " . $db->connect_error);
}

step 3: CSV File Upload

index.php

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

 
<div class="col-xs-12">
<div class="alert <?php echo $displayErrMsgTp; ?>"></div>
</div>
 
<div class="row">
<div class="col-md-12 head">
<div class="float-right"><a class="btn btn-success"><i class="plus"></i> Import</a></div>
</div>
<div id="importFrm" class="col-md-12" style="display: none;"><form action="importData.php" enctype="multipart/form-data" method="post"><input name="file" type="file" />
<input class="btn btn-primary" name="importSubmit" type="submit" value="IMPORT" /></form></div>
if($result->num_rows > 0){while($row = $result->fetch_assoc()){?>
<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>Status</th>
</tr>
</thead>
<tbody><!--?php $result = $db->query("SELECT * FROM friends ORDER BY id DESC");<br ?-->
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<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>

step 4: Import CSV Data to Database

importData.php

<!--?php include_once 'connect_db.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(($rwdata = fgetcsv($csvFile)) !== FALSE){ $name = $rwdata[0]; $email_address = $rwdata[1]; $mobile = $rwdata[2]; $is_active = $rwdata[3]; $prevQuery = "SELECT id FROM friends WHERE email_address = '".$rwdata[1]."'"; $friendsRes = $db->query($prevQuery);</p>
<p>                if($friendsRes->num_rows > 0){<br ?--> $db->query("UPDATE friends SET name = '".$name."', mobile = '".$mobile."', is_active = '".$is_active."', updated_at = NOW() WHERE email_address = '".$email_address."'");
}else{
$db->query("INSERT INTO friends (name, email_address, mobile, join_at, updated_at, is_active) VALUES ('".$name."', '".$email_address."', '".$mobile."', NOW(), NOW(), '".$is_active."')");
}
}

fclose($csvFile);

$qstring = '?is_active=succ';
}else{
$qstring = '?is_active=err';
}
}else{
$qstring = '?is_active=invalid_file';
}
}

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

Don’t Miss: upload csv file in php

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