Member Avatar for munik.bell

I'll appreciate any assistance on this. The scenario;

I have a database table with the following sample records;

data1-------------dt2----data3------data4--------data5------data6
Brewing Plant-----2------10000------11/3/2012----1136-------person1@yahoo.com
Brewing Plant-----2------10000------11/3/2012----1136-------person1@yahoo.com
Car Plant---------1------5000-------11/3/2012----568--------person1@yahoo.com
Brewing Plant-----1------5000-------11/3/2012----568--------person2@yahoo.com
Car Plant---------4------20000------11/3/2012----2272-------person2@yahoo.com
Brewing Plant-----3------15000------11/3/2012----1704-------person3@yahoo.com

This is my requirement:
I want just one notification email each sent to theses various persons with their information. Note that summations were done before the emails were sent; e.g;

Person1 should receive an email like

Data1-------------dt2----data5
Brewing Plant-----4------2272
Car Plant---------1------568

while Person2 should receive an email like

Data1-------------dt2---data5
Brewing Plant-----1-----568
Car Plant---------4-----2272

and so on.

I am using classic ASP and MySQL

Thanks for your anticipated assistance.

Edited by munik.bell because: added lines
  • 2 Contributors
  • 9 Replies
  • 138 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by munik.bell
Member Avatar for JorgeM

So which part to do you need help on, building the SQL query, implementing the actual email message, or including the SQL results in the email body, or all of the above?

Here is a reference you can take a look at with regard to sending email from ASP:
Sending Email Using CDOSYS in ASP

Member Avatar for munik.bell

It's the SQL query to generate the results and how to include it in the body of the email.

Below is how I send emails:

set mailobj = server.CreateObject("Persits.MailSender")
mailobj.Host = "mail.emailbroadcast.name"
mailobj.Port = 2525

mailbody="<html>------</html>"

mailobj.From = "global@emailbroadcast.name"
mailobj.FromName = "Emailbroadcast"

mailobj.AddAddress "Address"
mailobj.Subject = "Subject"

mailobj.Body = mailbody

mailobj.Send 
set mailobj =nothing
Member Avatar for JorgeM

So, when a user submits something on the page, I assume you will be sending that user an email. You would have to perform a lookup of the information in SQL once you have information about the users. Based on the information you provided, the user info is the email address, so your SQL query will look something like this:

SELECT data1, SUM(dt2) as dt2, SUM(data5) from table_1
WHERE data6='person1@yahoo.com'
GROUP BY data1

You would take those results and build your HTML body, probably by using an HTML table. You can assign this HTML to a variable, then reference that variable in your mail procedure.

mailbody="<html>------</html>" becomes mailbody = message

How you connect to your datasource and take the results and build an HTML table depends..you probably need help from ADO.

I also have various ADO tutorials that can help you with that:

ADO Tutorials
ADO Displaying Data in a Table

I hope this helps...

Member Avatar for munik.bell

Thanks JorgeM,

The query was partially helpful. When i run it i get result like this

Data1-------------dt2---data5-----email 
Brewing Plant-----1-----568-------person1@yahoo.com  
Car Plant---------4-----2272------person1@yahoo.com

With this I'll still have to send the email twice as the email address appears two times, I'd have wished it appeared just once so when I run the email script to send the email it picks the email just once:

Member Avatar for JorgeM

Ok so it appears that the SQL query you are using is not the same as the one I provided since I did not include the email (data6) column. Simple remove that column from your select statement.

You do not need to include the email address in the results. You should already know who you are sending the email to since its included in the SQL query (based on the example I gave you).

It could be that there is more information that you have that I do not know about so I am providing general guidance, not the actual solution.

In any event, your goal is to build the body of the email with the sql query results and send that email one time to the recepient.

Member Avatar for munik.bell

Yes, there are thousands of records like this with many different emails(data6) having many different Data1s, just like there is a person2 and so on. And I need to send just one email to each recipient containing all details of data1 with its corresponding sum of dt2 and sum of data5.

Member Avatar for munik.bell

Yes, there are thousands of records like this with many different emails(data6) having many different Data1s, just like there is a person2 and so on. And I need to send just one email to each recipient containing all details of data1 with its corresponding sum of dt2 and sum of data5.

Member Avatar for JorgeM

Yes, I understand. Again, your SQL code needs to include the WHERE clause so that your results are for the individual with the email you are targetting. Using ADO to connect to the data source, you will then receive one or more results from that SQL query. With the results, you build a table and assign that HTML table to the mailbody variable in your code.

Member Avatar for munik.bell

Thanks, understood

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.