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_head ers [, string additional_para meters]] )

example:
<?php

$body_of_mail = "Line 1\nLine 2\nLine 3";
$receivers_emai l_id="xyz@abc.c om";
$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 EnhancedMailMes sage = New EnhancedMailMes sage
msg.From = "[e-mail]" '"[e-mail] "
msg.FromName = "Administra or"
msg.To = "[e-mail] m"
msg.Subject = "Order with [" + transid + "]: "
msg.Body = lblmail.Text + lblmsg.Text
msg.BodyFormat = MailFormat.Html
msg.SMTPServerN ame = "mail.x.com " '"mail.x.com "
msg.SMTPUserNam e = "[e-mail]" '"[e-mail] "
msg.SMTPUserPas sword = "xxxx"
msg.SMTPServerP ort = 25
msg.Send()

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

Ronald