Do you want to be a good Web Developer?

Enter Your Email To Get Our Exclusive Tutorials Right In Your Email

Join TalkersCode Community And Recieve Exclusive Tutorials
On Web Development In Your Email

Export MySQL Data To Excel Using PHP And HTML

Friday, Jan 27, 2017

Tags:- PHP MySQL HTML


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.


See Demo
Export MySQL Data To Excel Using PHP And HTML


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.

Join Us With Our 21000+ Subscribers And Get Our Latest Tutorials Update Via Email