Creating excel from mysql and sending this as attachment email in php

Question

I need to send an email with attachment, and that attachment file contain some data fetched from mysql database at the same time.

That problem is already asked and described here but there is no any working answer. Can anyone have solution, than please answer.

   while( $row = mysql_fetch_row( $sqlQuery ) )
            {
            $line = '';
                foreach( $row as $value )
                {                                            
                    if ( ( !isset( $value ) ) || ( $value == "" ) )
                    {
                    $value = "\t";
                    }
                    else
                    {
                    $value = str_replace( '"' , '""' , $value );
                    $value = '"' . $value . '"' . "\t";
                    }
                $line .= $value;
                }
            $data .= trim( $line ) . "\n";
            }
            $data = str_replace( "\r" , "" , $data );

            $cho = "$header\n$data";
            echo $cho;

$headers = "From:[email protected](PFC Web Admin) \r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: application/vnd.ms-excel";
$headers .= 'Content-Disposition: attachment; filename=Test.xls' . "\r\n";
$headers  .= "Content-Transfer-Encoding: base64\r\n";
$headers  .= "".$cho."\r\n";
$subject = 'Record November';

   $mail = mail( '[email protected]', $subject, $msg, $headers );
   if( $mail == true )
   {
      echo "Message successfully sent";
   }
   else
   {
      echo "Message could not be sent";
   }

Excel is creating and have proper data but I need to mail this rather than download.

KIKO Software · Answer 1 · 2014-10-16T12:46:54.540

0

I don't see any attempt in your code to make a Excel file or an email with an attachment. I see those as two seperate questions.

When it comes to email attachments, it's best to use something that already exists, like:

http://swiftmailer.org

or

https://github.com/Synchro/PHPMailer

(order does not suggest anything)

For Excel the same thing applies, because with the CSV or XML versions of Excel files I always have problems. I have no suggestions though. I did find this more general site:

http://webdeveloperplus.com/php/5-libraries-to-generate-excel-reports-in-php/

edited Oct 16 '14 at 12:46

answered Oct 16 '14 at 10:51

KIKO Software

15,283
3
18
33

That is the problem, I need one solution for both the questions. – Param Kumar Oct 16 '14 at 11:08
I don't think you will find that. You need two solutions for two problems. With one solution you make the Excel file, with the other you email it. Putting stuff together, to make it do what you want, is the basis of programming. – KIKO Software Oct 16 '14 at 12:31

score 0 · Answer 2 · answered Oct 16 '14 at 11:31

Try this,it's working

<?php
include_once('inc/dbConnect.inc.php'); 
error_reporting(E_ERROR); 
$sql = mysql_query("SELECT * FROM tablename");
$row=mysql_fetch_assoc($sql);
$filename='temp/'.$filename.'.csv';
$fp=fopen($filename,"w");
$seperator="";

$comma="";

foreach($row as $name =>$value)

{

$seperator.=$comma.''.str_replace('','""',$name);
$comma=",";

}
$seperator.="\n";
 $seperator;

fputs($fp,$seperator);

mysql_data_seek($sql,0);

while($row=mysql_fetch_assoc($sql))

{

$seperator="";

$comma="";
foreach($row as $name =>$value)

{

$seperator.=$comma.''.str_replace('','""',$value);
$comma=",";

}

$seperator.="\n";
fputs($fp,$seperator);

}

fclose($fp);
$my_file = $filename.'.csv';
$path = "temp/";


$from_name = "hhhhh";

$from_mail = "[email protected]";

$mailto = "[email protected]";

$subject = "This is a mail with attachment.";

$message = "Hi,\r\n do you got attachment?\r\n\r\Hara";

$replyto="[email protected]";
$file = $path.$my_file;
    $file_size = filesize($file);


$handle = fopen($file, "r");

$content = fread($handle, $file_size);

fclose($handle);

$content = chunk_split(base64_encode($content));

$uid = md5(uniqid(time()));

$name = basename($file);

 $header = "From: ".$from_name." <".$from_mail.">\r\n";

$header .= "Reply-To: ".$replyto."\r\n";
    $header .= "MIME-Version: 1.0\r\n";

 $header .= "Content-Type: multipart/mixed; boundary=\"".$uid."\"\r\n\r\n";

$header .= "This is a multi-part message in MIME format.\r\n";

$header .= "--".$uid."\r\n";

$header .= "Content-type:text/plain; charset=iso-8859-1\r\n";

$header .= "Content-Transfer-Encoding: 7bit\r\n\r\n";

$header .= $message."\r\n\r\n";
    $header .= "--".$uid."\r\n";

$header .= "Content-Type: application/octet-stream; name=\"".$filename."\"\r\n"; // use different content types here

$header .= "Content-Transfer-Encoding: base64\r\n";

$header .= "Content-Disposition: attachment; filename=\"".$filename."\"\r\n\r\n";
    $header .= $content."\r\n\r\n";

$header .= "--".$uid."--";

mail($mailto, $subject, "", $header) 

?>

Your code is very nice @Hara and working almost proper but that attachment file coming empty in the mail, Can You tall me any suggestion for that. — Param Kumar, Oct 18 '14 at 09:19
I have checked my query, it is fine. generating data into temp folder but do not mailing this file. It is mailing another file name prefix with "temp_" which is blank, can u help me out — Param Kumar, Oct 18 '14 at 12:12
check the filename you are passing as a parameter.Do let me know if you are still facing any issue else send me your code to my email i'll check and get back to you. — Hara Prasad, Oct 18 '14 at 12:55

Creating excel from mysql and sending this as attachment email in php

2 Answers2