Using PHP to Export SQL Results into CSV & E-mail

[SeanAC]

Hi Everyone,

 

Firstly, I would like to make the following advanced apologies;

 

[*]Please accept my apologies if this question has been asked before. If you could point me to the correct place I would be happy to read there. Thus far I have been unable to locate anything which answers this directly.

 

[*]Please accept my apologies if I seem somewhat ignorant with regards to PHP. I am very much a beginner here, so go light on me .

 

I have a piece of PHP code which basically runs an SQL Query, and prints the results in HTML onto an e-mail. It then e-mails the contents to me. I would prefer this to instead print the data into a CSV file and e-mail that file to me. Essentially what I want the code to do is:

 

[*]Run an SQL Query on an Oracle database, to retrieve data

[*]Export the data into a CSV file - this file does not need to be kept server side

[*]E-mail this CSV File

 

The current code I have (which prints directly into the body of the e-mail) is as below. I assume it would not be too much more difficult to put this into CSV. As this relates to company data I have substituted certain references in the data below to remove potentially sensitive references.

 

<?php

putenv("TNS_ADMIN=/usr/lib/oracle/11.2/client64");

require_once "DB.php";

@$DB = DB::connect('oci8://#####:#####@domain');
if (DB::isError($DB))
{
echo 'Cannot connect to database: ' . $DB->getMessage();
}
else
{
$fromdate = strtotime("last Monday");
$todate = strtotime("last Sunday");
if($todate < $fromdate)
{
// subtract another week
$fromdate = strtotime("-1 weeks", $fromdate);
}

$fromdate = date('d/m/Y', $fromdate);
$todate = date('d/m/Y', $todate);

$body = "";
$body .= "<h3>For period " . $fromdate . " to " .  $todate . "</h3>\n";

$query = "SELECT DCONSUMER, DDATE, DTYPE, DAMT/100 AS DAMT, SUBSTR(DWHO,0,INSTR(DWHO,':')-1) AS DWHO, DREF, DEXTDESC   
FROM UTILITY.DEBT
WHERE SUBSTR(DWHO,0,INSTR(DWHO,':')-1) IN
    (
    SELECT ID FROM UTILITY.USERS
    WHERE USE_SEN = 'MANAGER'
    )
AND DTYPE NOT IN (CHG1',CHG2','CHG3','CHG4')
AND DDATE >= TO_DATE('".$fromdate."','dd/mm/yyyy')
AND DDATE <= TO_DATE('".$todate."','dd/mm/yyyy')
ORDER BY DAMT";

$result = $DB->query($query);

$body .= "<table cellspacing='3'\n";
$body .= "<font size='2'>\n";
$body .= "<tr><th bgcolor='#AAAAAA'>CONSUMER</th><th bgcolor='#AAAAAA'>DATE</th><th bgcolor='#AAAAAA'>TYPE</th><th bgcolor='#AAAAAA'>AMT</th><th bgcolor='#AAAAAA'>WHO</th><th bgcolor='#AAAAAA'>REF</th><th bgcolor='#AAAAAA'>EXTDESC</th></tr>\n";
while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC))
{
$body .= "<tr><td>" . $row['DCONSUMER'] . "</td><td>" . $row['DDATE'] . "</td><td>" . $row['DTYPE'] . "</td><td>" . $row['DAMT'] . "</td><td>" . $row['DWHO'] . "</td><td>" . $row['DREF'] . "</td><td>" . $row['DEXTDESC']. "</td></tr>\n";
}
$body .= "</font>\n";
$body .= "</table>\n";

#print $body;

$to = "";
$subject = "Automated Query: (".$fromdate." to ".$todate.")";
$headers  = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
$headers .= 'From: Automated Scripts <[email protected]>' . "\r\n";
$headers .= 'To: [email protected]' . "\r\n";
$headers .= 'Cc: [email protected]' . "\r\n";
mail($to, $subject, $body, $headers);
}
?>

 

Your assistance is much appreciated.

 

Cheers,

Sean

[SeanAC]

Okay, so I have tried to have a go at working out how to do this myself using google... I think I may have made the problem worse

 

I am getting the error "PHP Fatal error:  Only variables can be passed by reference in /home/sqlqueries/Test.php on line 45". It is probably fair to say I have no idea what I am doing at the moment. Frankly, I am still at the stage on PHP where everything looks like another language. Help anyone?

 

At the moment, it just sends me an e-mail with an empty spreadsheet... along with an error in PuTTY.

 

 

<?php

putenv("TNS_ADMIN=/usr/lib/oracle/11.2/client64");

require_once "DB.php";

@$DB = oci_connect('#####','#####','domain');
if (DB::isError($DB))
{
echo 'Cannot connect to database: ' . $DB->getMessage();
}
else
{ 
$strPath = "domain";  
$filName = "customer.csv";  
$objWrite = fopen($strPath."/".$filName, "w");
$fromdate = strtotime("last Monday");
$todate = strtotime("last Sunday");
if($todate < $fromdate)
{
	// subtract another week
	$fromdate = strtotime("-1 weeks", $fromdate);
}

$fromdate = date('d/m/Y', $fromdate);
$todate = date('d/m/Y', $todate);

$body = "";
$body .= "<h3>For period " . $fromdate . " to " .  $todate . "</h3>\n";

$Query = "SELECT DCONSUMER, DDATE, DTYPE, DAMT/100 AS DAMT, SUBSTR(DWHO,0,INSTR(DWHO,':')-1) AS DWHO, DREF, DEXTDESC     
FROM UTILITY.DEBT
WHERE SUBSTR(DWHO,0,INSTR(DWHO,':')-1) IN
    (
    SELECT ID FROM UTILITY.USERS
    WHERE USE_SEN = MANAGER'
    )
AND DTYPE NOT IN ('CHG1','CHG2','CHG3','CHG4')
AND DDATE >= TO_DATE('".$fromdate."','dd/mm/yyyy')
AND DDATE <= TO_DATE('".$todate."','dd/mm/yyyy')
ORDER BY DAMT";

$objQuery = oci_parse($DB, $Query);

while($objResult = oci_fetch_all($objQuery, OCI_BOTH))  
{  
fwrite($objWrite, "\"$objResult[DCONSUMER]\",\"$objResult[DDATE]\",\"$objResult[DTYPE]\",\"$objResult[DAMT]\",\"$objResult[DWHO]\",\"$objResult[DREF]\",\"$objResult[DEXTDESC]\" \n"); 
}  
fclose($objWrite);  
  
//*************** Send Email ***************//  
  
$strTo = "[email protected]";  
$strSubject = "CSV Report $fromdate - $todate";  
$strMessage = "Download $filName for CSV Report for period $fromdate to $todate";  
  
//*** Uniqid Session ***//  
$strSid = md5(uniqid(time()));  
  
$strHeader = "";  
$strHeader .= "From: Automation <[email protected]>\nReply-To: [email protected]\n";  
$strHeader .= "Bcc: [email protected]";  
  
$strHeader .= "MIME-Version: 1.0\n";  
$strHeader .= "Content-Type: multipart/mixed; boundary=\"".$strSid."\"\n\n";  
$strHeader .= "This is a multi-part message in MIME format.\n";  
  
$strHeader .= "--".$strSid."\n";  
$strHeader .= "Content-type: text/html; charset=windows-874\n"; // or UTF-8 //  
$strHeader .= "Content-Transfer-Encoding: 7bit\n\n";  
$strHeader .= $strMessage."\n\n";  
  
$strContent1 = chunk_split(base64_encode(file_get_contents($strPath."/".$filName)));  
$strHeader .= "--".$strSid."\n";  
$strHeader .= "Content-Type: application/octet-stream; name=\"".$filName."\"\n";  
$strHeader .= "Content-Transfer-Encoding: base64\n";  
$strHeader .= "Content-Disposition: attachment; filename=\"".$filName."\"\n\n";  
$strHeader .= $strContent1."\n\n";  
  
$flgSend = @mail($strTo,$strSubject,null,$strHeader); // @ = No Show Error //  
}  
?>  

[coupe-r]

This may not help you, but this is what I use to export into a CSV from PHP / mySQL and it works great.

 

I took out my query for this though.

 

<?php
$file = 'Notes_Export';
$csv_output = array();


$tmp = array();
$tmp[] = 'Created On';
$tmp[] = 'Created By';
$tmp[] = 'Note';

$csv_output[] = '"' . implode('","', $tmp) . '"';

$sql = "";
$result = mysqli_query($connect, $sql);



while($rowr = mysqli_fetch_row($result))
{
$tmp = array();

	for ($j=0; $j<3; $j++)	{$tmp[] = $rowr[$j];}
	$csv_output[] = '"' . implode('","', $tmp) . '"';
}


$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
// header( "Content-disposition: filename=".$filename.".csv");
header("Content-disposition: attachment; filename=".$filename.".csv");
print implode("\n",$csv_output) . "\n";
exit;
?>

[kicken]

You need to do two things.

 

1) Create a CSV format file.  Right now your just making a html table.  This is not too hard to do using fputcsv().

2) Email the file to yourself.  This involves creating a multipart mime email to attach the file.  It can be a bit of a pain.  I would recommend you get a class to do this for you, such as PHP Mailer or Mail::Mime

 

$tmp = tmpfile();

//Output headers 
$csvLength = fputcsv($tmp, array(
'CONSUMER',
'DATE',
'TYPE',
'...'
));

while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC))
{
$csvLength += fputcsv($tmp, array(
	$row['DCONSUMER'],
	$row['DDATE'],
	$row['DTYPE'],
	$row['...']
));
}

rewind($tmp);
$csvContent = fread($tmp, $csvLength);
fclose($tmp);

//mail your file here with whatever method you choose.
//$csvContent is the csv file, add it as an attachment.

 

[Barand]

I find it easier to create a download of the csv

 

<?php
include("testDBconnect.php");

$sql = "SELECT * FROM employees";

sql2csv ($sql, 'employees.csv', 1);   



function sql2csv($sql, $filename='', $headings=1)
{
   if (!$filename)
    $f = 'download_' . date('ymdhi') . '.csv';
   else
	 $f = $filename;
   $fp = fopen('php://output', 'w');	    // so you can fputcsv to STDOUT
   if ($fp) {
    $res = mysql_query($sql);
    if ($res) {
	    header('Content-Type: text/csv');
	    header('Content-Disposition: attachment; filename="'.$f.'"');
	    header('Pragma: no-cache');
	    header('Expires: 0');
	    $row = mysql_fetch_assoc($res);
	    if ($headings) {
		    fputcsv($fp, array_keys($row));
	    }
	    do {
		    fputcsv($fp, $row);
	    } while ($row = mysql_fetch_assoc($res));

    }
    else echo "Error in query";
    fclose($fp);
    exit;
   }

}

?>