Jump to content

e-mailing exported sql tables in excel file format


agon024

Recommended Posts

what I am trying to do is use this php script to load the data being submitted in the html form into my database and then populate the database into an excel (xls) file and then e-mail it to my address.

 

Everything works great it populates into the database and creates the xls file perfect. But it is wanting me to download the file. What can I add to the script to have it e-mail the file to my e-mail address INSTEAD of downloading it.

 

<?php

define('DB_NAME', 'database');
define('DB_USER', 'username');
define('DB_PASSWORD', 'password');
define('DB_HOST', 'hostname');

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
	if (!$link)	  
		{
			die('Could not connect: ' . mysql_error());  
			}	

$db_selected = mysql_select_db(DB_NAME, $link);

if (!$db_selected) {
die('Can\'t use ' . DB_NAME . ': ' . mysql_error());
}

$value1 = $_POST['groupname'];
$value2 = $_POST['name'];
$value3 = $_POST['address'];
$value4 = $_POST['city'];
$value5 = $_POST['state'];
$value6 = $_POST['zip'];
$value7 = $_POST['homephone'];
$value8 = $_POST['cellphone'];
$value9 = $_POST['email'];
$value10 = $_POST['age'];
$value11 = $_POST['maritalstatus'];
$value12 = $_POST['income'];
$value13 = $_POST['contact1'];
$value14 = $_POST['contact2'];
$value15 = $_POST['contact3'];
$value16 = $_POST['date1'];
$value17 = $_POST['date2'];
$value18 = $_POST['date3'];


$sql = "INSERT INTO clients (groupname, name, address, city, state, zip, homephone, cellphone, email, age, maritalstatus, income, contact1, contact2, contact3, date1, date2, date3) VALUES ('$value1', '$value2', '$value3', '$value4', '$value5', '$value6', '$value7', '$value8', '$value9', '$value10', '$value11', '$value12', '$value13', '$value14', '$value15', '$value16', '$value17', '$value18')";

if (!mysql_query($sql)) {
die('Error: ' . mysql_error());
}

mysql_close();

mysql_connect('hostname', 'username', 'password');
mysql_select_db('database');

$sql = "SELECT
`groupname` AS `Group`,
`name` AS `Customer Name`, 
`address` AS `Address`,
`city` AS `City`,
`state` AS `State`,
`zip` AS `Zip Code`,
`homephone` AS `Home Phone`,
`cellphone` AS `Cell Phone`,
`email` AS `E-Mail`,
`age` AS `Age Group`,
`maritalstatus` AS `Marital Status`,
`income` AS `Household Income`,
`contact1` AS `Contact VIA`,
`contact2` AS `Contact VIA`,
`contact3` AS `Contact VIA`,
`date1` AS `1st Date`,
`date2` AS `2nd Date`,
`date3` AS `3rd Date`

FROM fundtour_info.clients clients";

// Query Database
$result=mysql_query($sql);
$filename = 'file.xls';

// Send Header
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=$filename");
header("Content-Transfer-Encoding: binary ");

// XLS Data Cell

xlsBOF();	  
xlsWriteLabel(0,0,"Group");
xlsWriteLabel(0,1,"Name");
xlsWriteLabel(0,2,"Address");
xlsWriteLabel(0,3,"City");
xlsWriteLabel(0,4,"State");
xlsWriteLabel(0,5,"Zip Code");
xlsWriteLabel(0,6,"Home Phone");
xlsWriteLabel(0,7,"Cell Phone");
xlsWriteLabel(0,8,"E-mail Address :");
xlsWriteLabel(0,9,"Age Group");
xlsWriteLabel(0,10,"Marital Status");
xlsWriteLabel(0,11,"Income");
xlsWriteLabel(0,12,"Contact Via");
xlsWriteLabel(0,13,"Dates");
   
$xlsRow = 1;
while(list($groupname,$name,$address,$city,$state,$zip,$homephone,$cellphone,$email,$age,$maritalstatus,$income,$contact1, $contact2, $contact3,$date1, $date3, $date3)=mysql_fetch_row($result)) 
{		
	++$i;
	xlsWriteLabel($xlsRow,0,"$groupname");
	xlsWriteLabel($xlsRow,1,"$name"); 
	xlsWriteLabel($xlsRow,2,"$address");
	xlsWriteLabel($xlsRow,3,"$city");
	xlsWriteLabel($xlsRow,4,"$state");
	xlsWriteLabel($xlsRow,5,"$zip");
	xlsWriteLabel($xlsRow,6,"$homephone");
	xlsWriteLabel($xlsRow,7,"$cellphone");
	xlsWriteLabel($xlsRow,8,"$email");
	xlsWriteLabel($xlsRow,9,"$age");
	xlsWriteLabel($xlsRow,10,"$maritalstatus");
	xlsWriteLabel($xlsRow,11,"$income");
	xlsWriteLabel($xlsRow,12,"$contact1, $contact2, $contact3");
	xlsWriteLabel($xlsRow,13,"$date1, $date3, $date3");                      
	$xlsRow+++;
}
xlsEOF();
exit();

function xlsBOF() {
    echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);  
    return;
}

function xlsEOF() {
    echo pack("ss", 0x0A, 0x00);
    return;
}

function xlsWriteNumber($Row, $Col, $Value) {
    echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
    echo pack("d", $Value);
    return;
}

function xlsWriteLabel($Row, $Col, $Value ) {
    $L = strlen($Value);
    echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
    echo $Value;
return;
} 

?>

Thanks for any help

 

Instead of using echo, store everything in a variable and then use a mailer class like PHPMailer or SwiftMailer to send that as an attachment.

$xlsFile = "";

//insert your code

//using SwiftMailer as an example
require_once 'swiftmailer/lib/swift_required.php';
$attachment = Swift_Attachment::newInstance($xlsFile, 'file.xls', 'application/vnd.ms-excel');
$transport = Swift_MailTransport::newInstance(); //using php's mail()
$mailer = Swift_Mailer::newInstance($transport);
$message = Swift_Message::newInstance('Wonderful Subject')
  ->setFrom(array('[email protected]' => 'John Doe'))
  ->setTo(array('[email protected]', '[email protected]' => 'A name'))
  ->setBody('Here is the message itself')
  ->attach($attachment);
$result = $mailer->send($message);

//append to variable instead of echo
function xlsBOF() {
global $xlsFile;
$xlsFile .= pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
}

//repeat for other functions

Get rid of all the header() calls if you want the browser to stop prompting you to download it.

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.