Sometimes, you may need to export into Excel format from MySQL to use locally. The database or tables of the MySQL database can be exported into various file formats, such as CSV, XML, SQL, Excel, etc., by using the PHP client, phpMyAdmin. It is also possible to export MySQL data using PHP script instead of exporting the data manually. When a web application requires data to be stored in Excel format from the MySQL database, then it is best to use PHP script to perform this task. This tutorial shows you how to export MySQL data using PHP script.
Prerequisites
Complete the following tasks before exporting any MySQL data. You must open the MySQL client in the terminal or the phpMyAdmin client in the browser to perform the following tasks. A table with five records will be created in a database after executing the following SQL commands.
A. Create a MySQL database
Run the following SQL command to create a database named the company.
B. Create a table
Run the following SQL command to create a table named items.
`id` int(11) AUTO_INCREMENT PRIMARY KEY,
`name` varchar(100) NOT NULL,
`type` varchar(20) NOT NULL,
`brand` varchar(50) NOT NULL,
`price` double(5,2) NOT NULL
) ENGINE=InnoDB;
C. Insert data into the table
('Samsung A40s', 'Mobile','Sansung',300.00),
('Samsung 4523 40” inche', 'TV','Samsung',250.00),
('Walton Large Fridge', 'Fridge','Walton',400.00),
('LG 15” Monitor', 'Monitor','LG',100.00),
('DELL gaming Laptop', 'Laptop','Dell',450.00);
If you open the items table from the phpMyAdmin client, the contents of the items table will look like the following image.
Export MySQL Data to Excel file
Here, you must create a PHP file named read_and_export.php to create the following script, which reads records in the items table and creates an Excel file with the content of the items table. In the script, a database connection object is declared to retrieve data from the database table. Next, a select query is defined to read all records from the items table that are stored in a variable named $items. This variable is used to display the content of the table in tabular form and create an Excel file with table contents based on the user’s action. When the user clicks on a button that will later be created by the HTML document to export table data in Excel format, the isset() function of this script will return “true.” To create the Excel file, a filename is defined with the extension xsl. The required header information is passed using the header() function. Next, the $heading variable is used to set the header of the columns in the Excel file. The key values of the $items variable are used as the header values. Next, the implode() function is used to write the records of the items table into the Excel file.
read_and_export.php
query($query);
$items = array();
//Store table records into an array
while( $row = $result->fetch_assoc() ) {
$items[] = $row;
}
//Check the export button is pressed or not
if(isset($_POST["export"])) {
//Define the filename with current date
$fileName = "itemdata-".date('d-m-Y').".xls";
//Set header information to export data in excel format
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename='.$fileName);
//Set variable to false for heading
$heading = false;
//Add the MySQL table data to excel file
if(!empty($items)) {
foreach($items as $item) {
if(!$heading) {
echo implode("\t", array_keys($item)) . "\n";
$heading = true;
}
echo implode("\t", array_values($item)) . "\n";
}
}
exit();
}
?>
Create another PHP file named index.php with the following code to display the records in the items table in the browser with the button, Export to Excel. Here, the read_and_export.php file is included at the beginning of the script to make the database connection, read the table data, and create an Excel file with the table data when the user presses the button. The table data will be displayed in tabular format using bootstrap and jQuery.
index.php
//Add script to read MySQL data and export to excel
include("read_and_export.php");
?>
<!DOCTYPE html>
<html>
<head>
<title>Export MySQL Data to Excel using PHP</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet"
href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<link rel="stylesheet"
href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css">
<script
src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script
src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<center><br/><br/><h2
style='color:green'>Items Table Information</h2></center>
<div class="col-sm-12">
<div>
<form action="#" method="post">
<button type="submit" id="export" name="export"
value="Export to excel" class="btn btn-success">Export To Excel</button>
</form>
</div>
</div>
<br/>
<table id="" class="table table-striped table-bordered">
<tr>
<th>ID</th>
<th>Name</th>
<th>Type</th>
<th>Brand</th>
<th>Price</th>
</tr>
<tbody>
<?php foreach($items as $item) { ?>
<tr>
<td><?php echo $item ['id']; ?></td>
<td><?php echo $item ['name']; ?></td>
<td><?php echo $item ['type']; ?></td>
<td><?php echo $item ['brand']; ?></td>
<td>$<?php echo $item ['price']; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
</body>
</html>
Output
Here, both the read_and_export.php and the index.php files are stored in the location, var/www/html/php/export. Run the following URL in a browser to execute index.php.
http://localhost/php/export
You will get the following output after running the above URL in the browser.
The following dialog box will appear when you click the button, Export to Excel. Select the Save File radio button and click the OK button to create and download the Excel file with the table data.
When you click on the downloaded file to open it, the following dialog box will appear for you to select the necessary options before displaying the file. The data in every column are separated by tab(\t) at the time of creation of the Excel file. So, the tab option is selected here before generating the file.
The content of the Excel file will look like the following image.
Conclusion
An Excel file can be created from a MySQL table by separating column values with a specific separator. The separator can be a comma(,) a tab(\t), a semicolon(;), a space ( ), or other specific characters. In this tutorial, the tab is used as a separator. You must select the appropriate character in the Text Import dialog box before displaying the content of the Excel file. If any single or double quote is used in the content of the MySQL table data, then this data will not be stored in the Excel file. I hope that this tutorial has helped you to learn how to export MySQL table data into an Excel file.
