Export MySQL Data To Excel Using PHP And HTML
Last Updated : Jul 1, 2023
In this tutorial we will show you how to export mysql data to excel using PHP and HTML. Sometime there is a need to display your database data in excel spreadsheet then you can choose this method to
export mysql data to excel.
You may also like import excel file data to mysql using PHP.
CHECK OUT THIS TUTORIAL LIVE DEMO →
To Export MySQL Data To Excel It Takes Only Three Steps:-
- Make a HTML file and define markup
- Make a PHP file to export mysql data to excel
- Make a CSS file and define styling
Step 1. Make a HTML file and define markup
We make a HTML file and save it with a name export.html
<html> <head> <link href="export_style.css" type="text/css" rel="stylesheet"/> </head> <body> <div id="wrapper"> <div id="table_div"> <form method="post" action="export_data.php"> <table align=center cellspacing=5> <tr> <td><input type="text" name="name[]" placeholder="Enter Name"></td> <td><input type="text" name="age[]" placeholder="Enter Age"></td> <td><input type="text" name="country[]" placeholder="Enter Country"></td> </tr> <tr> <td><input type="text" name="name[]" placeholder="Enter Name"></td> <td><input type="text" name="age[]" placeholder="Enter Age"></td> <td><input type="text" name="country[]" placeholder="Enter Country"></td> </tr> <tr> <td><input type="text" name="name[]" placeholder="Enter Name"></td> <td><input type="text" name="age[]" placeholder="Enter Age"></td> <td><input type="text" name="country[]" placeholder="Enter Country"></td> </tr> </table> <input type="submit" name="export" value="EXPORT TO EXCEL"> </form> </div> </div> </body> </html>
In this step we create a form to enter some details which will be going to store in database in 'export_data.php' file.
We create three text rows to enter data you can create as many as you want.You may also like sort mysql table using PHP.
Step 2. Make a PHP file to export mysql data to excel
We make a PHP file and save it with a name export_data.php
// Database Structure CREATE TABLE 'employee_table' ( 'name' text NOT NULL, 'age' text NOT NULL, 'country' text NOT NULL, ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 <?php if(isset($_POST['export'])) { @header("Content-Disposition: attachment; filename=mysql_to_excel.csv"); $host="localhost"; $username="root"; $password=""; $databasename="sample"; $connect=mysql_connect($host,$username,$password); $db=mysql_select_db($databasename); $name=$_POST['name']; $age=$_POST['age']; $country=$_POST['country']; for($i=0;$i<count($name);$i++) { $name_val=$name[$i]; $age_val=$age[$i]; $country_val=$country[$i]; mysql_query("insert into employee_table values('$name_val','$age_val','$country_val')"); } $select = mysql_query("SELECT * FROM employee_table"); while($row=mysql_fetch_array($select)) { $data.=$row['name'].","; $data.=$row['age'].","; $data.=$row['country']."\n"; } echo $data; exit(); } ?>
In this step we create a database table 'employee_table' to store employee details entered by user then we write header which will put all the data to mysql_to_excel.csv file and download automatically to users
computer.
We get all the values entered by user and insert all the values to database using for loop this is a extra
functionality we give to user otherwise you can simply export existing mysql data to excel by simply using select query and displaying data all the data which will be displayed in if section will be exported to excel so make sure to display only relevant data.
You may also like delete multiple mysql rows using PHP.
Step 3. Make a CSS file and define styling
We make a CSS file and save it with a name export_style.css
body { margin:0 auto; padding:0px; text-align:center; width:100%; font-family: "Myriad Pro","Helvetica Neue",Helvetica,Arial,Sans-Serif; background-color:#EC7063; } #wrapper { margin:0 auto; padding:0px; text-align:center; width:995px; } #wrapper h1 { margin-top:50px; font-size:45px; color:#922B21; } #wrapper h1 a { color:#922B21; font-size:18px; } #table_div input[type="text"] { width:120px; height:35px; padding-left:5px; border-radius:3px; border:none; } #table_div input[type="submit"] { width:375px; height:35px; border-radius:3px; border:1px solid #922B21; background:none; color:#922B21; }
That's all, this is how to export mysql data to excel using PHP and HTML. You can customize this code further as per your requirement. And please feel free to give comments on this tutorial.
I hope this tutorial on export mysql to excel helps you and the steps and method mentioned above are easy to follow and implement.