Extract Data in Excel From MySQL database using PHP in Specific Date Range

In this tutorial Extract Data in Excel From MySQL database using PHP in Specific Date Range, we are going to learn how to export MySQL data to excel using PHP and HTML. We generally need reports in excel sheet, but sometimes our data stored in MySQL database. Then It is quite easy to fetch records from MySQL and export the result in a .csv file or .xls file and provider button for a user to download the data according to the date or between two dates. In this tutorial will make use of PHP and MySQL to do this functionality. This downloading excel report or data option is very important in a web application. Using this code, we can extract the data from MySQL database.

Connection Page

Here is a connection.php file, it will contain our script to the database. And it helps to connect to the server and use our database.

Connection.php

Database connection code

1

2

3

4

<?php

$conn=mysql_connect('localhost','root','');

mysql_select_db(‘excel');

?>

Display Page

Index.php

This page will appear whenever a user opens the site, this page contains simple web interface which contains two date input field that allows the user enter the first and last date to extract the data from the database in excel according to date.

Now, Create a PHP file named “index.php” and paste the following code inside of it.

When the user enters the date and clicks to the submit button the index.php page will redirect to logic page (excel.php) and extract the data from the database and download it.

Logic Page

This page contains the logic and the MySQL query to fetch the data from the database

Excel.php

Code of logic page

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

<?php

include('connection.php');

if(isset($_POST['submit'])){

$date1=$_POST['date1'];

$date2=$_POST['date2'];

$SQL="SELECT *

FROM excel

where DATE(date) BETWEEN '$date1' AND '$date2'";

$header='';

$result='';

$exportData=mysql_query($SQL)ordie("Sql error : ".mysql_error());

$fields=mysql_num_fields($exportData);

for($i=0;$i<$fields;$i++)

{

$header.=mysql_field_name($exportData,$i)."\t";

}

while($row=mysql_fetch_row($exportData))

{

$line='';

foreach($rowas$value)

{

if((!isset($value))||($value==""))

{

$value="\t";

}

else

{

$value=str_replace('"','""',$value);

$value='"'.$value.'"'."\t";

}

$line.=$value;

}

$result.=trim($line)."\n";

}

$result=str_replace("\r","",$result);

if($result=="")

{

$result="\nNo Record(s) Found!\n";

}

header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=export.xls");

header("Pragma: no-cache");

header("Expires: 0");

print"$header\n$result";

}

?>

Demo Image

That’s all, this is how we can export MySQL data to excel using simple PHP and HTML code. You can also customize this code further as per your requirement. To get the latest news and updates follow us on twitter & facebook, subscribe to our YouTube channel. And please feel free to ask any question regarding the tutorial.

Our Facebook Page

About techJunkgigs

TechJunkGigs is a blog for all the students learning programming. We are providing various tutorials related to programming and application development. You can get various nice and Easy tutorials related to programming, app development, graphics designing and animation. We are writing text tutorial and creating video and visual tutorials as well. You can check about the admin of the blog here and check out our sitemap