Create an excel document and have it emailed monthly

[bolton]

Hi

I have a database table with a few hundred records in it, and likely to grow.

How can I get all these records into an excel file and have it automatically emailed monthly.

Thanks

[Martyr2]

Have you checked out the fputcsv function? You can simply read your database (using mysqli or PDO) and then for each row, write them to the excel file using fputcsv. (Excel can use csv files in case you didn’t know)

Should be fairly straight forward.

[bolton]

Thanks. Ill take a look at that, looks quite straightforward.

How would I then get that to be emailed monthly? Would I need to save it to disk (somewhere on the server) and then send? Or would it not need to be saved first? I presume Id need to setup a Cron job? (Ive never done this before).

[Gandalf]

@bolton

I presume Id need to setup a Cron job?

Yes. You’ll need to check with your host how to set up a cron job. It would be as well to save the spreadsheet before sending it.

[rpkamp]

Alternatively you can have a look at https://packagist.org/packages/phpoffice/phpspreadsheet if you want to create a real Excel file with formatting and colors and stuff.

[bolton]

Im just working on a Hello World example. I have the csv file being written to, but when its emailed its just a blank csv. My code:

$list = array (
    array('a3333', 'bbb', 'ccc', 'dddd'),
    array('123', '456', '789'),
    array('"aaa"', '"bbb"')
);

$fp = fopen('file.csv', 'w');

foreach ($list as $fields) {
    fputcsv($fp, $fields);
}

fclose($fp);


$recipient = '#######;
$mailer = new PHPMailer();
$mailer->Host = "###";
	$mailer->SMTPAuth = true;
	$mailer->Username = "####";
	$mailer->Password = "####";
	$mailer->setFrom('###', '###');
	$mailer->AltBody = "To view the message, please use an HTML compatible email viewer";

	$email_body = compile_html_email_template('email-template.php', $data);
	$mailer->addAddress($recipient);
	$mailer->msgHTML($email_body);
	$mailer->Subject = $data['subject'];
$mailer->AddStringAttachment($fp, 'file.csv');
	$mailer->send();
	$mailer->clearAllRecipients();

Something to do with the $mailer->AddStringAttachment($fp, ‘file.csv’); ?

[Gandalf]

@bolton

AddStringAttachment

Are you sure that’s the right thing for a file attachment? I don’t think I’ve ever sent an attachment, so would need to check.

Edit: I think it should be addAttachment

[bolton]

LOL. You beat me to it @Gandalf - I just realised! Doh!

OK, so I know how to setup a cron job, and I know how to generate a csv file and email it.

The last piece in the puzzle is to get the rows from my table.

It needs to be something like:

$sql = "SELECT * FROM  clinicians";
$q = $conn->prepare($sql);
$q->execute();

$fp = fopen('file.csv', 'w');

$list = array (
    array('Name', 'role', 'email'),
while ($row = $q->fetch()) { 
    array(populate with name, populate with role, populate with email),
);

fclose($fp);

So it the array within an array that Im stuck on. (if that makes sense)

[Gandalf]

I may have misunderstood, but I don’t think you want an array. Just output the file line by line - each line being a row from your database with cells separated by a comma and if need be encapsulated (probably with a double-quote).

[bolton]

Ah I see. Yes. Ill give that a go.

[rpkamp]

You’d want something like this

// Please don't use SELECT *, it's a bad practice
// If these columns are incorrect, please correct them, and correct them
// below too in $row
// Also, I've added ORDER BY Name to get a nicely sorted list,
// rather than random ordering
$sql = "SELECT Name, role, email FROM  clinicians ORDER BY Name";
$q = $conn->prepare($sql);
$q->execute();

$fp = fopen('file.csv', 'w');

// ['Name', 'role', 'email'] is the same as array('Name', 'role', 'email'),
// but shorter. Works in PHP 5.6+.
fputcsv($fp, ['Name', 'role', 'email']);

while ($row = $q->fetch()) { 
    fputcsv($fp, array($row['Name'], $row['role'], $row['email'));
);

fclose($fp);

If you want to change the separator for CSV, please see the manual on how to do that: fputcsv.

[bolton]

Works like a charm - thanks so much guys!