start from here

Take from PHP Freaks :

Introduction

Hello fellow PHP Freaks. How are we feeling today? I hope you are all doing well. Today we are going to learn how to export a MySQL table into an Excel spreadsheet. I know this is extremely interesting and you are probably shaking with excitement, but please try and contain yourselves until the tutorial has been finished.
Why would I need to export anything into an Excel spreadsheet?

Okay let’s face it, in the business world today your boss doesn’t want to see your exquisite database or its’ structure. He/she wants easy to read and comfortable results. Non-tech people want something that makes them not feel like they are behind the times, they want something that is familiar and something they feel they can relate to. Is this wrong, no, I would feel the same way and that is one of the reasons I choose to write this tutorial. People want the easiest solution possible.

What is an Excel spreadsheet?

Excel is made by this small company named Microsoft. It is a program that can help the business world feel like they are using a database without ever really using a database. You can manage data into rows and columns, calculate basic arithmetic, and even add images.

What will be covered in this tutorial?

Basically this tutorial will cover how to extract data from your existing MySQL database and format it into an excel spreadsheet. After the spreadsheet has been formatted it will be available for download via a prompt. Once you see the code, you will see that it is actually quite basic. After you read this tutorial you will see that you can export your database into any format you wish. I just choose Excel because it sounds like it would be hard and intimidating, but in actuality it is one of the easiest. Are you ready yet? Is your mouth salivating? Okay let’s get started.

< ?php
define(db_host, "localhost");
define(db_user, "excel");
define(db_pass, "spreadsheet");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "export2excel");
mysql_select_db(db_name);
?>

Okay first thing’s first, we must establish a connection with our MySQL database. The value entered above are not real values, they will not connect to anything. You must fill in your host, username, password, and database name. Just as a note, it is always a very smart idea to set crucial data as a constant. This way no outside source will be able to change your information thru URL hacks.

Let’s break this down so we can understand what is going on above. First we are setting (5) constants. We set our hostname, username, password, link to the database connection, and our database name. Basically the first (4) lines of code just connect you to the database. After you connect to the database you do not need to do it again. The last (2) lines of code establish the name of the database you wish to open and then we select that database to work with. Now every time we would have to query the database, MySQL know which database we want to be talking to.

< ?php
$select = "SELECT * FROM TABLE_NAME";
$export = mysql_query($select);
$fields = mysql_num_fields($export);
?>

This small snippet of code is doing (3) very important things. The first line is our query to send to the database. Without this MySQL would have no clue what we want and an error would occur. The second line of code is simply calling or executing the query set above it, and the third line of code is counting the number of fields in the database. We need to know the number of fields so we can properly organize our columns and rows in our excel spreadsheet.

Extract Your Database Fields

< ?php
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "\t";
}
?>

In this fragment of code we are starting a loop and extracting all the field names from our database. In the first line of code we have set $i to 0. Then it checks to see if $i is less than the number of fields, and finally it adds 1 to $i. In the second line of code we define the $header variable with the field name. If you are wondering why I have a period in front of the equal sign it is because if we don’t, we will only extract the last field name, we want all the field names. If we would just type an equal sign the variable would write over itself on every loop, but with period beforehand, it just adds on the end of the variable. Pretty neat. This line also uses the mysql_field_name() function that will extract a field name depending on its’ numeric position in the database starting with 0. That is why are loop starts at zero and not 1. The last part of this line is a tab (\t). On every loop we write the field name and then we must tab over so we will be in the next column of our excel spreadsheet. After this loop is complete, our header in our spreadsheet will be complete and ready to be written. We will never really be opening up an excel spreadsheet and writing to it, just formatting data that will make up a spreadsheet.