By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,534 Members | 1,807 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,534 IT Pros & Developers. It's quick & easy.

Sending E-mails through MySql

P: 16
I have a table where i enter the students details.
Whenever we insert a row in this table, a mail should be sent to the student for the student to verify the details entered.
The email id is already stored in the table.
How to implement this using mysql?

Expand|Select|Wrap|Line Numbers
  1. create table s_details(
  2.     SELECT_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  3.     REF_NUMBER VARCHAR(20),
  4.     REG_NO VARCHAR(20),
  5.     TITLE    varchar(4) NOT NULL,        
  6.     FIRST_NAME    varchar(30) NOT NULL,
  7.     MIDDLE_NAME    varchar(30),
  8.     LAST_NAME    varchar(30) NOT NULL,
  9.     DATE_OF_BIRTH    date NOT NULL,
  10.     NATIONALITY    varchar(50) ,
  11.     E_MAIL        varchar(80) NOT NULL,
  12.     DAY_NUMBER    int NOT NULL,
  13.  
  14.     )TYPE=innodb;
  15.  
This is the table...
pls can anyone help on this?
Oct 22 '07 #1
Share this Question
Share on Google+
15 Replies


ronverdonk
Expert 2.5K+
P: 4,258
Email is sent via the server language used to insert the table (such as PHP), not by MySQL.
Since you have the email details at the time you insert the new row, you can easily construct an email message and sent it from your insert-into-MySQL script.

Ronald
Oct 22 '07 #2

P: 16
Email is sent via the server language used to insert the table (such as PHP), not by MySQL.
Since you have the email details at the time you insert the new row, you can easily construct an email message and sent it from your insert-into-MySQL script.

Ronald
Can u provide me more details on this!!
Oct 23 '07 #3

codegecko
Expert 100+
P: 533
Hi praveena,

There are two ways of doing what you are asking for.

The first way is programatically - i.e. you have a section on your website where the user puts in their details and signs up. This sign-up script saves the user's information into the database using PHP, ASP or whatever server-side language you are using.
What ronverdonk is suggesting is adding a few extra bits of code into this sign-up script that automatically sends the email to the new user (which makes sense unless you are planning on adding users manually to your database using a back-end application).

The other way is by using MySQL's SELECT... INTO OUTFILE functionality.
I recently created my own user database and wanted to send automated emails to them when they signed up to verify their email address.
I worked out a trigger that:
  1. Captured the new user's details (email, name and new Unique ID to verify email);
  2. Retrieved an HTML template from a table in my database;
  3. Substituted the user's details into that template;
  4. Outputted the resulting HTML as a ".eml" file into my mail server's pickup folder, using INTO OUTFILE SQL, and it got sent automatically!
I will put up my trigger code a little later on as I don't have it to hand at present, but I hope that this helps towards your problem.

Best regards,

medicineworker
Oct 23 '07 #4

P: 3
Any chance you can post this code?

I am working on something similar.


Thanks.
Feb 16 '08 #5

codegecko
Expert 100+
P: 533
Finally found the code in my code-bank!

Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER send_emailverifier AFTER INSERT, UPDATE ON tbl_users
  2. FOR EACH ROW BEGIN
  3. SELECT * FROM email_bodies WHERE EmailID = 1;
  4. SELECT * FROM tbl_users WHERE ClientID = @ClientID
  5. INSERT INTO tbl_emailverify VALUES (UUID, tbl_users.ClientID, OLD.CltEmail, NEW.CltEmail)
  6. SELECT concat("To: ",NEW.CltEmail & "," & OLD.CltEmail),
  7.                 "From: triggers@yourmysqlserver.whatever",
  8.                 concat("Subject: ",NEW.subject),
  9.                 "",
  10.                 email_bodies.EmailContent
  11. INTO OUTFILE "/inetpub/mailroot/pickup/mail.eml"
  12. FIELDS TERMINATED BY '\r\n';
  13. END
  14.  
I will be altering this function to accommodate more name-value substitutions into the email body as well as turning it into a stored procedure instead of a single trigger - that way it has far more benefit across a DB deployment.

medicineworker
Feb 17 '08 #6

P: 4
If you are using PHP as a server side scripting language use mail function of PHP to send an e-mail
Expand|Select|Wrap|Line Numbers
  1. syntax:
  2. bool mail ( string to, string subject, string message [, string additional_headers [, string additional_parameters]] )
  3.  
  4. example:
  5. <?php
  6.  
  7. $body_of_mail = "Line 1\nLine 2\nLine 3";
  8. $receivers_email_id="xyz@abc.com";
  9. $subject="mail";
  10. mail($body, $subject, $message);
  11.  
  12. ?>
Feb 18 '08 #7

ronverdonk
Expert 2.5K+
P: 4,258
If you are using PHP as a server side scripting language use mail function of PHP to send an e-mail

syntax:
bool mail ( string to, string subject, string message [, string additional_headers [, string additional_parameters]] )

example:
<?php

$body_of_mail = "Line 1\nLine 2\nLine 3";
$receivers_email_id="xyz@abc.com";
$subject="mail";
mail($body, $subject, $message);

?>
Ok, but how do you propose to accomplish that via the MySQL TRIGGER? Show us that code please.

Ronald
Feb 18 '08 #8

P: 19
May be this help ful to u...
Expand|Select|Wrap|Line Numbers
  1.  Dim msg As EnhancedMailMessage = New EnhancedMailMessage
  2.   msg.From = "xt@x.com" '"invoice@x.com" 
  3.                 msg.FromName = "Administraor"
  4.                 msg.To = "nitya@x.com"
  5.                 msg.Subject = "Order with [" + transid + "]: "
  6.                 msg.Body = lblmail.Text + lblmsg.Text
  7.                 msg.BodyFormat = MailFormat.Html
  8.                 msg.SMTPServerName = "mail.x.com" '"mail.x.com"
  9.                 msg.SMTPUserName = "x@x.com" '"invoice@x.com"
  10.                 msg.SMTPUserPassword = "xxxx" 
  11.                 msg.SMTPServerPort = 25
  12.                msg.Send()
Feb 18 '08 #9

ronverdonk
Expert 2.5K+
P: 4,258
May be this help ful to u...

Dim msg As EnhancedMailMessage = New EnhancedMailMessage
msg.From = "xt@x.com" '"invoice@x.com"
msg.FromName = "Administraor"
msg.To = "nitya@x.com"
msg.Subject = "Order with [" + transid + "]: "
msg.Body = lblmail.Text + lblmsg.Text
msg.BodyFormat = MailFormat.Html
msg.SMTPServerName = "mail.x.com" '"mail.x.com"
msg.SMTPUserName = "x@x.com" '"invoice@x.com"
msg.SMTPUserPassword = "xxxx"
msg.SMTPServerPort = 25
msg.Send()
Annh how do you trigger that from MySQL using the TRIGGER function?

Ronald
Feb 18 '08 #10

P: 3
<SNIP>
INTO OUTFILE "/inetpub/mailroot/pickup/mail.eml"
Do you realize that MS officially says that you should NOT create directly in the pickup folder but rather to a temp folder and then MOVE the file into /pickup?

This guarantees that the file will not be picked up before it is complete.
http://technet.microsoft.com/en-us/library/aa998408(EXCHG.65).aspx

Do you have an idea as to how the trigger could accomplish this "requirement?" Can MySQL perform file operations or somehow invoke them?


I will be altering this function to accommodate more name-value substitutions into the email body as well as turning it into a stored procedure instead of a single trigger - that way it has far more benefit across a DB deployment.
I would enjoy seeing this.

You also mentioned concatenating with an HTML file. That would be great to see as well.
Feb 19 '08 #11

codegecko
Expert 100+
P: 533
Hi fbachofner,

It would be possible to alter the above code to create a generic "send mail" function, passing parameters in to, from, subject and message body. Even an attachment in binary format could be considered.

However, it wouldn't be possible to create a generic substitution function (to replace {name} with your user's name from your DB table, or {email} with user's email etc.) as this is effectively limitless - you'd have to create a name-value pair array, and SQL doesn't support arrays to my knowledge.

Your next question - creating the file in a temp folder and then moving it - is not possible using SQL, full-stop. MySQL can only do file read and write, not more complex operations - unless the workaround would be to create the email body, save it to temp, then re-load it in MySQL and re-save to the mail pickup folder, then save a blank "" to the temp file to save space.

The best way forward would be:
1) MySQL to implement a "send mail" function akin to Oracle and Microsoft SQL Server, failing that;
2) Use a programming language - whether web or desktop-based - to send your mail and do your file operations, failing that;
3) Use the above code sparingly!

medicineworker

P.S. To CodeMaster123 and nityaprashant, please read - your suggestions are PHP and ASP, this thread is about using SQL (specifically MySQL Server) to send mail without PHP or ASP!
Feb 26 '08 #12

P: 2
Mr. codegecko please tell me what few codes may be added to front end application to send the email after adding data in database (MySQL)
Nov 27 '09 #13

nbiswas
100+
P: 149
Try this

Advanced email in PHP
Dec 4 '09 #14

nbiswas
100+
P: 149
Try this

Advanced email in PHP
Dec 4 '09 #15

P: 1
Instead of using php, if you own a VPS or Dedicated server Then you can go through this video on youtube and code a module for yourself

https://www.youtube.com/watch?v=Zm2pKTW5z98 (Send Email from MySQL 5.7 on Linux)
Mar 4 '16 #16

Post your reply

Sign in to post your reply or Sign up for a free account.