andrewaiello
asked on
extracting data from mysql to excel
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-Dispositio n: attachment; filename=extraction.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$headern$data";
?>
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-Dispositio
header("Pragma: no-cache");
header("Expires: 0");
print "$headern$data";
?>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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".
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) ;
$data = str_replace("r","",$data);
I think also was meaned as:
$data = str_replace("\r","",$data)
ASKER
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "t";
}