PHP mysql data export to excelsheet field name row1 and data in 2nd row? help.

[sayedsohail]

Hi,

I am trying to save one single record in excel sheet and download, the problem is all the headings and columns are exported into just one column,  How do i force my loop to set headings such as (field name) on first row and data in second row.  Please help?


[PHPNET]<?php
$recNum = $_GET['rec_No'];

$select = "SELECT * FROM clients where id=$recNum";
$export = mysql_query($select);
$fields = mysql_num_fields($export);

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

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

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

header("Content-Disposition: attachment; filename=extraction.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
header("Content-type: application/x-msdownload");
require_once("footer.php");
mysql_close($conn); [/PHPNET]

[dgiberson]

try using the html <table><tr><td></td></tr></table> setup, should fix it up for ya...

[sayedsohail]

Please can you just give an example, which line i need to modify in my code.  I got this code from tutorial.  please help. It seems i need to modify the whole thing after while loop and i am bit confused.  please help with a simple example please.  thanks.

[dgiberson]

[code]<?php
$recNum = $_GET['rec_No'];

$select = "SELECT * FROM clients where id=$recNum";
$export = mysql_query($select);
$fields = mysql_num_fields($export);
$header = "<table border=1><tr>";
for ($i = 0; $i < $fields; $i++) {
$header .= "<td>".mysql_field_name($export, $i) . "<\td>";
}
$header .= "</tr>";
while($row = mysql_fetch_row($export)) {
$line = "<tr>";
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "<td>&nbsp;</td>";
} else {
$value = str_replace('"', '""', $value);
$value = '<td>"' . $value . '"' . "<\td>";
}
$line .= $value;
}
$data .= trim($line)."</tr>";
}


if ($data == "") {
$data = "<tr><td colspan=$fields>(0) Records Found!<\td></tr>";
}
$data .= "</table>";
header("Content-Disposition: attachment; filename=extraction.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
header("Content-type: application/x-msdownload");
require_once("footer.php");
mysql_close($conn); [/code]


I think that should work.... if not its pretty close

[sayedsohail]

thanks for your help, its prints fine but with double quote and <d> tags. any suggestions please.


Field Name appears with <d> tag
id< d> member_id< d> name< d> address_1< d> address_2< d> address_3< d> p_code< d> city< d> country< d> l_line< d> fax< d> web_domain< d> email< d> expiration< d> mobile< d>

Data appears with double quotes and <d> tag

"11"< d>" "1"< d>" "Technologies Ltd"< d>" "Tip Road"< d>" "Yorkshire"< d> "y3HY"< d> "0123445 5324"< d>" "0123445 5324"< d>" "0000-00-00"< d>

[dgiberson]

$value = '<td>"' . $value . '"' . "<\td>";

should be

$value = "<td>" . $value . "<\td>";

[sayedsohail]

instead I tried something simple to print specific fields but i got an error any suggestions.

<?php

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

$recNum = $_GET['rec_No'];

$query2 = "SELECT id, name, address_1, p_code, city, l_line, mobile FROM clients WHERE id='$recNum'";


$result2 = mysql_query($query2) or die('Error, query failed');

$test_rows = mysql_num_rows($result2);
if ($test_rows <= 0) {
print "<table><TR><TD><h4>Sorry there are ($test_rows) clients in the database.  Please add some clients.\n</h4></td></tr></table>";
}

else { 

//print the table
print "<table border='1'>
<tr align='center'>
<td>Sl.No.</td>
<td>Client Name</td>
<td>Address</td>
<td>Post Code</td>
<td>Location</td>
<td>Phone</td>
<td>Mobile</td></tr>";

while(list($id, $name, $address_1, $p_code, $city, $l_line, $mobile) = mysql_fetch_array($result2))

{

print "<tr>
<td>$id</td>
<td>$name</td>
<td>$address_1</td>
<td>$p_code</td>
<td>$city</td>
<td>$l_line</td>
<td>$mobile</td></tr>";

}
print '</table>';
require_once("footer.php");
mysql_close($conn);

?>

[sayedsohail]

[quote author=dgiberson link=topic=124000.msg513287#msg513287 date=1169744732]
$value = '<td>"' . $value . '"' . "<\td>";

should be

$value = "<td>" . $value . "<\td>";
[/quote]

Now the double quotes are gone but < d> appears in every column..  However, I tried using more simpler way code above pretty close but not success in either of this methods. Any suggestions.