E-mailing exported sql tables in excel file format

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
agon024
Forum Newbie
Posts: 2
Joined: Thu Mar 29, 2012 2:36 pm

E-mailing exported sql tables in excel file format

Post 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
xtiano77
Forum Commoner
Posts: 72
Joined: Tue Sep 22, 2009 10:53 am
Location: Texas

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

Post 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.
Post Reply