473,322 Members | 1,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Sending E-mails through MySql

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
15 81432
ronverdonk
4,258 Expert 4TB
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
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
JamieHowarth0
533 Expert 512MB
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
Any chance you can post this code?

I am working on something similar.


Thanks.
Feb 16 '08 #5
JamieHowarth0
533 Expert 512MB
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
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
4,258 Expert 4TB
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
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
4,258 Expert 4TB
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
<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
JamieHowarth0
533 Expert 512MB
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
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
149 100+
Try this

Advanced email in PHP
Dec 4 '09 #14
nbiswas
149 100+
Try this

Advanced email in PHP
Dec 4 '09 #15
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

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

Similar topics

1
by: coder_1024 | last post by:
I'm trying to send a packet of binary data to a UDP server. If I send a text string, it works fine. If I attempt to send binary data, it sends a UDP packet with 0 bytes of data (just the...
0
by: praba kar | last post by:
Dear All, I have doubt regarding mail sending smtplib module. The below code is I used to send a mail. ########################################## import email.Message import email.Utils...
3
by: Robert A. van Ginkel | last post by:
Hello Fellow Developer, I use the System.Net.Sockets to send/receive data (no tcpclient/tcplistener), I made a receivethread in my wrapper, the receivethread loops/sleeps while waiting for data...
4
by: yaron | last post by:
Hi, I have a problem when sending data over TCP socket from c# client to java server. the connection established ok, but i can't send data from c# client to java server. it's work ok with...
3
by: Ant | last post by:
Hi, I'm using the MailMessage & smtpMail classes in System.Web.Mail to send mail, however it's not sending any emails. I'm using it on a Windows 2003 server. The simplest way to use this is...
3
by: Sydney | last post by:
Hi, I am trying to construct a WSE 2.0 security SOAP request in VBScript on an HTML page to send off to a webservice. I think I've almost got it but I'm having an issue generating the nonce...
0
by: remya1000 | last post by:
by using FTP i can send files to server using vb.net. if the file is big, then it will take some time to complete the sending process to server.or if we were sending 3-4 files to the server one by...
9
by: JoeP | last post by:
Hi All, How can I find the reason for such an error: Failure sending mail. Some Code... oMailMessage.IsBodyHtml = False oMailMessage.Body = cEmailBody Dim oSMTP As New SmtpClient...
4
by: =?Utf-8?B?R3V5IENvaGVu?= | last post by:
Hi all I use: Dim message As New MailMessage(txtTo.Text, txtFrom.Text, txtSubject.Text, txtBody.Text) Dim emailClient As New SmtpClient(txtSMTPServer.Text) emailClient.Send(message) And its...
6
by: Chocolade | last post by:
Hi, Im using System.Net.Mail to send email in my application it was working great without any problems untill this morning after like 20-30 tries it was sending the email ok then suddenly this...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.