Sending E-mails through MySql

[praveena mani]

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?

create table s_details(
    SELECT_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    REF_NUMBER VARCHAR(20),
    REG_NO VARCHAR(20),
    TITLE    varchar(4) NOT NULL,        
    FIRST_NAME    varchar(30) NOT NULL,
    MIDDLE_NAME    varchar(30),
    LAST_NAME    varchar(30) NOT NULL,
    DATE_OF_BIRTH    date NOT NULL,
    NATIONALITY    varchar(50) ,
    E_MAIL        varchar(80) NOT NULL,
    DAY_NUMBER    int NOT NULL,
 
    )TYPE=innodb;
 

This is the table...
pls can anyone help on this?

[ronverdonk]

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

[praveena mani]

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!!

[JamieHowarth0]

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

[fbachofner]

Any chance you can post this code?

I am working on something similar.


Thanks.

[JamieHowarth0]

Finally found the code in my code-bank!

CREATE TRIGGER send_emailverifier AFTER INSERT, UPDATE ON tbl_users
FOR EACH ROW BEGIN
SELECT * FROM email_bodies WHERE EmailID = 1;
SELECT * FROM tbl_users WHERE ClientID = @ClientID
INSERT INTO tbl_emailverify VALUES (UUID, tbl_users.ClientID, OLD.CltEmail, NEW.CltEmail)
SELECT concat("To: ",NEW.CltEmail & "," & OLD.CltEmail),
                "From: [e-mail]",
                concat("Subject: ",NEW.subject),
                "",
                email_bodies.EmailContent
INTO OUTFILE "/inetpub/mailroot/pickup/mail.eml"
FIELDS TERMINATED BY '\r\n';
END
 

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

[CodeMaster123]

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="[e-mail]";
$subject="mail";
mail($body, $subject, $message);
 
?>

[ronverdonk]

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="[e-mail]";
$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

[nityaprashant]

May be this help ful to u...

 Dim msg As EnhancedMailMessage = New EnhancedMailMessage
  msg.From = "[e-mail]" '"[e-mail]" 
                msg.FromName = "Administraor"
                msg.To = "[e-mail]"
                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 = "[e-mail]" '"[e-mail]"
                msg.SMTPUserPassword = "xxxx" 
                msg.SMTPServerPort = 25
               msg.Send()

[ronverdonk]

May be this help ful to u...

Dim msg As EnhancedMailMessage = New EnhancedMailMessage
msg.From = "[e-mail]" '"[e-mail]"
msg.FromName = "Administraor"
msg.To = "[e-mail]"
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 = "[e-mail]" '"[e-mail]"
msg.SMTPUserPassword = "xxxx"
msg.SMTPServerPort = 25
msg.Send()

Annh how do you trigger that from MySQL using the TRIGGER function?

Ronald

[fbachofner]

<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.

[JamieHowarth0]

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!

[Plzhelp]

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)

[nbiswas]

Try this

Advanced email in PHP

[nbiswas]

Try this

Advanced email in PHP

[miniweb]

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)