I've got a web-hosted MySQL database which collects subscription
information for my email newsletter (such as email address, name,
city, etc.)  I would like to find some kind of code or app that
watches a particular table in this MySQL database and emails me with
new content that gets added to it. Specifically, when someone joins
the list (adding a new record to the 'subscribedusers' table), this
code/app would email me the field information in their record.

---

Request for Question Clarification by endo-ga on 09 Jun 2003 20:04 PDT

Hi,

I could write such a program for you in Java, it would just
periodically query the database, every 10 minutes for example,
counting the number of records each time, when a new record is added,
it is then easy to retrieve it and e-mail you the data. You can then
run the Java app as a service under Windows.

If Java is acceptable for you, I just need more details about the
table you want queried, i.e. what colums are there and what are the
types of the data? If your table also stored the date at which the
entry was created, that would make things easier. Also any more
relevant information would be appreciated.

Looking forward to working on this.

Thanks.
endo

---

Clarification of Question by jhabley-ga on 09 Jun 2003 20:12 PDT

Hi Endo - thanks... sounds like a reasonable solution. To answer your
questions:

COLUMNS:
 -- Email address
 -- Encrypted password
 -- Subscription status (text, either "subscribed" or "pending")
 -- Date subscribed (in this format: 20030609200258 for 8:02:58, June
9, 2003)
 -- First name
 -- How they heard about my newsletter (text)
 -- Country
 -- City
 -- Category (a digit between 0 to 8)

Does this help?

Two questions back for you:

1. Would this be a NT/XP service, or a minimized application that
would have to launch at startup?

2. Would I need any special Java installation, or would the Sun
downloaded version work?

---

Clarification of Question by jhabley-ga on 09 Jun 2003 20:14 PDT

Sorry, one more question:

3. Would the applet be customizable in any way, either through a
simple UI or a text-editable CTL file?  I'm thinking I may want to
change the frequency of polling, the email addresses it goes to, etc.

---

Request for Question Clarification by endo-ga on 09 Jun 2003 20:27 PDT

Hi,

Since you have the date subscribed, then it's even easier, just need
to query the database from time to time and find the entries later
than a given date.

1. Would this be a NT/XP service, or a minimized application that
would have to launch at startup?

This would be a service, there is free software to run a Java
application as a service, invisibly so to say.
 
2. Would I need any special Java installation, or would the Sun
downloaded version work?
 
Regular JRE from Sun.

3. Would the applet be customizable in any way, either through a
simple UI or a text-editable CTL file?  I'm thinking I may want to
change the frequency of polling, the email addresses it goes to, etc.
 
I'm not planning on having any user interface as such, so it won't
really be an applet, just an application. As you suggest, the easiest
way would be to just have a text file, where first line would be
frequency of polling, second line destination e-mail address, third
line SMTP server, etc.

Just tell me the settings you want to configure and I can work on it.
I'll just need several hours to fit in some sleep, then I can work on
it, hopefully I can have it finished in about 16 hours. How does that
sound?

Thanks.
endo

---

Clarification of Question by jhabley-ga on 09 Jun 2003 21:10 PDT

Hi Endo,

 > This would be a service, there is free software to run a Java
 > application as a service, invisibly so to say.

Are you able to point me to these applications? If so, then we're good to go.
  
 > way would be to just have a text file, where first line would be
 > frequency of polling, second line destination e-mail address, third
 > line SMTP server, etc.
 > Just tell me the settings you want to configure and I can work on it.

Super, a TXT file is fine. I'd want:
- frequency of polling (# of min)
- SMTP server name
- email address to address report to
- email subject line to use

---

Request for Question Clarification by endo-ga on 09 Jun 2003 21:17 PDT

Hi,

The Java Service Wrapper does what I described, you can find it here:
http://wrapper.tanukisoftware.org/doc/english/index.html

I'll try and write a guide on how to use the Wrapper with the
application once it's written.

I'll need another 3 lines in the text file: database name, database
username and database password.

I'll get on this, first thing in the morning.

Thanks.
endo

---

Clarification of Question by jhabley-ga on 09 Jun 2003 21:19 PDT

Super thanks. You'll probably also need DB server info -- for
instance, in my case, it's db.netnation.com:4000 (port 4000).

---

Request for Question Clarification by kyrie26-ga on 09 Jun 2003 21:52 PDT

A more straightforward alternative is to do get the same script that
does the Insert action on the database, to send you a notification
email. This way, each time a new subscriber signs up (is inserted),
you get an email.

Just provide the existing script that currently handles new
subscribers, and I can modify it for you. I'm assuming that the
current script is probably in PHP, ASP, ColdFusion, JSP or some other
scripting language.

Let me know if you like this solution.

---

Clarification of Question by jhabley-ga on 09 Jun 2003 22:32 PDT

Hi Kyrie,

That probably would work best, actually. It's in PHP. Endo: Your
thoughts? If it can be built into the subscription script, would seem
easier to handle this way.

---

Request for Question Clarification by kyrie26-ga on 09 Jun 2003 23:59 PDT

jhabley-ga,

I specialize in PHP. If you could post that script, I could modify it
for you. Or, I could give you the code for you to "plug in" to the
script. Either way, you choose the level of detail you need. Just let
me know and I'll get cracking right away. Or Endo can do it too if he
wishes. I'll leave it to you both to decide the next step.


kyrie26-ga

---

Request for Question Clarification by endo-ga on 10 Jun 2003 05:18 PDT

Hi,

Yes that does make more sense, I was just assuming you didn't have
direct access to that script, or maybe didn't think laterally enough.
Unfortunately I have no knowledge of PHP (just JSP) so kyrie26 feel
free to go ahead.

Thanks.
endo

---

Clarification of Question by jhabley-ga on 10 Jun 2003 06:42 PDT

Good morning endo and kyrie26,

I think kyrie26's solution is probably more practical, as the email
processing happens immediately upon posting and doesn't actually
require any third-party polling. Endo: Thanks for your professionalism
in releasing this one.

The PHP script is rather long and is a product I paid for so I'm
hesitant to post it. (I'm not clear if my licence agreement even
permits it.) I do understand that Answers doesn't let us contact each
other directly, so I'd propose this:

I'll check this page every 5 min or so. Add a Clarification Question
to tell me when you're there and checking this page and I'll post a
URL with the file. If you can download it right away and let me know
when you've done that, I can take the site down.  Does that work?  Let
me know when you're checking...

---

Request for Question Clarification by kyrie26-ga on 10 Jun 2003 11:49 PDT

Hi jhabley-ga,

I have a better idea. Conceptually, all we need to do is to run a
mailer function right after the database insertion occurs. This
insertion point should be very easy to locate in the script.

I will provide a custom mailer function, and all you need to do is
stick it in at the insertion point. Well, actually, the function
declaration will be at the top of the code, but the function call will
be right after the insertion point.

How do you feel about this? It would be better Google policy-wise too,
because the purpose of Google Answers is to provide
publicly-accessible published Answers.

Let me know if you're comfortable with this idea. It may involve a
little bit of troubleshooting on your part, but I'm prepared to hold
your hand every step of the way. Just remember to make a backup copy
of the script before you start modifying it. What do you think?


Cheers,

kyrie26-ga

---

Clarification of Question by jhabley-ga on 10 Jun 2003 11:50 PDT

Sounds good to me, thanks. Let's start.

Hi jhabley-ga,

Thank you for your question! Let's jump into the code right away :



<?php // function declaration, to be placed at top of subscription
script


	function subscr_notif_mailer($message) {
	
		$to = "your-email@someplace.com"; // change this to recipient email
		$subject = "New Subscriber Notification";

		mail($to, $subject, $message);
	
	}


?>


<?php // function call, to be placed right after subscription database
insertion


	$nameStr = 'Name : ' . $_POST["name"] ; // change this variable to
whatever form variable you used for the name
	$nameStr .= '\n'; // append newline

	$emailStr = 'Email : ' . $_POST["email"] ; // change this variable to
whatever form variable you used for the email
	$emailStr .= '\n'; // append newline

	$cityStr = 'City : ' . $_POST["city"] ; // change this variable to
whatever form variable you used for the city
	$cityStr .= '\n'; // append newline
	
	$message = $nameStr . $emailStr . $cityStr;
	
	

?>


The assumption here is that we know the Form variables that are used
to hold the subscriber fields (name, email, city, etc.). These Form
variables are then "prettied up" in text strings, concatenated into
one $message variable, and passed to the mailer function to be sent to
the recipient. Please look through the code and take note of the
places where you need to change the variable names (instructions as
comments).

Then test by entering new subscriptions. You will also like to delete
these test entries later. A neat tool I recommend is SQLyog
(www.sqlyog.com). Just log in to the database using the hostname,
username and password, and you can access the tables directly to
delete the unwanted test records.

Voila! It should work. If not, let me know what error messages you get
and we'll take it from there. Good luck!


Cheers,

kyrie26-ga

---

Clarification of Answer by kyrie26-ga on 10 Jun 2003 13:03 PDT

Oops,

Apologies, I forgot to add the all-important line that actually calls
the mailer function :

subscr_notif_mailer($message);

This should be added as the last line in the function call block of
PHP code (the 2nd block). Thanks!

---

Request for Answer Clarification by jhabley-ga on 10 Jun 2003 13:26 PDT

Hi kyrie26,

First of all, thanks for your work on this and very well-commented
code.

We're part-way there. I've added the code into the script and it is
indeed sending me an email! Yay!

One problem at this point (you *did* permit me some hand-holding..
<grin>):

The email comes out like this: 

       Name : \nEmail : DELETE@ME.COM\n

   The name field doesn't seem to be working and it's not pretty --
the line-breaks seems to have not taken. As well, the other fields
like city, type of subscriber, etc. are generated dynamically. (I'm
using MailWorksPro 4.0, if this helps.)  Email and password are
hard-coded, but FirstName, City, HowYouHeard, and Profession fields I
added through the product's UI.  I'd send you the code if I knew
exactly where I could.

---

Request for Answer Clarification by jhabley-ga on 10 Jun 2003 13:30 PDT

If it helps, these appear to be the code variables.

		global $siteName;
		global $siteURL;
		global $siteEmail;
		global $fName;
		global $email;
		global $useTemplates;
		global $confirmEmailNew;
		global $banning;
		global $banEmail;
		global $siteFolder;
		global $dbPrefix;
		$fName = @$_POST["fName"];
		$lName = @$_POST["lName"];
		$email = @$_POST["email"];
		$pass1 = @$_POST["pass1"];
		$pass2 = @$_POST["pass2"];
		$tIds = @$_POST["templateId"];

---

Request for Answer Clarification by jhabley-ga on 10 Jun 2003 13:33 PDT

I'd also like to be able to make the reply-to address of the automated
email the email of the subscriber, if possible. I recognize this is
above the scope of the initial question, so to account for that and
the extra hand-holding, I will add $25 as a tip when we get this
working. Fair?

---

Clarification of Answer by kyrie26-ga on 10 Jun 2003 16:07 PDT

Hi again jhabley-ga,

No problem on the hand-holding! It is my utmost pleasure. I wasn't
expecting a tip at all, it is entirely up to you - but thanks in
advance, it will be a definite blessing.

I'm trying to figure out the line breaks right now... I should have a
solution for you in the next few hours. Thanks for your patience!


Regards,

kyrie26-ga

---

Request for Answer Clarification by jhabley-ga on 10 Jun 2003 16:12 PDT

Sounds good, thanks. I'll be waiting. Would also love a tip on how to
code plain text in the email. I'm sure it's something simple like

   ''This is the text.\nAnd this is the next line.'' + ;

or something. :)

---

Clarification of Answer by kyrie26-ga on 10 Jun 2003 18:49 PDT

Hi again jhabley-ga,

Try this :


<?php // function declaration, to be placed at top of subscription
script


	function subscr_notif_mailer($message, $headers) {
	
		$to = "your-email@someplace.com";
		$subject = "New Subscriber Notification";

		mail($to, $subject, $message, $headers);
	
	}


?>


<?php // function call, to be placed right after subscription database
insertion

	$nl = chr(10);

	$fNameStr = 'First Name : ' . $fName . $nl ;
	$lNameStr = 'Last Name : ' . $lName . $nl ;
	$emailStr = 'Email : ' . $email . $nl ;
	$cityStr = 'City : ' . $city . $nl ;
	$countryStr = 'Country : ' . $country . $nl ;
	$categoryStr = 'Category : ' . $category . $nl ;
	$howYouHeardStr = 'How You Heard : ' . $howYouHeard . $nl ;
	$professionStr = 'Profession : ' . $profession . $nl ;

	$message = $fNameStr . $lNameStr. $emailStr . $cityStr . $countryStr
. $categoryStr . $howYouHeardStr . $professionStr;
	
	$headers = "Reply-To: ".$email."\r\n";
	
	
	subscr_notif_mailer($message, $headers);  // function call


?>


I've tested the newline successfully, the chr(10) function works. I've
also added the "Reply-To:" field.

Just plug the variables in where applicable. If you're having problems
finding the variable names, you can send the script to me - go to
www.jobsvolution.ca/contact.cfm and send it through the contact form.
I have exclusive access to that account (coincidentally, that's a
mailer behind the scenes there).


Hope this works... let me know! Glad to help.


Cheers,

kyrie26-ga

---

Clarification of Answer by kyrie26-ga on 10 Jun 2003 22:47 PDT

jhabley-ga,

As per our "external" correspondence and subsequent walkthrus, I am
glad to have been of help in finding you the solution to your problem.
Thank you for using Google Answers!


Regards,

kyrie26-ga

---

Clarification of Answer by kyrie26-ga on 11 Jun 2003 14:21 PDT

For the record, here is the code that was accepted :


<?php // function declaration, placed at beginning of script with
other function declarations

 	function SubscrNotifMailer($message, $headers) 
		{ 

		  $to = "notify-recipient@someplace.com"; 
		  $subject = "New Subscriber Notification"; 
		 
		  mail($to, $subject, $message, $headers); 

		}


?>



<?php // function call, placed right after record insertion of new
subscription

		$nl = chr(10); 

		$emailStr = 'Email : ' . $email . $nl ; 
		$categoryStr = 'Category : ' . $_POST['cust_Category'] . $nl ; 
		$fNameStr = 'First Name : ' . $_POST['cust_first_name'] . $nl ; 
		$cityStr = 'City : ' . $_POST['cust_city'] . $nl ; 
		$countryStr = 'Country : ' . $_POST['cust_country'] . $nl ; 
		$howHeardStr = 'How Heard : ' . $_POST['cust_howheard'] . $nl ; 
		$templateIDStr = 'Template ID : ' . print_r($tIds) . $nl ; 

		$message = $emailStr . $categoryStr . $fNameStr . $cityStr .
$countryStr . $howHeardStr . $templateIDStr;

		$headers .= "Reply-To: ".$email."\r\n"; 

		ini_set("sendmail_from", "SubscrNotifMailer@todmaffin.com");
		SubscrNotifMailer($message, $headers);  // call mailer
		
?>

kyrie26-ga was excellent. Walked me through several versions of this
code and was very patient.