extracting data from mysql to excel

Posted on 2006-05-13
Last Modified: 2010-04-06

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

$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";

Question by:andrewaiello
    LVL 6

    Accepted Solution

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

    LVL 1

    Author Comment

    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";
    LVL 6

    Expert Comment

    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".
    LVL 6

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Most of the sites are being standardized with W3C Web Standards. W3C provides lot of web standard services to the web. They have the web specification, process and documentation for all the web standards. You can apply HTML, CSS and Accessibility st…
    Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System ( introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
    Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
    The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

    758 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

    10 Experts available now in Live!

    Get 1:1 Help Now