extracting data from mysql to excel


Below is a script that pulls data from a mysql database and puts it into an excel file.  The problem is all the data is being displayed in 1 row.  Does anyone see what the problem is?


//Connect To Database

$host = "d";
$user = "mo";
$password ="H";
$db_name = "m";

//Linking to the database on pair.com

$link = mysql_connect($host, $user, $password) or die (mysql_error());
Mysql_select_db("m") or die(mysql_error());

//Count the number of fields in the database

$select = "SELECT * FROM form_data";
$export = mysql_query($select);
$fields = mysql_num_fields($export);

//start a loop to extract all the field names from the db

for ($i = 0; $i < $fields; $i++) {
      $header .= mysql_field_name($export, $i) . "t";

//extracting all the data

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);

//Check the database for data

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

//prompts user to save the spread sheet

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=extraction.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$headern$data";

Who is Participating?
KostaConnect With a Mentor Commented:
In all places, where you see "t" and "n" should be "\t" and "\n", like this:

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


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

andrewaielloAuthor Commented:
thanks that works.  The field names are not showing up in the spreadsheet at all.  Is this code correct?

for ($i = 0; $i < $fields; $i++) {
     $header .= mysql_field_name($export, $i) . "t";
This part seems to be correct (except for "t" have to be "\t").
I think the problem is in this row:

print "$headern$data";

It should be:

print "$header\n$data";

The same thing - "\n" instead of "n".
this replace:
     $data = str_replace("r","",$data);
I think also was meaned as:
     $data = str_replace("\r","",$data);
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.