Sending an attachment via mail()

[greens85]

Hi all,

 

I currently have some code which when the page is loaded up prompts the user to save a CSV file:

 

PHP Code:

 

<?php
    // THIS EXPORTS ONE TABLE AT A TIME
    $host = 'localhost';
    $user = 'my_user';
    $pass = 'my_pass';
    $db = 'my_database';
    $table = 'my_tbl';
    $file = 'jobseekers_export';

    $link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
    mysql_select_db($db) or die("Can not connect.");

    $result = mysql_query("SHOW COLUMNS FROM ".$table."");
    $i = 0;
        if (mysql_num_rows($result) > 0) {
            while ($row = mysql_fetch_assoc($result)) {
    $csv_output .= $row['Field'].", ";
    $i++;
}
}
    $csv_output .= "\n";

    $values = mysql_query("SELECT * FROM ".$table."");
        while ($rowr = mysql_fetch_row($values)) {
            for ($j=0;$j<$i;$j++) {
    $csv_output .= $rowr[$j].", ";
}
    $csv_output .= "\n";
}

    $filename = $file."_".date("Y-m-d_H-i",time());
    header("Content-type: application/vnd.ms-excel");
    header("Content-disposition: csv" . date("Y-m-d") . ".csv");
    header( "Content-disposition: filename=".$filename.".csv");
    print $csv_output;
    exit;
?>

 

What I would like to do, is have this file emailed rather than having to actually visit a URL to save it. I know the basics of the mail() function, but I have no idea how I would use it to get this file to send as an attachment.

 

I have setup a Cron job on the server, which deals with the issue of automatically sending the email, if only I could get the file to be an attachment all would be well.

 

I have Googled this, but found no good tutorials, at least never find one I could make any sense of.

 

Would anyone be willing to advise?

 

Many Thanks,

 

Greens85

[jskywalker]

run PHP from commandline..

 

$ php yourscript.php >filename.csv

 

(you obviously need small changes to your code for it to work this way, i.e. you dont need the headers..)

[greens85]

Hi,

 

Firstly, many thanks for the reply...

 

Sorry I'm not too advanced with PHP, I don't really get what you mean by run PHP from the command line? Any chance you could 'dumb things down' to explain it to me?

 

I get what you mean by I will no longer need the headers, as they are only there to prompt me to save the file when I visit the URL.

[jskywalker]

when you visit the URL, your webserver start the script using PHP

 

when you run the script from commandline, you do not have to visit the URL, but in stead you type

 

(linux)

$ php yourscript.php >filename.csv

(windows)

C:\>php yourscript.php >filename.csv

 

this will run PHP which executes your script. output will be sent (without any further interaction) to 'filename.csv'

 

you could extend the cronjob you created to let it firstly create the csv-file, before mailing it...

 

[greens85]

when you run the script from commandline, you do not have to visit the URL, but in stead you type

 

(linux)

$ php yourscript.php >filename.csv

(windows)

C:\>php yourscript.php >filename.csv

 

this will run PHP which executes your script. output will be sent (without any further interaction) to 'filename.csv'

 

Presumably if I have to type the above into the commandline each time I want to export the csv, this would mean that the process would not be automatic (which is what I am trying to achieve)? 

 

you could extend the cronjob you created to let it firstly create the csv-file, before mailing it...

 

Or am I mistaken and this is where the process becomes automated? I'm a newbie, so please forgive my lack of knowledge... are you saying here that there is a command that can be placed in the cron job that will create the csv file & email me it? If so, would you be kind enough to advise how I can code this cron job?

 

Many thanks

[jskywalker]

in your 1st post in this thread:

I have setup a Cron job on the server, which deals with the issue of automatically sending the email, if only I could get the file to be an attachment all would be well.

 

What do you mean with that, ?

esp. when reading:

Or am I mistaken and this is where the process becomes automated? I'm a newbie, so please forgive my lack of knowledge... are you saying here that there is a command that can be placed in the cron job that will create the csv file & email me it? If so, would you be kind enough to advise how I can code this cron job?

in your (now) last post.....

 

[greens85]

Apologizes, I will try and make myself clearer...

 

At present I have a script called admin_export.php, consisting of the following PHP:

 

<?php
// THIS EXPORTS ONE TABLE AT A TIME
$host = 'localhost';
$user = 'my_user';
$pass = 'my_pass';
$db = 'my_db';
$table = 'jobseekers';
$file = 'jobseekers_export';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
	if (mysql_num_rows($result) > 0) {
		while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].", ";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");
	while ($rowr = mysql_fetch_row($values)) {
		for ($j=0;$j<$i;$j++) {
$csv_output .= '"'.$rowr[$j].'",'; 
}
$csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
print $csv_output;	
?>

 

If I navigate to that URL, the page prompts me to save the file... which if then opened in excel will contain the result of the query.

 

I have a cron job which looks like so:

 

php -q /path/to/file/admin_export.php

 

So this is obviously running the script above when specified... however the problem is at present when the cron runs I get an email from 'Cron Daemon' which displays all the results in the body of the email...

 

What I want to happen:

 

The cron to run as it is, but rather than having to visit the URL and be prompted to download the file, when the cron runs I want to recieve an email which attaches the CSV to the email.

 

I hope this makes some kind of sense

 

Many thanks

 

[jskywalker]

php -q /path/to/file/admin_export.php >/tmp/export.csv
mailx -a /tmp/export.csv -s"This is a subject" youremail@example.com

[greens85]

Hey tried that but I dont seem to be getting any emails through 

[jskywalker]

When you type that last line, do you get an error? or 'nothing' happens....

(and, Did you check your logfiles for errors?)

[greens85]

I don't get an error from the cron job, it returns a green tick... so I would assume it uis fine with the syntax.

 

One thing that might be worth noting tho, is that my temp file is one directory up from the location of admin_export.php... does this mean that the lines /tmp/export.csv should actually read ../tmp/export.php or does it make no difference?

 

As for logs I can't seem to find them, but we are hosted through a provider rather than our own server, so perhaps I dont have the permissions?

[jskywalker]

I'm using linux for quit some time now, and i've never seen a 'green tick'.....

 

I think question about sending mail, or failing to send mail, should go to the linux forum...?

[greens85]

When I say green tick, I'm talking about within cpanel...

 

Our website is hosted on a linux server and everything is accessed through cpanel...

 

I've attached a screen shot to demo what I mean...

 

Hope this helps

 

[attachment deleted by admin]

[jskywalker]

Do i see a SPACE between the "-" and the "a" in "mailx -a export.csv ...." ??

 

cpanel...(sigh), never used that, its like using Notepad.exe to control Windows...

[greens85]

You do, I tried it without but still nothing...

 

I have however gotten it working with this:

 

<?php
// THIS EXPORTS ONE TABLE AT A TIME
$host = 'host';
$user = 'user';
$pass = 'pass';
$db = 'db';
$table = 'table';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
	if (mysql_num_rows($result) > 0) {
		while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].", ";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");
	while ($rowr = mysql_fetch_row($values)) {
		for ($j=0;$j<$i;$j++) {
$csv_output .= '"'.$rowr[$j].'",'; 
}
$csv_output .= "\n";
}

$csv_filename = "subcities.csv";
$open = fopen($csv_filename, "w");
fwrite ($open, "$csv_output");
fclose ($open);

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

    $mail = new PHPMailer(); // defaults to using php "mail()"
    //$body = file_get_contents('contents.html');
$body = "Please find attached the daily SQL backup!";
    $body = eregi_replace("[\]",'',$body);
    $mail->AddReplyTo("noreply@educationvacancies.com","No Reply");
    $mail->SetFrom('autobackup@educationvacancies.com', 'Auto Backup'); 
    $mail->AddReplyTo("noreply@educationvacancies.com","No Reply");
    $address = "dgreenwell@educationvacancies.com";
    $mail->AddAddress($address, "David Greenwell");
    $mail->Subject = "SQL Backup";
    $mail->AltBody = "To view the message, please use an HTML compatible email viewer!"; // optional, comment out and test
    $mail->MsgHTML($body);
     
    $mail->AddAttachment("subcities.csv");
    
    if(!$mail->Send()) {
        echo "Mailer Error: " . $mail->ErrorInfo;
    } else {
        echo "Message sent!";
    }
?> 

 

I've then got a script & cron like this for each one of my tables, I know it's a bit clunky but it works which is a good starting point.