?
Solved

Daily export MySQL reports to email

Posted on 2010-01-13
6
Medium Priority
?
1,146 Views
Last Modified: 2013-12-17
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
Comment
Question by:Okyanet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 21

Assisted Solution

by:K V
K V earned 186 total points
ID: 26303694
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
 
LVL 21

Expert Comment

by:K V
ID: 26303947
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
 
LVL 28

Accepted Solution

by:
lesouef earned 189 total points
ID: 26304737
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
 
LVL 28

Expert Comment

by:lesouef
ID: 26511799
split in 2
0
 

Author Closing Comment

by:Okyanet
ID: 31676605
Thanks guys!
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The basic steps you have just learned will be implemented in this video. The basic steps are shown to configure an Exchange DAG in a live working Exchange Server Environment and manage the same (Exchange Server 2010 Software is used in a Windows Ser…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question