Jump to content

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

SeanAC

By SeanAC
in PHP Coding Help

 Share

Recommended Posts

SeanAC Newbie

SeanAC

Posted
Posted

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 <root@iron.domain.local>' . "\r\n";

	$headers .= 'To: myemail@domain.com' . "\r\n";

	$headers .= 'Cc: otheremail@domain.com' . "\r\n";

	mail($to, $subject, $body, $headers);

}

?>






 



Your assistance is much appreciated. :)


 



Cheers,



Sean



			
		


		
			

				
					
						

	
	
		

			
				
				

					
				

			
			
			
		

	


					
				
				
			

		

		
	


	

	






	



					
					
					
				

					

					
					







	

	

	

		

			

				


	
		SeanAC
	

				
				
					
						
Newbie
					
				
			

		

		

			

				


SeanAC
			

			

			    Posted 
				
			

		

	

	
	

		




	

		

			

				
    
					
						
  • Author
    • 
					
					
					
					
					
				

			

			
		


		

			Posted 
			
			
				
				
			
		

	


	

    

	

		
		

			

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 :P


 



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 = "me@domain.com";  
$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 <root@domain.local>\nReply-To: root@iron.domain.local\n";  
$strHeader .= "Bcc: improvements@domain.local";  
  
$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
	

				
				
					
						
Regular Member
					
				
			

		

		

			

				


coupe-r
			

			

			    Posted 
				
			

		

	

	
	

		




	

		

			

				
    
					
					
					
					
					
				

			

			
		


		

			Posted 
			
			
				
				
			
		

	


	

    

	

		
		

			

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
	

				
				
				
				
					
						
Prolific Member
					
				
			

		

		

			

				


kicken
			

			

			    Posted 
				
			

		

	

	
	

		




	

		

			

				
    
					
					
					
					
					
				

			

			
		


		

			Posted 
			
			
				
				
			
		

	


	

    

	

		
		

			

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.







 



			
		


		
			

				
					
						

	
	
		

			
				
				

					
				

			
			
			
		

	


					
				
				
			

		

		
			

		
	


	

	






	



					
						
    
							
							
								
  • 
									1 year later...
								
    • 
							
						

					
					
					
				

					

					
					







	

	

	

		

			

				


	
		Barand
	

				
				
				
				
					
						
Prolific Member
					
				
			

		

		

			

				


Barand
			

			

			    Posted 
				
			

		

	

	
	

		




	

		

			

				
    
					
					
					
					
					
				

			

			
		


		

			Posted 
			
			
				
				
			
		

	


	

    

	

		
		

			

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

}

?>








			
		


		
			

				
					
						

	
	
		

			
				
				

					
				

			
			
			
		

	


					
				
				
			

		

		
			

		
	


	

	






	



					
					
					
				
			
			



		

	


	
	
	
	
	
		
	
      
This thread is more than a year old. Please don't revive it unless you have something important to add.

			
				
				

	
		

			
Join the conversation

			

	
				
					You can post now and register later.
				
				If you have an account, sign in now to post with your account.
				
			

	
		

	




	
	
		
	
		
	
		
	
	
		
		
	
	

		

		


	
		Guest
	


		

			
				
					
				
					
						
    
							
  • 
								


	
	
	
	

								
							
    • 
						

					
				
					
				
			
			
				
					
						
							
						
						



    

		
		

			
		

		
			
 Reply to this topic...

		
		

			

				
					×
					  Pasted as rich text.   Restore formatting
				
			

		

		

			

				  Only 75 emoji are allowed.
			

		

		

			

				×
				  Your link has been automatically embedded.   Display as a link instead
			

		

		

			

			

		

		

			

				×
				  Your previous content has been restored.   Clear editor
			

		

		

			

				×
				  You cannot paste images directly. Upload or insert images from URL.
			

		

		
	

		
	

	

	

		

			×
			
		

		

	



						
					
				
					
				
					
				
			
			
    
				
					
						
					
						
					
						
							
  • 
								


    
	

    

								
							
    • 
						
					
				
				
					
  • 

	

    • 
				
			

		

	



			
		

	

	
		

			

				
					


    
          Share
    

    

				
				
                

                


				
			

		

	







	

		
			
				Go to topic listing
			
		
	

	




						


					

					


					

				

			

			
		

		
		


	

		×
		

			
				

					
				

			

			

			

			
		

	






	

		×
		

			
    
				
  • Create New...
    • 
				
			

		

	



		






















	

		

			
Important Information

			
We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.