Sometimes it may be necessary to export from MySQL to Excel for local use. A MySQL database or tables can be exported using the phpMyAdmin PHP client in various file formats such as CSV, XML, SQL, Excel, etc. It is also possible to export MySQL data with a PHP script instead of exporting the data manually. If a web application needs to store data in Excel format from a MySQL database, it is preferable to use a PHP script for this task. This tutorial shows you how to export MySQL data with a PHP script.

Requirements

Perform the following tasks before exporting MySQL data You must open the MySQL client in the terminal or the phpMyAdmin client in your browser to perform the following tasks. After executing the following SQL commands, a table with five data records is created in the database.

A. Creating a MySQLdatabase

To create a database with a company name, execute the following SQL command

B. Create Table.

To create a table of item names, execute the following SQL command.

C. Insert the data shown in Table.

INFORMATION INDICATIONS (Name, Type, Brand, Price) VALUES
(Samsung A40s, Mobile, Sansung 300.00),
(Samsung 4523 40 inches, TV, Samsung 250.00).00),
(Walton, Fridge, Walton large fridge,400.00),
(LG 15 Monitor, Monitor, LG 100.00),
(DELL gaming laptop, laptop, Dell,450.00) ;

When you open the array of elements of the phpMyAdmin client, the content of the array of elements looks like this.

Exporting MySQL data to an Excel file

Here you need to create a PHP file called read_en_export.php to create the following script that reads the data in the table of elements and creates an Excel file with the contents of the table of elements. The script explains the database connection object to extract data from the database table. Next, the query is defined as a selection request to read all records of the table of items stored in a variable called $items. This variable is used to display the contents of the table as a table and to create an Excel file with the contents of the table based on the user’s actions. When a user clicks a button that is then created by an HTML document to export the table data to Excel, the isset() function of this script is executed. To create an Excel file, the filename is defined with the extension xsl. The required header information is transmitted with the header() function. The variable $heading is then used to define the column header in an Excel file. The core values of the variable $items are used as header values. Another implode() function is used to write records from the table of elements to the Excel file.

Read_and_execute.php

request($query);
$Line item = array() ;

// Save the records of table
while ( $row = $result->fetch_assoc() ) {
$items[] = $row ;
}
// Make sure the Export button is pressed or not
if(isset($_POST[export])). {
//Define the name of the file with the current date
$fileName = itemdata-.date(‘d-m-Y’)…xls ;

// Specify header information for Excel data export
header (‘Content-Type: application/vnd.ms-excel’);
header (‘Content-Disposition: attachment; filename=’.$fileName) ;

// Set the variable false for field
$Heading = false ;

add the data from the /MySQL table to the Excel fileif(! empty($items)) {foreach($items as $items) {if(!$heading) {echo implode(t, array_keys($items))) . n ;$heading = true ;}echo implode(t, array_values($items)) . n ;}}}output() ;}

?>

Create another PHP file called index.php with the following code to display the table data in your browser using the Export to Excel button. Here at the beginning of the script the file Read_en_export.php is included, with which you can connect to the database, read the table data and create an Excel file with the table data when the user presses the button. The data in the table is tabulated using Bootstrap and jQuery.

index.php

/Add the script to read and export MySQL data to Excel
include(read_and_export.php)

?>

href=https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css>
href=https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css>

 

 

 

style=’color:green’>Element array information

 

 

 

Top of Form

value=Export to excel class=btn btn-success>Export to Excel

Bottom of Form

 

 

 

 

ID

 

Name

 

Come on in.

 

Brand

 

Prices

 

 

 

 

$

 

 

 

 

Net revenue

Here the files read_en_export.php and index.php are stored on var/www/html/php/export. To start index.php, run the following URL in your browser.

http://localhost/php/export

You will get the following result after executing the above URL in your browser

When you click the Export to Excel button, the following dialog box will appear. Select the Save File radio button and click OK to create and download an Excel file containing the table data.

When you click on the downloaded file to open it, the following dialog box will appear to select the required options before the file is displayed. The data in each column are separated by one or more tables when creating the Excel file. Therefore, the bookmark option is selected here before the file is created.

The contents of the Excel file look like this.

Conclusion

You can create an Excel file from a MySQL table by dividing the column values by a certain separator. The separator can be a comma(,) tab(t), semicolon(;), space( ) or other specific characters. In this course material, the tabulator is used as a separator. Before you can view the contents of an Excel file, you must select the appropriate icon in the text input dialog box. If single or double quotes are used in the content of a MySQL data table, the data is not stored in an Excel file. I hope this guide has helped you learn how to export data from MySQL tables to an Excel file.

php not connecting to mysql ubuntu,php mysql connection problem linux,php mariadb example,php 5.4 connect to mysql,php securely connect to mysql database,remote mysql connection php,php export to excel xlsx,php export array to excel,pdo export to excel,export to excel in php w3schools,source code php export to excel,exporting data from mysql to excel using php,mysql export to csv command line,mysql query output to file,mysql export to csv with headers,mysql workbench export to csv,mysql output csv stdout,mysql into outfile access denied,download data from database in php,export html table to csv php,php fputcsv,mysql export table to csv,mysql into outfile,mysqldump to csv,php mysql export to excel,php export to excel with formatting,how to connect mysql database with php in linux,php code to retrieve data from mysql database and display,php mysql program examples,php and mysql,php connect to mysql database,how to connect mysql database in php in ubuntu,php mariadb

You May Also Like

Warmest Socks For Men & Women

Do you feel comfortable and warm wearing socks in winter? Whatever you…

10+ Best Free Sports Streaming Sites in 2020

Almost all providers of cable and satellite television offer sports programmes, but…

Doogee X95 Android 10 Stock ROM [Flash File]

Good news for Doogee X95 users, and it comes in the form…

Best wireless microphone under 100- Review (2020)

The best aspect of technological development is the wealth of different products…