• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1148
  • Last Modified:

Daily export MySQL reports to email

I have an ecommerce site that currently allows me to run transaction reports on demand.

However, I want to have the reports run automatically, every day at 0100am.

I imagine this could be done with a crontab and some kind of query to pull the relevant data from the MySQL database, format it into a csv file, then send the csv as an attachment to a specified email address.

Is there a software tool that will allow me to do all of the above?

Or is there a relatively easy way to implement it with a crontab set to execute a script that will do that?

I have very limited programming skills so it would be great to find an existing script that I could adapt to  my site. Thanks in advance!

ps. my site is hosted on a unix VPS, administrated via Plesk. Please let me know if more info is required
0
Okyanet
Asked:
Okyanet
  • 2
  • 2
2 Solutions
 
K VDatabase ConsultantCommented:
Simply:--

SELECT * INTO OUTFILE '/tmp/result.txt'  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'  FROM TABLE_NAME;


Mail data :--

cat /tmp/result.txt | mail -s "Hi" "mail-id@host.ext"
0
 
K VDatabase ConsultantCommented:
You may use mutt to send file as an attachment.

mutt -s "mail-subject" -a /tmp/result.txt mail-id@host.ext  < mail-body-file.txt
0
 
lesouefCommented:
I doubt any admin will let you play with the crontab of his server.
but you can set this on your own machine which will execute the relevant php script on site to receive the file or email you wish (receiving the file is easier to me)
down there a script to download a query as a tabbed txt file:
<?php
require_once ('db_connect.php') ; // to connect to the db
$query = "SELECT fi_id, fi_email, fi_receivers, fi_dir, fi_filename, fi_date from table ;
$query_result = mysql_query($query);
if ($query_result == true)
{	while ($row = mysql_fetch_assoc($query_result)) 
	{ $text .=  $row['fi_id'].'	'.$row['fi_date'].'	'.$row['fi_email'].'	'.$row['fi_receivers'].'	'.$row['fi_dir'].'	'.$row['fi_filename'].'	'.$fsize.'	'.$row['fr_date']."\n";
	}
	mysql_free_result($query_result);
	header("Content-type: application/force-download");
	header('Content-Transfer-Encoding: Binary');
	header("Content-disposition: attachment; filename=trucklog.txt");
	header("Content-Size: " . strlen($text));
	header("Content-Length: " . strlen($text));
	echo $text ;
}
?>

Open in new window

0
 
lesouefCommented:
split in 2
0
 
OkyanetAuthor Commented:
Thanks guys!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now