Export Data to Excel is a common feature of web applications to allow dynamic data export to Excel file to save locally for further use. If you’re a PHP developer and thinking to implement data export to excel, then you’re here at right place. You will learn here how to implement data export to excel using PHP & MySQL.
In our previous tutorial you have learned how to export data to excel in CodeIgniter, now in this tutorial we will explain how to export data to excel with PHP and MySQL.
We will cover this tutorial step by step to create live demo to implement data export to excel with PHP and MySQL. You can also download complete source code of live.
Also, read:
- Export Data to Excel with PhpSpreadsheet using CodeIgniter
- Export Data to CSV File with PHP and MySQL
- Export HTML Table Data to Excel, CSV and Text with jQuery, PHP and MySQL
So let’s start implementing data export to excel with PHP and MySQL. Before we begin, take a look on files structure for this example.
- index.php:
- export.php:
Step1: Create MySQL Database Table
As we will cover this tutorial with example to export data to Excel file, so first we will create MySQL database table developers
to store developer records.
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, `image` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
We will also insert few records to this table.
INSERT INTO `developers` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`, `image`) VALUES (1, 'Smith', 'Java', 'Newyork', 'Male', 'Software Engineer', 34, 'image_1.jpg'), (2, 'David', 'PHP', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'), (3, 'Rhodes', 'jQuery', 'New Jersy', 'Male', 'Web Developer', 30, 'image_2.jpg'), (4, 'Sara', 'JavaScript', 'Delhi', 'Female', 'Web Developer', 25, 'image_2.jpg'), (5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Female', 'Programmer', 35, 'image_2.jpg'), (6, 'Steve', 'Angular', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'), (7, 'Cook', 'MySQL', 'Paris', 'Male', 'Web Developer', 26, 'image_2.jpg'), (8, 'Root', 'HTML', 'Paris', 'Male', 'Web Developer', 28, 'image_2.jpg'), (9, 'William', 'jQuery', 'Sydney', 'Male', 'Web Developer', 23, 'image_2.jpg'), (10, 'Nathan', 'PHP', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'), (11, 'Shri', 'PHP', 'Delhi', 'Male', 'Web Developer', 38, 'image_2.jpg'), (12, 'Jay', 'PHP', 'Delhi, India', 'Male', 'Web Developer', 30, 'image_3.jpg');
Step2: Get Records from MySQL Database Table
In export.php
file, we will get developer records from MySQL database table developers
and store into an array.
include_once("db_connect.php"); $sqlQuery = "SELECT name, gender, age, skills, address, designation FROM developers LIMIT 10"; $resultSet = mysqli_query($conn, $sqlQuery) or die("database error:". mysqli_error($conn)); $developersData = array(); while( $developer = mysqli_fetch_assoc($resultSet) ) { $developersData[] = $developer; }
Step3: Display Records with Export to Excel Button
In index.php
file, we will display developer records from $developersData
array. We will also add data export button to export data.
<div class="container"> <div class="well-sm col-sm-12"> <div class="btn-group pull-right"> <form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="post"> <button type="submit" id="dataExport" name="dataExport" value="Export to excel" class="btn btn-info">Export To Excel</button> </form> </div> </div> <table id="" class="table table-striped table-bordered"> <tr> <th>Name</th> <th>Gender</th> <th>Age</th> <th>Skills</th> <th>Address</th> <th>Designation</th> </tr> <tbody> <?php foreach($developersData as $developer) { ?> <tr> <td><?php echo $developer ['name']; ?></td> <td><?php echo $developer ['gender']; ?></td> <td><?php echo $developer ['age']; ?></td> <td><?php echo $developer ['skills']; ?></td> <td><?php echo $developer ['address']; ?></td> <td><?php echo $developer ['designation']; ?></td> </tr> <?php } ?> </tbody> </table> </div>
Step4: Implement Data Export to Excel
Now we will implement export data to excel when export button clicked. We will use $developersData
array for data exported and saved into an xlsx file.
<?php if(isset($_POST["dataExport"])) { $fileName = "webdamn_export_".date('Ymd') . ".xls"; header("Content-Type: application/vnd.ms-excel"); header("Content-Disposition: attachment; filename=\"$fileName\""); $showColoumn = false; if(!empty($developersData)) { foreach($developersData as $developerInfo) { if(!$showColoumn) { echo implode("\t", array_keys($developerInfo)) . "\n"; $showColoumn = true; } echo implode("\t", array_values($developerInfo)) . "\n"; } } 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
Hello, first of all, good job and nice article,
I have just one little problem, if you can help me, thanks!
i look the tutorial (export from php mysql to excel) and adapte the code to my database table, and works fine, but when i put “where id_form =’”.$id_form.”‘”,
the select from works fine, but the export, give me a white and clear excel file, i try put the “id_form” in the index field but, still not working.
Any solution ? I dont change nothing in the code, just put the database connection for my website (not localhost), insert more data to export, and working, and the condition “where id = id” because, in the backoffice of the website i’m building, the client will select the content he wants, and export just that content.
may there be there error, try to debug. may be there any error. thanks!
hello
Nice article.
In my case, the output of the SQL query which changes everytime by making few selections is displayed on the page- how can I download the result as excel?