Export php data to csv

Export Data to CSV File using PHP and MySQL

CSV (comma-separated values) is the most popular file format to store data in plain text for offline uses. Generally, a CSV file is used to import and export data for moving data between programs. Import and export data is the most used feature in the web application, and CSV file format is the best choice for that.

The import and export, both features are implemented easily with PHP in the web application. In the previous tutorial, we have discussed about Import CSV File Data into MySQL database using PHP. In this tutorial, we will show you how to export data from MySQL database to CSV file using PHP.

To demonstrate Export to CSV functionality, we will build an example script that will export member’s data from the MySQL database and save it in a CSV file using PHP.

• Fetch data from the database using PHP and MySQL.
• Create a CSV file in PHP and save data in it.
• Export MySQL data and download it in a CSV file using PHP.

Create Database Table

To store the data, a table needs to be created in the database. The following SQL creates a members table with some basic fields in the MySQL database.

CREATE TABLE `members` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL, `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `gender` enum('Male','Female') COLLATE utf8_unicode_ci NOT NULL, `country` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `created` datetime NOT NULL, `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Active | 0=Inactive', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Configuration (dbConfig.php)

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.

// Database configuration 
$dbHost = "localhost"; 
$dbUsername = "root"; 
$dbPassword = "root"; 
$dbName = "codexworld"; 

// Create database connection 
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 

// Check connection 
if ($db->connect_error) < 
die("Connection failed: " . $db->connect_error); 
>

Data List from MySQL Database (index.php)

Initially, all the member’s data is fetched from the database and listed in a tabular format.

• An EXPORT button is placed at the top of the data list.
• By clicking the Export button, the data is exported from the database and allow to download on a local drive as a CSV file.

 div class="col-md-12 head"> div class="float-right"> a href="exportData.php" class="btn btn-success">i class="dwn"> i> Export a> div> div> table class="table table-striped table-bordered"> thead class="thead-dark"> tr> th>#ID th> th>Name th> th>Email th> th>Gender th> th>Country th> th>Created th> th>Status th> tr> thead> tbody>  // Fetch records from database 
$result = $db->query("SELECT * FROM members ORDER BY id ASC"); 
if($result->num_rows > 0) < 
while($row = $result->fetch_assoc()) < 
?> tr> td> echo $row['id']; ?> td> td> echo $row['first_name'].' '.$row['last_name']; ?> td> td> echo $row['email']; ?> td> td> echo $row['gender']; ?> td> td> echo $row['country']; ?> td> td> echo $row['created']; ?> td> td> echo ($row['status'] == 1)?'Active':'Inactive'; ?> td> tr>  > >else ?> tr>td colspan="7">No member(s) found. td> tr>  > ?> tbody> table>

For this example script, the Bootstrap library is used to style the HTML table and buttons. So, include the Bootstrap CSS library and custom stylesheet file (if any).

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

Export Data to CSV File using PHP (exportData.php)

The exportData.php file handles the data export and CSV file download process using PHP and MySQL.

• Retrieve data from the MySQL database.
• Create a file pointer using fopen() function.
• Specify the header columns and put data into the CSV file.
• Output each row of the data, format line as CSV, and write to file pointer.
• Set Content-Type and Content-Disposition to force the browser to download the file rather than display it.

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

// Fetch records from database 
$query = $db->query("SELECT * FROM members ORDER BY id ASC"); 

if($query->num_rows > 0) < 
$delimiter = ","; 
$filename = "members-data_" . date('Y-m-d') . ".csv"; 
 
// Create a file pointer 
$f = fopen('php://memory', 'w'); 
 
// Set column headers 
$fields = array('ID', 'FIRST NAME', 'LAST NAME', 'EMAIL', 'GENDER', 'COUNTRY', 'CREATED', 'STATUS'); 
fputcsv($f, $fields, $delimiter); 
 
// Output each row of the data, format line as csv and write to file pointer 
while($row = $query->fetch_assoc()) < 
$status = ($row['status'] == 1)?'Active':'Inactive'; 
$lineData = array($row['id'], $row['first_name'], $row['last_name'], $row['email'], $row['gender'], $row['country'], $row['created'], $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 color: #007700">. $filename . '";'); 
 
//output all remaining data on a file pointer 
fpassthru($f); 
> 
exit; 

?>

Conclusion

This example code provides an easy way to export data to CSV file in PHP. You can enhance or customize the functionality of this Export to CSV script as per your needs. If you want to implement this export functionality in client-side script, use JavaScript to do it – Export HTML Table Data to CSV using JavaScript

Are you want to get implementation help, or modify or enhance the functionality of this script? Click Here to Submit Service Request

If you have any questions about this script, submit it to our QA community — Ask Question

Источник

How to Export CSV Files Using PHP and MySQL

Export data to CSV file from MySQL database using PHP; In this tutorial, we will learn how to export data in CSV file from MySQL database using PHP.

Sometimes we need to export data into MySQL database. The best solution to exporting data into a MySQL database using the PHP script in In CSV file format.

This tutorial helps you an easy way to exporting data into the MySQL database table in CSV file using a PHP script.

Export CSV Files Using PHP and MySQL

• Step 1 – Create a Database Connection File
• Step 2 – Export MySQL Data to CSV file PHP Code

Step 1 – Create a Database Connection File

In this step, you will create a file name db.php and update the below code into your file.

The below code is used to create a MySQL database connection in PHP. When we insert form data into MySQL database, there we will include this file:

Step 2 – Export MySQL Data to CSV file PHP Code

In this step, you need to create one file name export.php and update the below code into your file.

The code below is used to get data from the MySQL database and export it to a CSV file or download it. The sample file name is users-sample.csv.

 $users = array(); if (mysqli_num_rows($result) > 0) < while ($row = mysqli_fetch_assoc($result)) < $users[] = $row; >> header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename=users-sample.csv'); $output = fopen('php://output', 'w'); fputcsv($output, array('No', 'Name', 'Mobile', 'Email')); if (count($users) > 0) < foreach ($users as $row) < fputcsv($output, $row); >> ?>

Conclusion

In this tutorial, we have learned how to export data into the MySQL database in CSV using PHP code.

This is a very basic and easy example of exporting data into the MySQL database in CSV file using PHP code.

If you have any questions or thoughts to share, use the comment form below to reach us.

Источник

Export Data to CSV File with PHP and MySQL

Comma Separated Values (CSV) is a popular file format to store tabular data into plain text file. The each line of the CSV file is a data record and each record consists of one or more fields, separated by commas. The data stored in CSV file can easily be imported to and exported from application.

If you’re thinking about to implement data export to CSV with PHP, then you’re here at right place. As in our previous tutorial you have learned how to export data to excel with PHP. In this tutorial you will learn how to Export data to CSV File using PHP and MySQL.

We will cover this tutorial in easy steps to implement live example to export data to csv using PHP and MySQL

So let’s start implementing data export to CSV using PHP and MySQL. Before we begin, take a look on files structure for this example.

Step1: Create MySQL Database Table

As we will implement live example to export data to CSV file, so first we will create table developers to store developer data.

CREATE TABLE `developers` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `skills` varchar(255) NOT NULL, `address` varchar(255) NOT NULL, `gender` varchar(255) NOT NULL, `designation` varchar(255) NOT NULL, `age` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will insert some records into developers table to export.

INSERT INTO `developers` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`) VALUES (1, 'Smith', 'Java', 'Newyork', 'Male', 'Software Engineer', 34), (2, 'David', 'PHP', 'London', 'Male', 'Web Developer', 28), (3, 'Rhodes', 'jQuery', 'New Jersy', 'Male', 'Web Developer', 30), (4, 'Sara', 'JavaScript', 'Delhi', 'Female', 'Web Developer', 25), (5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Female', 'Programmer', 35), (6, 'Steve', 'Angular', 'London', 'Male', 'Web Developer', 28), (7, 'Cook', 'MySQL', 'Paris', 'Male', 'Web Developer', 26), (8, 'Root', 'HTML', 'Paris', 'Male', 'Web Developer', 28), (9, 'William', 'jQuery', 'Sydney', 'Male', 'Web Developer', 23), (10, 'Nathan', 'PHP', 'London', 'Male', 'Web Developer', 28), (11, 'Shri', 'PHP', 'Delhi', 'Male', 'Web Developer', 38), (12, 'Jay', 'PHP', 'Delhi, India', 'Male', 'Web Developer', 30);

Step2: Get Records from MySQL Database

First we will get records from MySQL database table developers and store records into an array to display developers records and also to export into CSV file.

Step5: Display Records into HTML Table

In index.php file, we will display developers records into Bootstrap HTML table. We will also create Form with submit type data export button.

 
 
 Export to CSV 
 
 
      ?>
 
Name
 
Gender
 
Age
 
Designation
 
Address
 
 
  
 
 

Step3: Implement Data Export to CSV with PHP

Now in export_data.php file, we will implement data export functionality to export data to CSV file using developers array. The data export started when export Form submitted and exported data downloaded into CSV file.

 fputcsv($FH, array_values($developer)); > fclose($FH); > exit; > ?>

You may also like:

• User Management System with PHP & MySQL
• Datatables Add Edit Delete with Ajax, PHP & MySQL
• Build Helpdesk System with jQuery, PHP & MySQL
• Build Online Voting System with PHP & MySQL
• School Management System with PHP & MySQL
• DataTables Add Edit Delete with CodeIgniter
• Create RESTful API using CodeIgniter
• Build Reusable Captcha Script with PHP
• Product Search Filtering using Ajax, PHP & MySQL
• Image Upload and Crop in Modal with jQuery, PHP & MySQL
• Build Push Notification System with PHP & MySQL
• Project Management System with PHP and MySQL
• Hospital Management System with PHP & MySQL
• Build Newsletter System with PHP and MySQL
• Skeleton Screen Loading Effect with Ajax and PHP
• Build Discussion Forum with PHP and MySQL
• Customer Relationship Management (CRM) System with PHP & MySQL
• Online Exam System with PHP & MySQL
• Expense Management System with PHP & MySQL

You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download

Recommendations

Источник