Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Send query results via email

Status
Not open for further replies.
Kozimoto

Kozimoto

Technical User
Sep 25, 2002
44
US
I'm searching for a way to send individualized emails to subscribers much the same way DICE.com, or any job listing site does, when a user selects their criteria and frequency to receive "their job listings" on a regular basis.

I'm looking for code examples, MX extensions, or any solution that a mere mortal could implement.

Thanks for any and all replies!
 
Sort by date Sort by votes
Kozimoto, are you using any kin of server-side scripting, like Coldfusion, asp, php?? If so, in CF you can simply query the dB for any searchwords that match the users criteria, and then using <cfmail> send out emails notifying them of any new items/changes.


____________________________________
Just Imagine.
 
Upvote 0 Downvote
GUJUm0deL

I'm using ASP and JavaScript if that helps. I'd like to send out the mail using CDONTS or ASPmail... Also, I'd like to automate this so a query would run nightly, query each users &quot;notify me&quot; criteria, then have the system create an HTML email with links to the matches found using their stored criteria. Also, just to minimize the admin involved, have this service last for a specific period of time, then the system would inform the user via email that their search is about to expire and invite them back.

It's one of those things where you know what you want the darn thing to do, but just can't figure out how to get it done. Thanks very much for your post though.
 
Upvote 0 Downvote
depends what is in your URL search string and if u have restricted access to pages based on login or some other criteria...if it is a fully public site then just grab the URL after you perfored the querry...gab it into a form element or something and then send it with CDONTS

&quot;Also, I'd like to automate this so a query would run nightly&quot;
&quot;Cheep way&quot; of doing it is by shedulling a
Start-->Run--> with resutls and CDONTS

there is some info on:


Take care!

> need more info?
:: don't click HERE ::
 
Upvote 0 Downvote
Here is ASP script that Dumps a query to excel. You could mod Response.ContentType="application/vnd.ms-excel" to "text/html" and format output as plain text as you require.

Sub QueryToExcel(strSQL, strConnString)
'Response.Buffer=true
Response.ContentType="application/vnd.ms-excel"
'-- insert your database connection code here
'response.write sql
'response.end()
set rs=server.createobject("adodb.recordset")
rs.open strSQL, strConnString, 3, 3
totalfields=rs.fields.count
'-- I hate typing html, so I'll set this stuff to a variable
rowstart="<tr>"
rowend="</tr>"
cellstart="<td valign=top>"
cellend="</td>"
response.write "<html><body>"
response.write "<table border='1' bordercolor='#000000' cellpadding='2' cellspacing='0' width='100%'>" & rowstart
' Write Header Row
For each fld in rs.fields
response.write cellstart & "<b>" & fld.name & "</b>" & cellend
Next
response.write rowend
If not rs.eof then
'arrAllData=rs.getrows
totalrecs=rs.recordcount
End If
If totalrecs=0 then
response.write rowstart & "<td colspan=" & totalfields & ">No data was returned" & cellend & rowend
Else
While Not rs.EOF
rowtext = ""
response.write rowstart
For Each fld In rs.Fields
If IsNull(fld.value) Then
rowdata = ""
Else
' You can strip out unwanted chars in data here if you want
' rowdata = replace(fld.value,chr(13) & chr(10)," ")
rowdata = fld.value
End If
response.write cellstart & rowdata & cellend
Next
response.write rowend
rs.MoveNext
Wend
End If
response.write "</table>"
response.write "</body></html>"

rs.close
set rs=nothing

End Sub

 
Upvote 0 Downvote
again....send the ULR that will perform the querry ones the users hits it....unless ur using URL-rewrites and your site is not restriced something in the line of
and that is it.
You dont want to send htmls but rather refer to them...
HTH

> need more info?
:: don't click HERE ::
 
Upvote 0 Downvote
Lebisol,

Thanks for the reply. I use URL parameters for query links like AUTOS, click the link and it will pull up all AUTOS in the results page. Great for that.

I have a table called tbl_user_notify where users will store their query parameters, so basically I need a page that will loop through each user, run a query using their stored parameters from the tbl_user_notify table, and send the results of each query to the email addess found in the user table.

 
Upvote 0 Downvote
Hi Kozimoto!
I think your principal will still stand just filter the recordset/table that contains users info and their querries/results much like u have done.
so, to access the page- in line of:
WHERE UserdID ="URLvar" GROUP BY userID or whatever applies
to start the 1st user

and this page will return some results from table where u would set them to paramaters (the ones to be used in StoredProc)

IF RS <> EOF
varSearchWord=RS.Field.Item("SearhWord").Value
varSearchCat=RS.Field.Item("SearhCategory").Value
....
then use the same variables to pull into procedure
Proc.execute() '
then diplay results (hint: END IF


this will give u ALL users...
but I would suggest u prime this starting page with a login page as you would not want "other users" to see what searches are beign performed...your choice
HTH
All the best!

> need more info?
:: don't click HERE ::
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

fdgsogc
  • Locked
  • Question
Displaying results of an SQL Pivot Query
Replies
1
Views
678
jdhilljr
jdhilljr
Ceilicat
  • Locked
  • Question
Query of Queries Union Not Totaling Across Fields
Replies
2
Views
372
OneTrickPony
OneTrickPony
malibu65k
  • Locked
  • Question
Show a button on a page based on query results
Replies
1
Views
241
okiiyama
okiiyama
redoakhg
  • Locked
  • Question
SQL Query Help 1
Replies
5
Views
338
redoakhg
redoakhg
mptwoadmin
  • Locked
  • Question
Update form from query preload?
Replies
1
Views
251
LyndonOHRC
LyndonOHRC

Part and Inventory Search

Sponsor

Back
Top