How to export mysql database to excel and send this file to email without phpmailer

Question

Please help me to make this function.. when the data is retrieved and managed into a file but not download the file, but the file to be sent to the email (attachment). but do not wear phpmailer ..

Answer 1

hmm, of what i can figure out, you will be using the standard php mail function. make sure your server supports that.

(if you are using Apache, make sure sendmail is installed, configured and enabled)

just FYI, sending an attachement with mail() is much more work than including phpmailer and going with the flow. anyhow,

function mail_you_do_you_m8($path, $filename, $from_name, $from_mail, $replyto)
$file = $path.$filename;
$content = file_get_contents( $file);
$content = chunk_split(base64_encode($content));
$uid = md5(uniqid(time()));
$name = basename($file);

// headers
$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";

// email a body with attachement
$nmessage = "--".$uid."\r\n";
$nmessage .= "Content-type:text/plain; charset=iso-8859-1\r\n";
$nmessage .= "Content-Transfer-Encoding: 7bit\r\n\r\n";
$nmessage .= $message."\r\n\r\n";
$nmessage .= "--".$uid."\r\n";
$nmessage .= "Content-Type: application/octet-stream; name=\"".$filename."\"\r\n";
$nmessage .= "Content-Transfer-Encoding: base64\r\n";
$nmessage .= "Content-Disposition: attachment; filename=\"".$filename."\"\r\n\r\n";
$nmessage .= $content."\r\n\r\n";
$nmessage .= "--".$uid."--";

if (mail($mailto, $subject, $nmessage, $header)) {
    return true; 
} else {
  return false;
}

}

now of my understanding you are able to get everything in a csv file already, you just could not send it.. correct? otherwise, i can help you with the extraction from mysql server.

also: no hassle way to get an excel with proper columns and formatting is to use an html table and open it in excel.. i've done that a couple times for a no-fee excel export and people seemed to enjoy it.. csv confuses non tech savvy folks :)

Answer 2

Export mysql database to excel

<?php
/*******EDIT LINES 3-8*******/
$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "username"; //MySQL Username     
$DB_Password = "password";             //MySQL Password     
$DB_DBName = "databasename";         //MySQL Database Name  
$DB_TBLName = "tablename"; //MySQL Table Name   
$filename = "excelfilename";         //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
//create MySQL connection   
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
//execute query 
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");    
//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    }   
?>

Send this file to email without phpmailer

To send an attachment with mail() function is way harder than you expect. You sound like you're rejecting it because you want the easier option. Trust me, PHPMailer is the easier option by a very large margin compared to trying to do it yourself with PHP's built-in mail() function. PHP's mail() function really isn't very good.

To use PHPMailer:

• Download the PHPMailer script from here: http://github.com/PHPMailer/PHPMailer
• Extract the archive and copy the script's folder to a convenient place in your project.
• Include the main script file -- require_once('path/to/file/class.phpmailer.php');

Now, sending emails with attachments goes from being insanely difficult to incredibly easy:

$email = new PHPMailer();
$email->From      = 'you@example.com';
$email->FromName  = 'Your Name';
$email->Subject   = 'Message Subject';
$email->Body      = $bodytext;
$email->AddAddress( 'destinationaddress@example.com' );

$file_to_attach = 'PATH_OF_YOUR_FILE_HERE';

$email->AddAttachment( $file_to_attach , 'NameOfFile.xls' );

return $email->Send();

It's just that one line $email->AddAttachment(); -- you couldn't ask for any easier.

If you do it with PHP's mail() function, you'll be writing stacks of code, and you'll probably have lots of really difficult to find bugs.

Ressources :

• Export MySQL data to Excel in PHP
• Send attachments with PHP Mail()?