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?
 
gr8gonzoConsultantCommented:
What is the problem? Saying it doesn't work doesn't give us any useful information to go on
0
 
Chris StanyonCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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 StanyonCommented:
+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 StanyonCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.