CSV also known as (Comma Separated Values) is a widely popular file format to store plain text data for offline uses. Mostly, CSV files format is used to import and export the data for moving data between different programs.
For the data management portion, the import and export feature is quite helpful. The database may be uploaded to and several pieces of data can be inserted using the Import feature. Bulk data may be quickly and easily imported into the database by using the Import function. The table data list may be downloaded by the user and saved in a file for offline usage using the export capability. The Export function allows you to download numerous records in a file format.
This article will walk you through the intricate process, empowering you with the knowledge to seamlessly manage your data. I understand the importance of hands-on learning, so get ready to delve into the world of PHP, MySQL, and CSV manipulation.
Steps to Use PHP and MySQL to Import and Export CSV Files
Step #1: Create Database Table
In the database, a table must be created in order to hold the member’s data. In the MySQL database, the following SQL creates a members table with a few fundamental columns.
CREATE TABLE members (
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,
phone VARCHAR(15) COLLATE utf8_unicode_ci NOT NULL,
created DATETIME NOT NULL,
modified DATETIME NOT NULL,
status ENUM('Active', 'Inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;The following functionality will be included in the sample import and export script.
- Obtain user information from the database.
- Use PHP to import data from a CSV file into a database.
- From a database, export data to CSV using PHP.
Step #2: Create the dbconfig.php file for database configuration
<?php
// Database configuration
const DB_HOST = "localhost";
const DB_USERNAME = "root";
const DB_PASSWORD = "";
const DB_NAME = "DemoTutorials";
// Create database connection
$db = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);
// Check connection
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}
//echo "Connected successfully";
?>The database connection is made using the dbconfig.php file. Enter your MySQL database credentials for the database host ($dbHost), user ($dbUsername), password ($dbPassword), and name ($dbName).
Step #3: Create index.php Main File for Uploading and Downloading CSV Files
index.php: This is the main page where users can upload and view CSV data.
Main code:
<?php
// Include the database configuration file
require_once 'dbconfig.php';
// Handle status messages
if (!empty($_GET['status'])) {
switch ($_GET['status']) {
case 'succ':
$statusType = 'alert-success';
$statusMsg = 'Members data imported successfully.';
break;
case 'err':
$statusType = 'alert-danger';
$statusMsg = 'An error occurred. Please try again.';
break;
case 'invalid_file':
$statusType = 'alert-danger';
$statusMsg = 'Please upload a valid CSV file.';
break;
default:
$statusType = '';
$statusMsg = '';
}
}
?>
<!DOCTYPE html>
<html lang="en-US">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Import and Export CSV using PHP and MySQL</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
</head>
<body>
<?php if (!empty($statusMsg)) { ?>
<div class="col-xs-12">
<div class="alert <?= $statusType ?>"><?= $statusMsg ?></div>
</div>
<?php } ?>
<div class="row">
<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>
<a href="exportcsvfile.php" class="btn btn-primary"><i class="exp"></i> Export</a>
</div>
</div>
<div class="col-md-12" id="importFrm" style="display: none;">
<form action="importcsvfile.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>
<table class="table table-striped table-bordered">
<thead class="thead-dark">
<tr>
<th>#ID</th>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
<th>Status</th>
</tr>
</thead>
<tbody>
<?php
$result = $db->query("SELECT * FROM users ORDER BY id DESC");
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= $row['name'] ?></td>
<td><?= $row['email'] ?></td>
<td><?= $row['phone'] ?></td>
<td><?= $row['status'] ?></td>
</tr>
<?php } } else { ?>
<tr><td colspan="5">No records 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>
</body>
</html>Step #4: Create a importcsvfile.php to Create a Database using CSV Data
importcsvfile.php: This script handles the importing of CSV data into the database.
Copy the following and paste it into command:
<?php
require_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];
$phone = $line[2];
$status = $line[3];
$prevQuery = "SELECT id FROM users WHERE email = '".$line[1]."'";
$prevResult = $db->query($prevQuery);
if ($prevResult->num_rows > 0) {
$db->query("UPDATE users SET name = '".$name."', phone = '".$phone."', status = '".$status."', modified = NOW() WHERE email = '".$email."'");
} else {
$db->query("INSERT INTO users (name, email, phone, created, modified, status) VALUES ('".$name."', '".$email."', '".$phone."', NOW(), NOW(), '".$status."')");
}
}
fclose($csvFile);
$qstring = '?status=succ';
} else {
$qstring = '?status=err';
}
} else {
$qstring = '?status=invalid_file';
}
}
header("Location: index.php".$qstring);
?>Step #5: Create exportcsvfile.php file to export data to a CSV format
exportcsvfile.php: This script handles exporting data from the database to a CSV file for download.
<?php
require_once 'dbconfig.php';
$filename = "users_" . date('Y-m-d') . ".csv";
$delimiter = ",";
$f = fopen('php://memory', 'w');
$fields = array('ID', 'Name', 'Email', 'Phone', 'Created', 'Status');
fputcsv($f, $fields, $delimiter);
$result = $db->query("SELECT * FROM users ORDER BY id DESC");
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$lineData = array($row['id'], $row['name'], $row['email'], $row['phone'], $row['created'], $row['status']);
fputcsv($f, $lineData, $delimiter);
}
}
fseek($f, 0);
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '";');
fpassthru($f);
exit();
?>Using PHP and MySQL, the exportcsvfile.php file manages the data export procedure.
- Obtain the database’s records.
- Using the PHP fopen() function, create and open a file in the writing-only mode.
- Using the PHP fputcsv() function, set header columns, format as CSV, then write it to the opened file.
- Data from the database should be output, formatted as CSV, and written to a file.
- Force the browser to download data as a file in CSV format.
Common Practices for Optimal Performance
To ensure your CSV import and export processes are seamless and efficient, I’ll recommend these best practices to keep in mind
- Optimize your MySQL database for quick data manipulation.
- Implement data validation and sanitization to prevent security vulnerabilities.
- Utilize indexing on columns that are frequently used in search and filter operations.
- Regularly maintain and optimize your codebase for optimal performance.
Conclusion
Congratulations! You’ve learned how to import and export CSV files using PHP and MySQL. By following the detailed steps outlined in this guide, you now possess the expertise to seamlessly manage your data interchange needs. Remember, practice makes perfect, so keep honing your skills and exploring new ways to leverage PHP and MySQL for your web development projects.
Remember, mastering PHP and MySQL for CSV file handling opens up a world of possibilities for your web development projects. By following the comprehensive insights and hands-on steps provided in this guide, you’re equipped to streamline your data management processes and elevate your development prowess.
Keep coding, keep innovating, and unlock the true potential of dynamic web applications!
FAQs on Import and Export CSV Files
1. What is the significance of using CSV files in web development?
CSV files, or Comma-Separated Values files, play a crucial role in web development as they offer a simplified format for storing and exchanging tabular data between different systems. This format’s versatility and ease of use make it ideal for various applications, such as data import, export, and synchronization.
2. Can I implement the CSV import and export processes in an automated manner?
Automation is highly recommended for efficient data management. By integrating scheduled tasks or incorporating these processes into your web application’s backend, you can ensure that data synchronization and manipulation occur seamlessly, reducing manual intervention and saving time.
3. Is it possible to export specific data from a MySQL database to a CSV file?
To export specific data, you can customize your MySQL query to retrieve only the desired information. By tailoring your query to your needs, you can ensure that the exported CSV file contains precisely the data you’re looking to manage or share.
