PHP MYsql export to Excel Not printing header lables.

Posted on 2009-02-23
Last Modified: 2013-12-12
For some reason this php script that exports mysql to excel is not printing my header labels.
How would i get it to print the column header labels? Looks like its already setup to do this.


This PHP script Extracts MySQL table and downloads into an Excel Spreadsheet.

Script by Jeff Johns, for a full explanation and tutorial on this, see:



YOUR DATABASE HOST = (ex. localhost)

USERNAME = username used to connect to host

PASSWORD = password used to connect to host

DB_NAME = your database name

TABLE_NAME = table in the database used for extraction


To extract specific fields and not the whole table, simply replace

the * in the $select variable with the fields you want


define(db_host, "localhost");

define(db_user, "####");

define(db_pass, "######");

define(db_link, mysql_connect(db_host,db_user,db_pass));

define(db_name, "roster");



Build query, call it, and find the number of fields


$select = "SELECT * FROM employees";

$export = mysql_query($select);

$count = mysql_num_fields($export);


Extract field names and write them to the $header variable


for ($i = 0; $i < $count; $i++) {

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



Extract all data, format it, and assign to the $data variable


while($row = mysql_fetch_row($export)) {

$line = '';

foreach($row as $value) {

if ((!isset($value)) OR ($value == "")) {

$value = "\t";

} else {

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

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


$line .= $value;


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


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


Set the default message for zero records


if ($data == "") {

$data = "n(0) Records Found!n";



Set the automatic download section


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

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

header("Pragma: no-cache");

header("Expires: 0");

print "$headern$data";


Open in new window

Question by:hicommfcu
    LVL 16

    Assisted Solution

    What if you change line #65 to

    print $header . $data;
    LVL 19

    Accepted Solution

    Close, but how about this

    print $header. "\n". $data;
    LVL 16

    Expert Comment

    Even better.. ;-)

    Author Closing Comment


    Featured Post

    Easy Project Management (No User Manual Required)

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    A colleague recently asked me about how to give his client a small part of the web site that could be completely under the client's control.  Since I have done this sort of thing before to add emergency banners to a web site, I decided I would creat…
    Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now