LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 12-23-2011, 03:54 PM   #1
drazenmd
LQ Newbie
 
Registered: Dec 2011
Posts: 6

Rep: Reputation: Disabled
Send email from shell to users in Mysql


Hi, I need help about sending mail in linux. I have a ubuntu server with working web site, users are uploading files in one main directory (to folder inbox). With samba I shared that directory and on other mashine is Windows server and application that is reading that files and writting content to database. Based of the result is creating new file with content: package name that is recieved, size, date and time and does the package contained erros or it was OK. That newly created files are again sent over samba to the same directory in ubuntu server (to folder outbox). That all is working!

Now, i need to listen are there new files in folder outbox. If there are new files, I need to send their content to specific email address. I have users in Mysql database. The name of files that are coming to outbox is with mask like this ddmmyyyy.x where x is user id in Mysql. So if "extension" of file is 1, I need to send mail to mail address of user in mysql with user id 1. If its 2 I am sending to user with user id... and after that move file from outbox to archive.

So, it's going like this: new file arrived in outbox, based on the extension of that file, select email address of user from database with user id = extension of file. On that email address send content of that file and move file to archive.

Does anyone can help me to solve this. Thank you!

P.S I have a mail working and I can send email from shell.
 
Old 12-24-2011, 09:08 AM   #2
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 17,960

Rep: Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693
Quote:
Originally Posted by drazenmd View Post
Hi, I need help about sending mail in linux. I have a ubuntu server with working web site, users are uploading files in one main directory (to folder inbox). With samba I shared that directory and on other mashine is Windows server and application that is reading that files and writting content to database. Based of the result is creating new file with content: package name that is recieved, size, date and time and does the package contained erros or it was OK. That newly created files are again sent over samba to the same directory in ubuntu server (to folder outbox). That all is working!

Now, i need to listen are there new files in folder outbox. If there are new files, I need to send their content to specific email address. I have users in Mysql database. The name of files that are coming to outbox is with mask like this ddmmyyyy.x where x is user id in Mysql. So if "extension" of file is 1, I need to send mail to mail address of user in mysql with user id 1. If its 2 I am sending to user with user id... and after that move file from outbox to archive.

So, it's going like this: new file arrived in outbox, based on the extension of that file, select email address of user from database with user id = extension of file. On that email address send content of that file and move file to archive. Does anyone can help me to solve this. Thank you! P.S I have a mail working and I can send email from shell.
Since you've got everything working, writing this shell script should be trivial for you. I'd use inotify to watch the directory, then send messages/move files from there. There are also other tools that watch directories for changes, too, but inotify is what I tend to use.

Some examples:
http://fileschanged.sourceforge.net/
http://stackoverflow.com/questions/4...ectory-changes
https://github.com/rvoicilas/inotify-tools/wiki/

Few examples in those links to get you started. Once you know the new file(s) have arrived, you can look at the extension, and do a mysql select from the DB, and grab the address of that user. This all depends, though, on how many files per day you're talking about, how many users in the DB, etc. If you're talking about files coming in at hundreds per minute, and a huge customer DB, that will obviously change your solution.

I tend to write everything in Perl these days. Perl has inotify available as a module, and hooks into MySQL VERY well.
http://blogs.perl.org/users/smash/20...new-files.html

Last edited by TB0ne; 12-24-2011 at 09:12 AM.
 
Old 12-24-2011, 12:58 PM   #3
drazenmd
LQ Newbie
 
Registered: Dec 2011
Posts: 6

Original Poster
Rep: Reputation: Disabled
I done this so far, I start script manualy and it going through a directory. If there is file with extension I want I move it to archive and calling a php file that is sending mail to that user. Problem is here that I start script manualy and I second problem is that file I include in php like a message in mail i define in code, it's not text from file that called that code. I don't know how to solve this because I am begginer in linux and not so good in shell programming. Here is my code:


#!/bin/sh
# Configure variables at the begining of the script
MONITOR_DIR=/var/www/frep/predaja2
TMP_DIR=/home/tmp
SUCCESS_DIR=/home/t_arhiva
FAILED_DIR=/home/g_arhiva
cd $MONITOR_DIR
COUNT_FILES=$(ls -l * | grep ^- | wc -l)
if [ $COUNT_FILES -gt 0 ]; then
echo "A total of" $COUNT_FILES "file(s) found.";

for f in *.562; do
php /var/www/mail/mail.php
echo "Moving file:" $f "to:" $TMP_DIR/$PROCCESS_ID_DIR;
mv $f $TMP_DIR/$PROCCESS_ID_DIR
mv $f $SUCCESS_DIR/
done
else
echo "No files found... Exit!";
fi

***mail.php***


<?php
//orderid = 777;
$dbhost = 'localhost';
$dbuser =’’xxx;
$dbpass = 'xxxxxxx';
$dbname = 'xxxx';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(!$conn) { die('Failed to connect to server: ' . mysql_error()); }
mysql_select_db($dbname);
$query=mysql_query("SELECT email FROM users WHERE userID = '777'");
$query_row=mysql_fetch_array($query);
$str= file_get_contents("d13121101.562");
$proba = ($query_row[email]);
$to = $proba;
$subject = "Test mail";
$message = $str;
$from = "info@xxx.net";
$headers = "From:" . $from;
mail($to,$subject,$message,$headers);
echo "Mail Sent.";
?>
 
Old 12-25-2011, 09:18 AM   #4
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 17,960

Rep: Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693Reputation: 3693
Quote:
Originally Posted by drazenmd View Post
I done this so far, I start script manualy and it going through a directory. If there is file with extension I want I move it to archive and calling a php file that is sending mail to that user. Problem is here that I start script manualy and I second problem is that file I include in php like a message in mail i define in code, it's not text from file that called that code. I don't know how to solve this because I am begginer in linux and not so good in shell programming. Here is my code:


#!/bin/sh
# Configure variables at the begining of the script
MONITOR_DIR=/var/www/frep/predaja2
TMP_DIR=/home/tmp
SUCCESS_DIR=/home/t_arhiva
FAILED_DIR=/home/g_arhiva
cd $MONITOR_DIR
COUNT_FILES=$(ls -l * | grep ^- | wc -l)
if [ $COUNT_FILES -gt 0 ]; then
echo "A total of" $COUNT_FILES "file(s) found.";

for f in *.562; do
php /var/www/mail/mail.php
echo "Moving file:" $f "to:" $TMP_DIR/$PROCCESS_ID_DIR;
mv $f $TMP_DIR/$PROCCESS_ID_DIR
mv $f $SUCCESS_DIR/
done
else
echo "No files found... Exit!";
fi

***mail.php***


<?php
//orderid = 777;
$dbhost = 'localhost';
$dbuser =’’xxx;
$dbpass = 'xxxxxxx';
$dbname = 'xxxx';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(!$conn) { die('Failed to connect to server: ' . mysql_error()); }
mysql_select_db($dbname);
$query=mysql_query("SELECT email FROM users WHERE userID = '777'");
$query_row=mysql_fetch_array($query);
$str= file_get_contents("d13121101.562");
$proba = ($query_row[email]);
$to = $proba;
$subject = "Test mail";
$message = $str;
$from = "info@xxx.net";
$headers = "From:" . $from;
mail($to,$subject,$message,$headers);
echo "Mail Sent.";
?>
You're very close. You can easily put the MySQL code into your bash script, and not bother with the php program. Try these:
Code:
This will get you the result from MySQL for the user's email address:
emailresult=$(mysql -u $dbuser $dbname -sN -e “select email FROM users WHERE userID = `<some value>`”)

...then just use the mail command to send your email:
mail -s "<Subject goes here>" -r <from address> -a <filename to attach> <user ID to send to>
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
automatically send an email to new users....? oru Slackware 2 09-14-2007 03:56 AM
Qmail: send email from shell mikeshn Linux - General 1 09-30-2004 02:19 AM
send email through shell mikeshn Linux - General 10 01-01-2004 03:35 PM
how to allow send email just to users of my linux box ? jfmendez27 Linux - Software 2 11-13-2003 10:28 AM
How to allow selected users to send a email to a particular IP xanthium Linux - Networking 2 11-05-2002 09:49 AM


All times are GMT -5. The time now is 04:54 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration