e-mailing exported sql tables in excel file format


#1

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]

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

[/php]

Thanks for any help


#2

First, I don’t think you should be writing the data as LABEL’s.
This might cause some problems for editing them later as EXCEL may not understand they are data, just labels.
(Not really sure on this!) The way I have seen it done is to write the live data using this function:
[php]
function xlsWriteString( $Row , $Col , $Value ) {
$L = strlen( $Value );
echo pack( “ssssss” , 0x204 , 8 + $L , $Row , $Col , 0x0 , $L );
echo $Value; return;
}
[/php]
Anyways… To send it as an email, you just have to add the file as an attachment to your email.
To do that, you just need to set up the headers correctly to add the file to it. This is how it is usually done:
(Assuming you already have the email set up and working, this is how to add the attachment-header…)
Depending on how your email code is set up, these are the headers to add…
[php]
header(“Content-Type: application/vnd.ms-excel”);
header(“Content-Disposition: filename=$yourfilename.xls”);
header(“Content-Description: Excel output”);
[/php]
Hope that helps…


#3

K…Did what you said and changed all the xlsWriteLabel to xlsWriteString and still works.

What I want the script to do is load the form data into the mysql database. Create and excel file from the database info and then send an email with all the form info that was just submitted and an excel file attachment with all the database info.

So the email would just look like:

Name: Jason Statham
E-mail: stathamj@hollywood.com

…and so on. but also have an excel file attached with all the customers in it.

Here is the full script I am trying now:
[php]

<?php // This is my Insert to Database Code // 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(); // This is my Excel File Creation Code // 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(); xlsWriteString(0,0,"Group"); xlsWriteString(0,1,"Name"); xlsWriteString(0,2,"Address"); xlsWriteString(0,3,"City"); xlsWriteString(0,4,"State"); xlsWriteString(0,5,"Zip Code"); xlsWriteString(0,6,"Home Phone"); xlsWriteString(0,7,"Cell Phone"); xlsWriteString(0,8,"E-mail Address :"); xlsWriteString(0,9,"Age Group"); xlsWriteString(0,10,"Marital Status"); xlsWriteString(0,11,"Income"); xlsWriteString(0,12,"Contact Via"); xlsWriteString(0,13,"Dates"); $xlsRow = 2; 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; xlsWriteString($xlsRow,0,"$groupname"); xlsWriteString($xlsRow,1,"$name"); xlsWriteString($xlsRow,2,"$address"); xlsWriteString($xlsRow,3,"$city"); xlsWriteString($xlsRow,4,"$state"); xlsWriteString($xlsRow,5,"$zip"); xlsWriteString($xlsRow,6,"$homephone"); xlsWriteString($xlsRow,7,"$cellphone"); xlsWriteString($xlsRow,8,"$email"); xlsWriteString($xlsRow,9,"$age"); xlsWriteString($xlsRow,10,"$maritalstatus"); xlsWriteString($xlsRow,11,"$income"); xlsWriteString($xlsRow,12,"$contact1, $contact2, $contact3"); xlsWriteString($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 xlsWriteString($Row, $Col, $Value ) { $L = strlen($Value); echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L); echo $Value; return; } // This is my Mail Code // $to = "myemail@domain.com"; $referers = array (mysite.com', 'my.site.ip.add', 'localhost'); $subject = "Website Signup Submission"; $message = "Group: " . $_POST["groupname"]; $message .= "\n\nName: " . $_POST["name"]; $message .= "\n\nAddress: " . $_POST["address"]; $message .= "\n\nCity: " . $_POST["city"]; $message .= "\n\nState: " . $_POST["state"]; $message .= "\n\nZip Code: " . $_POST["zip"]; $message .= "\n\nHome Phone: " . $_POST["homephone"]; $message .= "\n\nCell Phone: " . $_POST["cellphone"]; $message .= "\n\nE-mail: " . $_POST["email"]; $message .= "\n\nAge Groups: " . $_POST["age"]; $message .= "\n\nMarital Status: " . $_POST["maritalstatus"]; $message .= "\n\nAnnual Gross Household Income: " . $_POST["income"]; $message .= "\n\nOK To Contact VIA: " . $_POST["contact1"]; $message .= ", " . $_POST["contact2"]; $message .= ", " . $_POST["contact3"]; $message .= "\n\nDates: " . $_POST["date1"]; $message .= " , " . $_POST["date2"]; $message .= " , " . $_POST["date3"]; $headers = "From: " . $_POST["email"]; $headers .= "\nReply-To: $email"; mail($to, $subject, $message); ?>

[/php]

The e-mail portion works when I have this code on its own.

[php]
$to = "myemail@domain.com";
$referers = array (mysite.com’, ‘my.site.ip.add’, ‘localhost’);
$subject = “Website Signup Submission”;
$headers = “MIME-Version: 1.0\r\n”;
$message = "Group: " . $_POST[“groupname”];
$message .= "\n\nName: " . $_POST[“name”];
$message .= "\n\nAddress: " . $_POST[“address”];
$message .= "\n\nCity: " . $_POST[“city”];
$message .= "\n\nState: " . $_POST[“state”];
$message .= "\n\nZip Code: " . $_POST[“zip”];
$message .= "\n\nHome Phone: " . $_POST[“homephone”];
$message .= "\n\nCell Phone: " . $_POST[“cellphone”];
$message .= "\n\nE-mail: " . $_POST[“email”];
$message .= "\n\nAge Groups: " . $_POST[“age”];
$message .= "\n\nMarital Status: " . $_POST[“maritalstatus”];
$message .= "\n\nAnnual Gross Household Income: " . $_POST[“income”];
$message .= "\n\nOK To Contact VIA: " . $_POST[“contact1”];
$message .= ", " . $_POST[“contact2”];
$message .= ", " . $_POST[“contact3”];
$message .= "\n\nDates available: " . $_POST[“date1”];
$message .= " - " . $_POST[“date2”];
$headers = "From: " . $_POST[“email”];
$headers .= “\nReply-To: $email”;

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

// if the redirect option is set: redirect them
if ($redirect) {
header(“Location: $redirect”);
exit;
} else {
echo “Thank you for your submission\n”;
echo “

\n”;
echo “This form was created by Me © 2012 <a href=“http://www.site.com”>site.com\n\n”;
exit;
}

[/php]

It sends an email with no attachment obviously. So that works by itself. But when I add it at the bottom of the rest of the script it doesn’t work. Do you know of a better way to do it? I am not an expert in PHP so an help is greatly appreciated.


#4

Well, this what you have now:
$headers = "From: " . $_POST[“email”];
$headers .= “\nReply-To: $email”;
You need something like this:
$headers = "From: " . $_POST[“email”];
$headers .= “\nReply-To: $email”;
$headers .= “\nContent-Type: application/vnd.ms-excel”;
$headers .= “\nContent-Disposition: filename=” . $yourfilename.xls;
$headers .= “\nContent-Description: Excel output”;

Something like that would work… Let us know if it doesn’t…


#5

Well 2 problems. The first is that the script at the top wich has the database export script, the excel creation script, and the e-mail script; only the datavase export and excel creation script work. The E-mail portion doesn’t work.

If I cut and paste the e-mail script into its own PHP file and change the form to call the e-mail only script then it sends an email just fine to my email address. But not when it is combined with the other script.

What am I doing wrong.


#6

Sorry forgot the second problem. The Excel creation script is creating a file that it wants you to download. How can I change the headers in the excel creation part so that it will create a filename.xls and save it to my root /public_html folder?


#7

Agon024, Okay, I was gone for a bit… You said two problems, but, they sounded like the same one.

You get your posts, no problem.
You save them in the database, no problem.
You create an Excel file and store it, no problem. (With the new .PDF extension as we discussed before.)

So, at that point, can you open the file inside Excel? Does it appear to be correct at that point?

Then, you attempt to email the file as an attachment and it fails. Correct?

I will test sending a PDF file by attachment and send you a fixed version of your code to do that.
Should be some simple header error I can’t see looking at your code. Hopefully we can solve this today!


#8

Here is the new code I am working with:

[php]

<?php // This is my Insert to Database Code // define('DB_NAME', 'database'); define('DB_USER', 'username'); define('DB_PASSWORD', 'password'); define('DB_HOST', 'localhost'); $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['name1']; $value3 = $_POST['name2']; $value4 = $_POST['address']; $value5 = $_POST['city']; $value6 = $_POST['state']; $value7 = $_POST['zip']; $value8 = $_POST['homephone']; $value9 = $_POST['cellphone']; $value10 = $_POST['email']; $value11 = $_POST['age']; $value12 = $_POST['maritalstatus']; $value13 = $_POST['income']; $value14 = $_POST['contact1']; $value15 = $_POST['contact2']; $value16 = $_POST['contact3']; $value17 = $_POST['date1']; $value18 = $_POST['date2']; $value19 = $_POST['date3']; $value20 = $_POST['gift']; $sql = "INSERT INTO clients (groupname, name1, name2, address, city, state, zip, homephone, cellphone, email, age, maritalstatus, income, contact1, contact2, contact3, date1, date2, date3, gift) VALUES ('$value1', '$value2', '$value3', '$value4', '$value5', '$value6', '$value7', '$value8', '$value9', '$value10', '$value11', '$value12', '$value13', '$value14', '$value15', '$value16', '$value17', '$value18', '$value19', '$value20')"; if (!mysql_query($sql)) { die('Error: ' . mysql_error()); } mysql_close(); // This is my Excel File Creation Code // mysql_connect('localhost', 'username', 'password'); mysql_select_db('database'); $sql = "SELECT `groupname` AS `Group`, `name1` AS `Customer Name 1`, `name2` AS `Customer Name 2`, `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`, `gift` AS `Gift Choice` 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(); xlsWriteString(0,0,"Group"); xlsWriteString(0,1,"Name 1"); xlsWriteString(0,2,"Name 2"); xlsWriteString(0,3,"Address"); xlsWriteString(0,4,"City"); xlsWriteString(0,5,"State"); xlsWriteString(0,6,"Zip Code"); xlsWriteString(0,7,"Home Phone"); xlsWriteString(0,8,"Cell Phone"); xlsWriteString(0,9,"E-mail Address :"); xlsWriteString(0,10,"Age Group"); xlsWriteString(0,11,"Marital Status"); xlsWriteString(0,12,"Income"); xlsWriteString(0,13,"Contact Via"); xlsWriteString(0,14,"Dates"); xlsWriteString(0,15,"Gift Choice"); $xlsRow = 2; while(list($groupname,$name1,$name2,$address,$city,$state,$zip,$homephone,$cellphone,$email,$age,$maritalstatus,$income,$contact1, $contact2, $contact3,$date1, $date3, $date3, $gift)=mysql_fetch_row($result)) { ++$i; xlsWriteString($xlsRow,0,"$groupname"); xlsWriteString($xlsRow,1,"$name1"); xlsWriteString($xlsRow,2,"$name2"); xlsWriteString($xlsRow,3,"$address"); xlsWriteString($xlsRow,4,"$city"); xlsWriteString($xlsRow,5,"$state"); xlsWriteString($xlsRow,6,"$zip"); xlsWriteString($xlsRow,7,"$homephone"); xlsWriteString($xlsRow,8,"$cellphone"); xlsWriteString($xlsRow,9,"$email"); xlsWriteString($xlsRow,10,"$age"); xlsWriteString($xlsRow,11,"$maritalstatus"); xlsWriteString($xlsRow,12,"$income"); xlsWriteString($xlsRow,13,"$contact1, $contact2, $contact3"); xlsWriteString($xlsRow,14,"$date1, $date3, $date3"); xlsWriteString($xlsRow,15,"$gift"); $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 xlsWriteString($Row, $Col, $Value ) { $L = strlen($Value); echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L); echo $Value; return; } // This is my Mail Code // include("class.phpmailer.php"); include("class.smtp.php"); // note, this is optional - gets called from main class if not already loaded $mail = new PHPMailer(); $body = file_get_contents('contents.html'); $body = eregi_replace("[\]",'',$body); $mail->IsSMTP(); $mail->SMTPAuth = true; // enable SMTP authentication $mail->SMTPSecure = "ssl"; // sets the prefix to the servier $mail->Host = "smtp.gmail.com"; // sets GMAIL as the SMTP server $mail->Port = 465; // set the SMTP port $mail->Username = "myuser@gmail.com"; // GMAIL username $mail->Password = "password"; // GMAIL password $mail->From = "me@me.com"; $mail->FromName = "Fund Tours Site Info"; $mail->Subject = "New Client Signup"; $mail->AltBody = "This is the body when user views in plain text format"; //Text Body $mail->WordWrap = 50; // set word wrap $mail->MsgHTML($body); $mail->AddReplyTo("me@me.com","Webmaster"); $mail->AddAttachment("file.xls"); // attachment $mail->AddAddress("myemail@address.com","First Last"); $mail->IsHTML(true); // send as HTML if(!$mail->Send()) { echo "Mailer Error: " . $mail->ErrorInfo; } else { echo "Message has been sent"; } ?>

[/php]
Oh…Thank god you’re back :). I can’t figure this out.

What it does correctly:

  1. It takes the form info submitted and inputs it into the database properly.
  2. It takes the sql database where the info is being inputed and creates an .xls file from that database.

What it is doing INcorrectly

  1. It is wanting you to download the .xls file to the users hard drive when you hit the submit button.
  2. It is not sending the attachment to the e-mail address.

What I want it to do:

  1. I want it to create the .xls file and save it to the local server (/public_html folder) overwriting the file each time.
  2. I want it to e-mail the file to the address specified. If I removed all the code ABOVE “// This is my Mail Code //” it e-mails the file to me properly. When I add the code back in it wont email again.

I am using phpmailer to send the attachment.

The big problem is that I want it to create “file.xls”, save and overwright the file in my /public_html folder and then mail it to me as an attachment.


#9

Very interesting… I have found the problem. It seems that by giving the file headers of the type you have, it means that you want to OPEN the file inside a browser, not STORE it as a file. It is a small difference. I am working on it now. But, at least I now know why it is doing what it is…

Oh, also, that is why the attachment is not working as there is no file to attach… (Same problem!)

Hope to have code for you soon…


#10

Great news! I figured it out… And, a simple fix for it.

To explain, you are sending headers to the browser. This means you are planning on sending data to the browser. And, of course, that is why you are getting the OPEN issues with the browser. You are not actually creating a file. So, to fix that, you just have to remove all the headers and save the data to a file instead of sending it to the browser. You could rewrite all your Excel code to output to a file, or just cheat using PHP code to capture the file and save it.

To do this you use PHP’s page Object. You basically create a PHP object at the beginning of the code and close it at the end of the Excel code. That is that, just a couple of lines to fix it… Here is the new version of the code which is your old version along with the added OBJ lines…

[php]
// This is my Excel File Creation Code //
mysql_connect(‘localhost’, ‘username’, ‘password’);
mysql_select_db(‘database’);
$sql = “SELECT
groupname AS Group,
name1 AS Customer Name 1,
name2 AS Customer Name 2,
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,
gift AS Gift Choice
FROM fundtour_info.clients clients”;

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

// XLS Data Cell
ob_start(); // start the file
xlsBOF();
xlsWriteString(0,0,“Group”);
xlsWriteString(0,1,“Name 1”);
xlsWriteString(0,2,“Name 2”);
xlsWriteString(0,3,“Address”);
xlsWriteString(0,4,“City”);
xlsWriteString(0,5,“State”);
xlsWriteString(0,6,“Zip Code”);
xlsWriteString(0,7,“Home Phone”);
xlsWriteString(0,8,“Cell Phone”);
xlsWriteString(0,9,“E-mail Address :”);
xlsWriteString(0,10,“Age Group”);
xlsWriteString(0,11,“Marital Status”);
xlsWriteString(0,12,“Income”);
xlsWriteString(0,13,“Contact Via”);
xlsWriteString(0,14,“Dates”);
xlsWriteString(0,15,“Gift Choice”);
$xlsRow = 2;

while(list($groupname, $name1, $name2, $address, $city, $state, $zip, $homephone, $cellphone, $email, $age, $maritalstatus, $income, $contact1, $contact2, $contact3,$date1, $date3, $date3, $gift)=mysql_fetch_row($result)) {
++$i;
xlsWriteString($xlsRow,0,"$groupname");
xlsWriteString($xlsRow,1,"$name1");
xlsWriteString($xlsRow,2,"$name2");
xlsWriteString($xlsRow,3,"$address");
xlsWriteString($xlsRow,4,"$city");
xlsWriteString($xlsRow,5,"$state");
xlsWriteString($xlsRow,6,"$zip");
xlsWriteString($xlsRow,7,"$homephone");
xlsWriteString($xlsRow,8,"$cellphone");
xlsWriteString($xlsRow,9,"$email");
xlsWriteString($xlsRow,10,"$age");
xlsWriteString($xlsRow,11,"$maritalstatus");
xlsWriteString($xlsRow,12,"$income");
xlsWriteString($xlsRow,13,"$contact1, $contact2, $contact3");
xlsWriteString($xlsRow,14,"$date1, $date3, $date3");
xlsWriteString($xlsRow,15,"$gift");
$xlsRow++;
}
xlsEOF(); // $filename should be set to some writeable location
file_put_contents($filename, ob_get_clean());
exit();
[/php]
Now basically, I removed the headers (no browser needed) and used the PHP object for storage.
Lastly, writing it to a file. So, try that and see if it writes the file to your folder where this code is. If so, FTP it back to your system and test it inside of Excel. If this works, then, let me know and we can move onto the email part… (Which actually may work now that it has a file to send!) Later…


#11

sweet…!!!

Ok it creates the file and saves it to my /public_html folder but still does not e-mail to my address. I don’t have to us phpmailer if you know a better way to do it.

What I really want it to do is email the file and then after everything is done have it take you to completed.html


#12

Okay, the file is now created… Did you copy it to your system and test it inside Excel?
Let me know if that works correctly…

Also, let’s keep the PHPmailer code since you already have it all working. I will try to figure that out…

I think we are almost there…


#13

yes i tested it inside of excel and its good.


#14

Okay, one more question… Do you need to send it by SMTP? If not, it should send without it. If you need to for some security reason, let me know… I have to rewrite the mailing code different for SMTP or NOT-SMTP…
Let me know, looks like we are almost done! Yea!


#15

I don’t need it to send smtp. I just didn’t know what was the best way with PHPMailer to do this.


#16

So, try this version of your email code… I just changed enough to make it work. I can not test it here…
Let me know… Hey, if this works, can you post all this over again for others? Just remove your connection info… SO, I don’t have to do this one again… LOL

[php]
// This is my Mail Code //

include(“class.phpmailer.php”);
include(“class.smtp.php”); // note, this is optional - gets called from main class if not already loaded

$mail = new PHPMailer();

$body = file_get_contents(‘contents.html’);
$body = eregi_replace("[]",’’,$body);

$mail->IsSMTP();
$mail->SMTPAuth = true; // enable SMTP authentication
$mail->SMTPSecure = “ssl”; // sets the prefix to the servier
$mail->Host = “ssl://smtp.gmail.com”; // sets GMAIL as the SMTP server
$mail->Port = 465; // set the SMTP port

$mail->Username = "myuser@gmail.com"; // GMAIL username
$mail->Password = “password”; // GMAIL password

$mail->From = "me@me.com";
$mail->FromName = “Fund Tours Site Info”;
$mail->Subject = “New Client Signup”;
$mail->AltBody = “This is the body when user views in plain text format”; //Text Body
$mail->WordWrap = 50; // set word wrap

$mail->IsHTML(true); // send as HTML

$mail->MsgHTML($body);

$mail->AddReplyTo("me@me.com",“Webmaster”);

$mail->AddAttachment(“file.xls”); // attachment

$mail->AddAddress("myemail@address.com",“First Last”);

if(!$mail->Send()) {
echo "Mailer Error: " . $mail->ErrorInfo;
} else {
echo “Message has been sent”;
}
?>
[/php]
Okay, of course, you have to put in your Gmail’s user name and password. I think your reply email might not be correct. It is supposed to be the users email so you can reply back to it. Since the email is sent from the website, I would think it would be the user’s email address… Not sure…

Well, test it and let me know… If any errors post them…


#17

nope…this didn’t work.

The code works by itself…but when combined with the other code it doesn’t work.


#18

What error do you get from PHP-Mailer???


#19

I don’t get an error. It just goes to a blank page.

It inputs the data into the sql table and creates the xls file but doesn’t email it. When I remove the code above “// This is my Mail Code //” its sends the attachment just fine.


#20

Im sorry that may have been a miss-type on my side… You comment out a line in PHP like this:
// some comment…

Not like this // comments… //

It seems that you shouldn’t put a comment in a comment. It will comment out everything till it sees a ?> !!!

So, just remove the last // and that should be that.

Does that mean that you have it working now? If so, let’s celebrate! LOL…