✖

All TalkersCode Topics

Follow TalkersCode On Social Media

✖

devloprr.com - A Social Media Network for developers Join Now ➔

Export MySQL Data To Excel Using PHP And HTML

Last Updated : Jul 1, 2023

IN - PHP MySQL HTML | Written & Updated By - Amruta

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.

Export MySQL Data To Excel Using PHP And HTML

CHECK OUT THIS TUTORIAL LIVE DEMO →

To Export MySQL Data To Excel It Takes Only Three Steps:-

  1. Make a HTML file and define markup
  2. Make a PHP file to export mysql data to excel
  3. 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.

CHECK OUT THIS TUTORIAL LIVE DEMO →