sending an email with an attachment csv file in php

I have a dead line to generate CSV and send as Email attachment in PHP. I picked up a sample code that I am trying to use but its not working.

<?php
include ('/var/www/config.php');
$con = mysqli_connect(DB_HOST,DB_USER, DB_PASSWORD, DB_DATABASE);
if (!$con){
  die('Could not connect: ' . mysqli_connect_error());
}

function create_csv_string($data){
	
	$headerQuery="
	select
      COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = 'rofr'
      and TABLE_NAME LIKE 'AerialAssetTracker_v2'
	  ";
	$resultHeader = mysqli_query($con,$headerQuery);
	while ($row = mysqli_fetch_row($resultHeader)){
		  $header[] = $row[0];
	}
	
    // Open temp file pointer
    if (!$fp = fopen('php://temp', 'w+')) return FALSE;
	fputcsv($fp,$header);

    $query="
	SELECT * 
	from AerialAssetTracker_v2 
	where RequiredCompletionDate <> '0000-00-00'
	";
	$data = mysqli_query($con,$query);
	
	// Loop data and write to file pointer
	while ($line = mysqli_fetch_assoc($data)){		
      fputcsv($fp, $line);
	}
	
    // Place stream pointer at beginning
    rewind($fp);

    // Return the data
    return stream_get_contents($fp);

}

function send_csv_mail($csvData, $body, $to = 'REDACTED@REDACTED.au', $subject = 'Website Report', $from = 'noreply@REDACTED.au') {

    // This will provide plenty adequate entropy
    $multipartSep = '-----'.md5(time()).'-----';

    // Arrays are much more readable
    $headers = array(
        "From: $from",
        "Reply-To: $from",
        "Content-Type: multipart/mixed; boundary="$multipartSep""
    );

    // Make the attachment
    $attachment = chunk_split(base64_encode(create_csv_string($csvData))); 

    // Make the body of the message
    $body = "--$multipartSep\r\n"
        . "Content-Type: text/plain; charset=ISO-8859-1; format=flowed\r\n"
        . "Content-Transfer-Encoding: 7bit\r\n"
        . "\r\n"
        . "$body\r\n"
        . "--$multipartSep\r\n"
        . "Content-Type: text/csv\r\n"
        . "Content-Transfer-Encoding: base64\r\n"
        . "Content-Disposition: attachment; filename="AerialAssetReport-" . date("F-j-Y") . ".csv"\r\n"
        . "\r\n"
        . "$attachment\r\n"
        . "--$multipartSep--";

    // Send the email, return the result
    return @mail($to, $subject, $body, implode("\r\n", $headers)); 

}

//$array = array(array(1,2,3,4,5,6,7), array(1,2,3,4,5,6,7), array(1,2,3,4,5,6,7));

send_csv_mail($data, "Aerial Asset Report \r\n \r\n REDACTED.au");

?>

Open in new window

adokliAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gr8gonzoConsultantCommented:
What is the problem? Saying it doesn't work doesn't give us any useful information to go on
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris StanyonWebDevCommented:
Turn on error reporting, run your script and report back the results! Add this to the top of you script:

error_reporting(E_ALL);
ini_set('display_errors', 1);

Open in new window


First thing I notice is this line:

send_csv_mail($data, ...

What is $data - it's not set anywhere in your page.
0
Julian HansenCommented:
Why not use the PHPMailer library - it is pretty much standard in the PHP world and it handles all the difficult stuff for you.

https://github.com/PHPMailer/PHPMailer

The install instructions specify you need composer to install but you don't have to do that

Simply download these files to a vendor/phpmailer folder
PHPMailer.php
Exceptions.php
SMTP.php
You will find these in the SRC folder.

Then create your file like this
<?php
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

require_once('vendor/phpmailer/PHPMailer.php');
require_once('vendor/phpmailer/SMTP.php');
require_once('vendor/phpmailer/Exception.php');

Open in new window


You can then follow the sample on the above link
// Setup PHPMailer and the SMTP authorisation
$mail = new PHPMailer();
$mail->isSMTP();
$mail->Host = 'your.smtpserver.com';
$mail->SMTPAuth = true;
$mail->Username = 'smtp_username';
$mail->Password = 'smtp_password';
$mail->SMTPSecure = 'tls';
$mail->Port = 587;        
// Now the message
$mail->setFrom('from@example.com', 'Mailer');
$mail->addAddress('joe@example.net', 'Joe User');
$mail->addReplyTo('info@example.com', 'Information');
// The following only if needed
$mail->addCC('cc@example.com');
$mail->addBCC('bcc@example.com');

//Attachments
$mail->addAttachment('/var/tmp/file.csv');

//Content
$mail->isHTML(true);
$mail->Subject = 'Here is the subject';
$mail->Body    = 'This is the HTML message body <b>in bold!</b>';
$mail->AltBody = 'This is the body in plain text for non-HTML mail clients';

$mail->send();

Open in new window

0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

gr8gonzoConsultantCommented:
Alright, finally got a moment to look at this.

So there are several problems here. Starting at the top:

1.  This line in create_csv_string function:
$resultHeader = mysqli_query($con,$headerQuery);

Open in new window

...will always fail, because the database connection resource, $con, is not within the scope of the create_csv_string function. So within that function, $con is an undefined variable. The quick fix here is to add:

global $con; to the top of your function:
function create_csv_string($data){
        global $con; // <-- Makes the $con variable from the global scope available in the current function
        $headerQuery="

Open in new window


2. Next, you don't even need a separate query to pull the column names from the info schema. When you do a mysqli_fetch_assoc() call, you will have your column names as your array keys, which is also a more reliable way to access them, since they're tightly coupled to your data. So try this function instead:

function create_csv_string($data){

  global $con;

  // Open temp file pointer
  if (!$fp = fopen('php://temp', 'w+')) return FALSE;

  $query = "SELECT * from AerialAssetTracker_v2 WHERE RequiredCompletionDate <> '0000-00-00'";
  $data = mysqli_query($con,$query);

  // Loop data and write to file pointer
  $writtenHeader = false;
  while ($line = mysqli_fetch_assoc($data))
  {
    if(!$writtenHeader)
    {
      fputcsv($fp, array_keys($line));
      $writtenHeader = true;
    }
    fputcsv($fp, $line);
  }

  // Place stream pointer at beginning
  rewind($fp);

  // Return the data
  return stream_get_contents($fp);
}

Open in new window


3. You have an unescaped quote mark in your mail headers:
"Content-Type: multipart/mixed; boundary="$multipartSep""

Open in new window

Should be:
"Content-Type: multipart/mixed; boundary=\"$multipartSep\""

Open in new window


4. And another in your body:
"Content-Disposition: attachment; filename="AerialAssetReport-" . date("F-j-Y") . ".csv"\r\n"

Open in new window

Should be:
"Content-Disposition: attachment; filename=\"AerialAssetReport-" . date("F-j-Y") . ".csv\"\r\n"

Open in new window


5. Finally, don't suppress the errors from your mail() call with @ - you won't be able to tell if something failed. So instead of "return @mail", just "return mail" so you can see any errors that come from the mailer routine.

Make those updates and see what comes out of it.
1
Julian HansenCommented:
I subscribe to the global is bad camp - but I am not fanatical about it.

My preference is Dependency injection so I would recommend the following
function create_csv_string($con, $data){
 ...
}

Open in new window

and then call it with the first parameter being the $con value.
1
Chris StanyonWebDevCommented:
+1 for DI :)
0
gr8gonzoConsultantCommented:
Ideally, database-driven scripts like this are object-oriented so the connection is passed around by a property, but I wanted to keep the changes to a minimum. Passing around the resource is fine, but he'd also have to pass it around twice since create_csv_string is called by the send_csv_mail function, so $con would have to be passed to send_csv_mail first, and then send_csv_mail would have to pass it to create_csv_string.

Hence the global suggestion. I'm with you on the "global is bad" idea in general, though.
0
Chris StanyonWebDevCommented:
he'd also have to pass it around twice

That's exactly what is already happening with the $data (no idea why - it's never actually used anywhere).

Ideally, the SendMail function should have nothing to do with creating the CSV at all:

$data = GetCsvData($dbConn);
$sent = SendEmail($data, "Some Subject Line");
0
adokliAuthor Commented:
Thanks so much. Your comments really fixed the problem.
0
Julian HansenCommented:
Glad you are working but I would advise taking time to look at PHPMailer - there is not much merit in doing the nuts and bolts of sending attachments with email when there is a tried and tested library that does it for you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.