Solved

mysql to excel question

Posted on 2007-04-08
2
308 Views
Last Modified: 2013-12-12
I'm exporting info from a mysql db to excel. All works fine.

Is there anyway of adding to the script something which will add three fields/cells to excel which arent part of the db.

for example

add a excel heading of 'sample'  and a cell value of 'X' to each record.

See script below.

<?
$check = mysql_pconnect("sample", "sample", "sample") or die(mysql_error());
mysql_select_db("sample", $check) or die(mysql_error());

$Recordset1 = mysql_query("SELECT date, subtotal as Amount, total as IncTax Amount, companyname as Cofusedmedia, description FROM tblinvoices t1, tblclients t2, tblinvoiceitems t3
where t1.userid = t2.id
and t1.userid = t3.userid", $check) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$fields = mysql_num_fields($Recordset1);

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

 do {
    $line = '';
    foreach($row_Recordset1 as $value) {                                            
        if ((!isset($value)) OR ($value == "")) {
            $value = "\t";
        } else {
            $value = str_replace('"', '""', $value);
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim($line)."\n";
} while($row_Recordset1 = mysql_fetch_assoc($Recordset1));
$data = str_replace("\r","",$data);

if ($data == "") {
    $data = "\n(0) Records Found!\n";  
      }
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=report.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
?>
<?php
mysql_free_result($Recordset1);
?>
0
Comment
Question by:Cheryl Lander
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 400 total points
ID: 18872049
hi
change your sql to
select 'the title you want here','','','','',''
from yourtable
limit 1
union all
SELECT 'X', date, subtotal as Amount, total as IncTax Amount, companyname as Cofusedmedia, description FROM tblinvoices t1, tblclients t2, tblinvoiceitems t3
where t1.userid = t2.id
and t1.userid = t3.userid

this way you select the header from any table you want
and the unilon will add the result (notice you need the same number of column in both of the select statements thats why i added the empty strings)
you can also select 'X' using sql and use it like another column
0
 
LVL 15

Assisted Solution

by:babuno5
babuno5 earned 100 total points
ID: 18874478
I think this should work

$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$fields = mysql_num_fields($Recordset1);

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

 do {
    $line = '';
    foreach($row_Recordset1 as $value) {                                            
        if ((!isset($value)) OR ($value == "")) {
            $value = "\t";
        } else {
            $value = str_replace('"', '""', $value);
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
        $line .= "newcoloumn1value1\t";
        $line .= "newcoloumn1value2\t";
        $line .= "newcoloumn1value3\t";
    }
    $data .= trim($line)."\n";
} while($row_Recordset1 = mysql_fetch_assoc($Recordset1));
$data = str_replace("\r","",$data);

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

820 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