Export MySQL to Excel Using PHP

The code below will export every column name and value from your database into an excel document.

Note: Delete the line spaces if you receive a parse error.

01
<?php

02
/*******EDIT LINES 3-8*******/

03
$DB_Server = "localhost"; //MySQL Server 

04
$DB_Username = "username"; //MySQL Username 

05
$DB_Password = "password";             //MySQL Password 

06
$DB_DBName = "databasename";         //MySQL Database Name 

07
$DB_TBLName = "tablename"; //MySQL Table Name

08
$filename = "excelfilename";         //File Name

09
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/

10
//create MySQL connection

11
$sql = "Select * from $DB_TBLName";

12
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)

13
    or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());

14
//select database

15
$Db = @mysql_select_db($DB_DBName, $Connect)

16
    or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());

17
//execute query

18
$result = @mysql_query($sql,$Connect)

19
    or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());

20
$file_ending = "xls";

21
 

22
//header info for browser

23
header("Content-Type: application/xls");

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

25
header("Pragma: no-cache");

26
header("Expires: 0");

27
 

28
/*******Start of Formatting for Excel*******/

29
//define separator (defines columns in excel & tabs in word)

30
$sep = "\t"; //tabbed character

31
 

32
//start of printing column names as names of MySQL fields

33
for ($i = 0; $i < mysql_num_fields($result); $i++) {

34
echo mysql_field_name($result,$i) . "\t";

35
}

36
print("\n");

37
//end of printing column names

38
 

39
//start while loop to get data

40
    while($row = mysql_fetch_row($result))

41
    {

42
        $schema_insert = "";

43
        for($j=0; $j<mysql_num_fields($result);$j++)

44
        {

45
            if(!isset($row[$j]))

46
                $schema_insert .= "NULL".$sep;

47
            elseif ($row[$j] != "")

48
                $schema_insert .= "$row[$j]".$sep;

49
            else

50
                $schema_insert .= "".$sep;

51
        }

52
        $schema_insert = str_replace($sep."$", "", $schema_insert);

53
 $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);

54
        $schema_insert .= "\t";

55
        print(trim($schema_insert));

56
        print "\n";

57
    }

58
?>

59

-By Parthiv Patel