?
Solved

extracting data from mysql to excel

Posted on 2006-05-13
4
Medium Priority
?
202 Views
Last Modified: 2010-04-06
Hi,

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?

<?php

//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";
?>

0
Comment
Question by:andrewaiello
  • 3
4 Comments
 
LVL 6

Accepted Solution

by:
Kosta earned 2000 total points
ID: 16676095
In all places, where you see "t" and "n" should be "\t" and "\n", like this:

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

and:

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

0
 
LVL 1

Author Comment

by:andrewaiello
ID: 16676145
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";
}
0
 
LVL 6

Expert Comment

by:Kosta
ID: 16676264
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".
0
 
LVL 6

Expert Comment

by:Kosta
ID: 16676273
this replace:
     $data = str_replace("r","",$data);
I think also was meaned as:
     $data = str_replace("\r","",$data);
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It's sometimes a bit tricky to use date functions in Oracle BPEL. I'll explain quickly how you can add N days to the current date. In a BPEL process this can be useful, and you can adapt it to fit your needs. First of all, let's see how to add 1 …
This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
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…
Suggested Courses

850 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