Import and Export include is extremely helpful for the information the board segment. The Import usefulness enables the client to transfer and embed numerous information in the database. Utilizing the Import include, the mass information can be embedded in the database on a solitary snap. The fare usefulness enables the client to download the table information rundown and spare in a document for disconnected use. Utilizing the Export include, various records can be downloaded in a document position.
For the most part, the CSV document position is utilized to import and fare information in the web application. CSV (comma-isolated qualities) record stores the information in plain content configuration and moves information between projects. The import and fare usefulness can be effectively actualized with a CSV document utilizing PHP and MySQL. Import CSV record information in database/Export information to CSV document both can be incorporated with PHP and MySQL. In this instructional exercise, we will tell you the best way to import and fare CSV record information in database utilizing PHP and MySQL.
Make Database Table
To store the part’s information, a table should be made in the database. The following SQL query makes an users table with some essential fields in the MySQL database.
|
2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE `users` ( `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; |
In the example import and export script, the following functionality will be implemented.
- Fetch the user’s data from the database.
- Import CSV file data into database using PHP.
- Export data to CSV using PHP from Database.
Create Database Configuration File (dbconfig.php)
|
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<?php // Database configuration $dbHost = "localhost"; $dbUsername = "root"; $dbPassword = ""; $dbName = "Tutorialswebsite"; // Create database connection $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); // Check connection if ($db->connect_error) { die("Connection failed: " . $db->connect_error); } ?> |
The dbconfig.php is used to connect the database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.
Create main file to Upload CSV Fileand Download (index.php)
|
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 |
<?php // Load the database configuration file include_once 'dbconfig.php'; // Get status message if(!empty($_GET['status'])){ switch($_GET['status']){ case 'succ': $statusType = 'alert-success'; $statusMsg = 'Members data has been imported successfully.'; break; case 'err': $statusType = 'alert-danger'; $statusMsg = 'Some problem 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 class="no-js" lang="en-US" prefix="og: http://ogp.me/ns#"> <head> <title>How to Import and Export CSV File using PHP and MySql</title> <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" > </head> <body> <!-- Display status message --> <?php if(!empty($statusMsg)){ ?> <div class="col-xs-12"> <div class="alert <?php echo $statusType; ?>"><?php echo $statusMsg; ?></div> </div> <?php } ?> <div class="row"> <!-- Import & Export 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> <a href="exportcsvfile.php" class="btn btn-primary"><i class="exp"></i> Export</a> </div> </div> <!-- CSV file upload form --> <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> <!-- 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>Phone</th> <th>Status</th> </tr> </thead> <tbody> <?php // Get member rows $result = $db->query("SELECT * FROM users 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['phone']; ?></td> <td><?php echo $row['status']; ?></td> </tr> <?php } }else{ ?> <tr><td colspan="5">No Record found...</td></tr> <?php } ?> </tbody> </table> </div> <!-- Show/hide CSV upload form --> <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> |
Create ( importcsvfile.php ) to Import CSV Data into Database
|
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
<?php // Load the database configuration file include_once 'dbconfig.php'; if(isset($_POST['importSubmit'])){ // Allowed mime types $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'); // Validate whether selected file is a CSV file if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvMimes)){ // If the file is uploaded if(is_uploaded_file($_FILES['file']['tmp_name'])){ // Open uploaded CSV file with read-only mode $csvFile = fopen($_FILES['file']['tmp_name'], 'r'); // Skip the first line fgetcsv($csvFile); // Parse data from CSV file line by line while(($line = fgetcsv($csvFile)) !== FALSE){ // Get row data $name = $line[0]; $email = $line[1]; $phone = $line[2]; $status = $line[3]; // Check whether member already exists in the database with the same email $prevQuery = "SELECT id FROM users WHERE email = '".$line[1]."'"; $prevResult = $db->query($prevQuery); if($prevResult->num_rows > 0){ // Update member data in the database $db->query("UPDATE users SET name = '".$name."', phone = '".$phone."', status = '".$status."', modified = NOW() WHERE email = '".$email."'"); }else{ // Insert member data in the database $db->query("INSERT INTO users (name, email, phone, created, modified, status) VALUES ('".$name."', '".$email."', '".$phone."', NOW(), NOW(), '".$status."')"); } } // Close opened CSV file fclose($csvFile); $qstring = '?status=succ'; }else{ $qstring = '?status=err'; } }else{ $qstring = '?status=invalid_file'; } } // Redirect to the listing page header("Location: index.php".$qstring); ?> |
Create ( exportcsvfile.php ) to Export Data into CSV File
|
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
<?php // Load the database configuration file include_once 'dbconfig.php'; $filename = "users_" . date('Y-m-d') . ".csv"; $delimiter = ","; // Create a file pointer $f = fopen('php://memory', 'w'); // Set column headers $fields = array('ID', 'Name', 'Email', 'Phone', 'Created', 'Status'); fputcsv($f, $fields, $delimiter); // Get records from the database $result = $db->query("SELECT * FROM users ORDER BY id DESC"); if($result->num_rows > 0){ // Output each row of the data, format line as csv and write to file pointer while($row = $result->fetch_assoc()){ $lineData = array($row['id'], $row['name'], $row['email'], $row['phone'], $row['created'], $row['status']); fputcsv($f, $lineData, $delimiter); } } // Move back to beginning of file fseek($f, 0); // Set headers to download file rather than displayed header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="' . $filename . '";'); // Output all remaining data on a file pointer fpassthru($f); // Exit from file exit(); ?> |
The exportcsvfile.php file handles the data export process using PHP and MySQL.
- Fetch the records from the database.
- Create and open a file with writing-only mode using PHP fopen() function.
- Set header columns, format as CSV and write it to the opened file using PHP fputcsv() function.
- Output data from the database, format as CSV and write it to file.
- Force browser to download data as CSV format in a file.
Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request OR Chat Using Bottom Right Facebook Chat Box
Pradeep Maurya is the Professional Web Developer & Designer and the Founder of “Tutorials website”. He lives in Delhi and loves to be a self-dependent person. As an owner, he is trying his best to improve this platform day by day. His passion, dedication and quick decision making ability to stand apart from others. He’s an avid blogger and writes on the publications like Dzone, e27.co
