Solved

mysql to excel question

Posted on 2007-04-08
2
315 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

724 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