Page 1 of 1

E-mailing exported sql tables in excel file format

Posted: Sat Apr 07, 2012 1:11 pm
by agon024
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 (csv) 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.

Code: Select all

<?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

Re: E-mailing exported sql tables in excel file format

Posted: Mon Apr 09, 2012 6:47 am
by xtiano77
I would remove the

Code: Select all

headers( );
section, then following the link below, add whatever code you need and e-mail the file to yourself. Just my two cents.