Create CSV from MySQL database and attach to PHPMailer email

Question

I am trying to make a php file that will create a CSV file of my database and attach it to a PHPMailer email that will automatically send. The file will be on a webserver on a raspberry pi. I got the file to create the CSV file and send an email separately, but not attach the CSV to email and send correctly. Any help would be greatly appreciated! Here is the code so far:

<?php

    require("connect2.php");

    $filename = "hoursandpay.csv";

  header('HTTP/1.1 200 OK');
  header('Content-Type: text/csv; charset=utf-8');  
  header('Content-Disposition: attachment; filename=$filename');  
  header('Cache-Control: no-cache, no-store, must-revalidate');
  header('Pragma: no-cache');
  header('Expires: 0');
  $output = fopen($filename, 'w');  
  fputcsv($output, array('ID', 'PIN', 'FNAME', 'LNAME', 'DATE'));  
  $query = "SELECT * FROM hoursandpay ORDER BY DATE DESC";  
  $result = mysqli_query($conn, $query);  
  while($row = mysqli_fetch_assoc($result))  
  {  
       fputcsv($output, $row);  
  } 

  require_once('PHPMailer_5.2.0/class.phpmailer.php');

  $mail = new PHPMailer();
  $mail->IsSMTP();
  $mail->SMTPAuth = true;
  $mail->SMTPSecure = 'ssl';
  $mail->Host = "smtp.gmail.com";
  $mail->Port = 465;
  $mail->Username = "[email protected]";
  $mail->Password = "XXXXXXXX";
  $mail->SetFrom('[email protected]', 'Test123');
  $mail->Subject = "Hours and Pay CSV File";
  $mail->MsgHTML('Hi! <br><br> Here is the Hours and Pay datatable. 
  <br><br> Thanks!');
  $mail->AddAddress('[email protected]', 'Test User');

  $mail->AddAttachment($filename);
  unlink($filename);

  $mail->Send();

  fclose($output); 

  ?>

Answer 1

You’re using an old version of PHPMailer, and even on rpi you should be running a recent enough version of PHP to allow you to use PHPMailer 6.x.

You’re deleting the file you want to send before the send happens. addAttachment stores the details of the file, but not its content, which is not read until it’s actually sent. So move the unlink call to after your send call.