Solved

mysql to excel question

Posted on 2007-04-08
2
301 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

867 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

16 Experts available now in Live!

Get 1:1 Help Now