Mysql data to Excel is ok but how to email it as attachement. Help

[irkevin]

Hi all,

 

as you can see from the title, Extracting data from Mysql and output it in Excel file is fine.

 

But i need to send it to an email address as Attachement.

 

Can someone point me in the right direction or give me some advice?

 

Thanks 

[Maq]

Amazing, I typed your problem description in Google and found this link in 2 seconds...

[irkevin]

I tried it but it didn't work, or maybe i didnt apply it well...

 

this is what im using

 

<?php
$host = 'myhost';
$user = 'username';
$pass = 'password';
$db = 'dbname';
$table = 'tablename';

//$id = $_GET['id'];

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("Select * from $table");

$header = "<table border='0' cellpadding='0' cellspacing='0'>";
$header .= "<tr><td width=\"300\" style=\"border:1px solid #000000; color:#0099CC\">Menu Name</td>";

while ($row = @mysql_fetch_array($result)) {
     $header .= "<td>". $row["name"] . "</td>";
}

$header .= "</tr>";
$header .= "</table>";

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: inline; filename=members.xls");
echo $header;
exit;

$to = "irkevin@hotmail.com";
$subject = 'Test email with attachment'; 
$fileattname = "members.xls";
$fileatttype = "application/vnd.ms-excel ";

$semi_rand = md5( time() ); 
        $mime_boundary = "==Multipart_Boundary_x{$semi_rand}x"; 
    
        $headers .= "\nMIME-Version: 1.0\n" . 
                    "Content-Type: multipart/mixed;\n" . 
                    " boundary=\"{$mime_boundary}\"";
    
        $message = "This is a multi-part message in MIME format.\n\n" . 
                "--{$mime_boundary}\n" . 
                "Content-Type: text/plain; charset=\"iso-8859-1\"\n" . 
                "Content-Transfer-Encoding: 7bit\n\n" . 
                $message . "\n\n";
    
        $data = chunk_split( base64_encode( $data ) );
                 
        $message .= "--{$mime_boundary}\n" . 
                 "Content-Type: {$fileatttype};\n" . 
                 " name=\"{$fileattname}\"\n" . 
                 "Content-Disposition: attachment;\n" . 
                 " filename=\"{$fileattname}\"\n" . 
                 "Content-Transfer-Encoding: base64\n\n" . 
                 $data . "\n\n" . 
                 "--{$mime_boundary}--\n"; 


        if( mail($to, $subject, $message, $headers ) ) {
         
            echo "<p>The email was sent.</p>"; 
         
        }
        else { 
        
            echo "<p>There was an error sending the mail.</p>"; 
         
        }
    
?>

 

I get the file to open with the correct data, but it never send me a mail.. The email add is ok. zats my email address. Whats wrong with it

[irkevin]

Ok im using this now

 

<?php
$host = 'hostname';
$user = 'username';
$pass = 'password';
$db = 'databasename';
$table = 'tablename';

//$id = $_GET['id'];

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("Select * from $table");

$header = "<table border='0' cellpadding='0' cellspacing='0'>";
$header .= "<tr><td width=\"300\" style=\"border:1px solid #000000; color:#0099CC\">Menu Name</td>";

while ($row = @mysql_fetch_array($result)) {
     $header .= "<td>". $row["name"] . "</td>";
}

$header .= "</tr>";
$header .= "</table>";

//header("Content-type: application/vnd.ms-excel");
//header("Content-Disposition: inline; filename=members.xls");
echo $header;
//exit;

//define the receiver of the email
$to = 'irkevin@hotmail.com';
//define the subject of the email
$subject = 'Test email with attachment';
//create a boundary string. It must be unique
//so we use the MD5 algorithm to generate a random hash
$random_hash = md5(date('r', time()));
//define the headers we want passed. Note that they are separated with \r\n
$headers = "From: webmaster@example.com\r\nReply-To: webmaster@example.com";
//add boundary string and mime type specification
$headers .= "\r\nContent-Type: multipart/mixed; boundary=\"PHP-mixed-".$random_hash."\"";
$data = chunk_split(base64_encode($data));
$headers .= "Content-Disposition: attachment;\n" . 
                 "filename=\"{$fileattname}\"\n" . 
                 "Content-Transfer-Encoding: base64\n\n" . 
                 $data . "\n\n" . 
                 "--{$mime_boundary}--\n";
                 
        $message .= "Hi";
                  
$mail_sent = @mail($to,$subject,$headers,$message);
//if the message is sent successfully print "Mail sent". Otherwise print "Mail failed"
echo $mail_sent ? "Mail sent" : "Mail failed";
?>

 

echo $header; <---- this actually gives me whats in the excel file. thats good, I receive email now but with no attachement at all what am i doing wrong?

[irkevin]

Finally I've been able to receive email with the attached excel, but when I open it, i don't see the data ive been expected, instead i see "Members.xls" written.

 

Here is the code. Help me plz

 

<?php
$host = 'hostname';
$user = 'username';
$pass = 'password';
$db = 'databasename';
$table = 'tablename';

//$id = $_GET['id'];

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("Select * from $table");

$header = "<table border='0' cellpadding='0' cellspacing='0'>";
$header .= "<tr><td width=\"300\" style=\"border:1px solid #000000; color:#0099CC\">Menu Name</td>";

while ($row = @mysql_fetch_array($result)) {
     $header .= "<td>". $row["name"] . "</td>";
}

$header .= "</tr>";
$header .= "</table>";

//header("Content-type: application/vnd.ms-excel");
//header("Content-Disposition: attachement; filename=members.xls");
//echo $header;
//exit;

//define the receiver of the email
$to = 'irkevin@hotmail.com';
//define the subject of the email
$subject = 'Test email with attachment';
//create a boundary string. It must be unique
//so we use the MD5 algorithm to generate a random hash
$random_hash = md5(date('r', time()));
//define the headers we want passed. Note that they are separated with \r\n
$headers = "From: webmaster@example.com\r\nReply-To: webmaster@example.com";
//add boundary string and mime type specification
$headers .= "\r\nContent-Type: multipart/mixed; boundary=\"PHP-mixed-".$random_hash."\"";
$attachment = chunk_split(base64_encode('members.xls'));
//define the body of the message.
ob_start(); //Turn on output buffering
?>
--PHP-mixed-<?php echo $random_hash; ?> 
Content-Type: multipart/alternative; boundary="PHP-alt-<?php echo $random_hash; ?>"

--PHP-alt-<?php echo $random_hash; ?> 
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hello World!!!
This is simple text email message.

--PHP-alt-<?php echo $random_hash; ?> 
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

<h2>Hello World!</h2>
<p>This is something with <b>HTML</b> formatting.</p>

--PHP-alt-<?php echo $random_hash; ?>--

--PHP-mixed-<?php echo $random_hash; ?> 
Content-type: application/vnd.ms-excel; name="members.xls" 
Content-Transfer-Encoding: base64 
Content-Disposition: attachment 

<?php echo $attachment; ?>
--PHP-mixed-<?php echo $random_hash; ?>--

<?php
//copy current buffer contents into $message variable and delete current output buffer
$message = ob_get_clean();
//send the email
$mail_sent = @mail( $to, $subject, $message, $headers );
//if the message is sent successfully print "Mail sent". Otherwise print "Mail failed"
echo $mail_sent ? "Mail sent" : "Mail failed"; 
?>

[irkevin]

well i think this can't be done  !

 

Thanks anyways

[premiso]

Where do you write to the file "members.xls" ?

 

I see where you pull some data and attach a file, but I fail to see where the file is populated with data?

 

That is probably your main issue is that you are not creating the members.xls file so it is just passing you an empty file. If I am missing something let me know.

[irkevin]

what should I add to the code? Im really lost with this

[premiso]

Well what I would first is think of what you want to be in the excel file. What columns from the table?

 

Do you just want a list of names, so just 1 column with multiple rows?

 

If so I am sure something like this would work:

 

<?php
// create the file on the server and populate the data
$export_file = "members.xls";
$fp = fopen($export_file, "wb");
if (!is_resource($fp))
{
die("Cannot open $export_file");
}
$host = 'hostname';
$user = 'username';
$pass = 'password';
$db = 'databasename';
$table = 'tablename';

//$id = $_GET['id'];

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("Select * from $table");

while ($res = mysql_fetch_assoc($result)) {
    $data[] = $res['name'];
}

fwrite($fp, serialize($data));
fclose($fp);
?>

 

I am not to good with the attachment and email part, but that will get the file, members.xls created and populated with data on your server.

 

I would run that and then open up the member.xls and check that it has the expected data in it before trying to email it as an attachment.

[irkevin]

it actually put a file called member.xls on the server, but the data is written like this

 

a:2:{i:0;s:5:"Kevin";i:1;s:6:"thomas";}