Page 1 of 2

[mod] Automatically send last list message to new subscriber

PostPosted: 8:48pm, Thu 17 Aug, 2006
by Foxpower
Hi, I think, the best entry point to implement a function to send the latest newsletter to a new subscriber automatically is in the file /lists/index.php in the function confirmPage($id) between lines 503 and 504 (before the return $res;).

Right after the confirmation of the newsletter subscription the latest newsletter should be sent out in the next processqueue cycle.

Currently I'm searching for the function to resend a specified message. Maybe anyone has the exact file name and line number of this function? I get lost with that huge amount of different, undocumented scripts.

ThanX, Foxpower

PostPosted: 10:13pm, Thu 17 Aug, 2006
by Foxpower
OK, found the part that was responsible for the resending of a newsletter. It's in /lists/admin/messages.php, lines 80-94.

Code: Select all
if (isset($_GET['resend'])) {
  $resend = sprintf('%d',$_GET['resend']);
  # requeue the message in $resend
  print $GLOBALS['I18N']->get("Requeuing")." $resend ..";
  $result = Sql_query("update ".$tables["message"]." set status = \"submitted\" where id = $resend");
  $suc6 = Sql_Affected_Rows();
  # only send it again to users, if we are testing, otherwise only to new users
  if (TEST)
    $result = Sql_query("delete from ".$tables["usermessage"]." where messageid = $resend");
  if ($suc6)
    print "... ".$GLOBALS['I18N']->get("Done");
  else
    print "... ".$GLOBALS['I18N']->get("failed");
  print"<br><hr><br><p>\n";
}

Now I have to think about including the relevant parts in /lists/index.php.

PostPosted: 9:23pm, Fri 18 Aug, 2006
by Foxpower
Here's a solution for the problem / feature request that works for me (phplist 2.10.2).
I use only one list, so I assume that the actual newsletter to send has the highest id in the table - ah, and it can only be a record, that has a embargo date in the past. If you use more lists in your phplist installation, then you have to refine the sql query. OK, let's start (we're working on the file /lists/index.php):

Search for line 501 ($res .= $html;) and insert after that the following six lines:

Code: Select all
  $job = Sql_Query("SELECT max(id) FROM $tables[message] WHERE embargo <= now()");
  $nldata = Sql_Fetch_Array($job);
  $result = Sql_Query("UPDATE $tables[message] SET status = \"submitted\" WHERE id = $nldata[0]");
  $success = Sql_Affected_Rows();
  if ($success)
    $res .= "You will revcieve in a few minutes a copy of our latest newsletter.<br>";

That's all. Every time a new user confirms his newsletter activation, the latest valid newsletter record is set to 'resend' (table phplist_message, field status = submitted).

The next time when your cron job processes the queue the latest newsletter will be sent out to new users.

Greets, Foxpower$

PostPosted: 10:25pm, Sun 24 Sep, 2006
by Keys
Fox, you mention:
Foxpower wrote:The next time when your cron job processes the queue the latest newsletter will be sent out to new users.


Does that mean you have to set up a cron job?

I put in the code you suggested and tested it by subscribing with one of my e-mail addresses. Everything went well but I am still waiting for the last newsletter sent.

As far as I know, I don't have any cron jobs set up. When I have a newsletter ready I simply send it to the list.

PostPosted: 9:33am, Mon 25 Sep, 2006
by Foxpower
No, you don't need to set up a cron job, it works also with the manual process queue in the admin backend, but the process queue has to be activated via cron or backend link either. The latest newsletter is only marked for re-sending.

Here's a compilation with my tips and solutions for phplist, but only in german: http://www.contentschmiede.de/archiv/2006/08/07/das_newsletter-tool_phplist/

Anyone done this for Multiple lists?

PostPosted: 12:13pm, Tue 10 Oct, 2006
by snapper
I'm quite new to phplist, but would really like to be able to use this functionality. However, I have a number of lists, and I'm trying to work out what the sql query would be.

Has anyone done this, or do I need to spend some time analysing the database to work out how to get the latest message for the lists the user has subscribed to? Looking at the number of relations in the database makes me think this will be quite complex.

In the short term, I'll work around it by only sending to one list, so the message will be the latest for the list I want users to subscribe to - but it would be so useful to get it work for multiple lists.

regards

Snapper

PostPosted: 5:16pm, Thu 09 Nov, 2006
by myopenbar
So here is a good start with the query for MULTIPLE LISTS. I am using my table names here but in the actual index.php file we would need to use the proper table variables [message] and (i think) [listmessage]. [LISTID] represents the id of the list the user would subscribe to.

QUERY:
SELECT max(phplist_message.id) FROM phplist_message LEFT JOIN phplist_listmessage ON phplist_message.id=phplist_listmessage.messageid WHERE embargo <= now() AND phplist_listmessage.listid = [LISTID];

thoughts:
1. What is and where do i find the list id for the subscriber (vs. a preference change)?
2. this should all be in a loop incase the user signs up for multiple lists

PostPosted: 5:55pm, Tue 21 Nov, 2006
by snapper
myopenbar - have you got anywhere with this? I've been looking at the code, but don't have enough of an understanding of how phplist is constucted to work out how to implement this query - nor do I really have a phplist dev environment.

I'm surprised that there is not more demand for this - as it seems an obvious enhancement.

regards

snapper

small bug

PostPosted: 5:52pm, Tue 05 Jun, 2007
by jsherk
There's a bit of bug...

When more than one person subscribes to the list, and then they CONFIRM there subscription, the first person to do so will get the extra message:

You will revcieve in a few minutes a copy of our latest newsletter

But everybody else who CONFIRMS there subscription does not receive this message.

As soon as the Queue is processed though, then the first person to confirm (after the queue is procesed) gets the extra message again (You will revcieve in a few minutes a copy of our latest newsletter), but nobody else gets it.

Everybody gets a copy of the newsletter though. The actual sending out of the last newsletter is working fine, it's just the message they receive upon confirmation that only shows up to the first person.

It's not really a big issue. The feature works great (once I figured the Cron job thing out), and everybody who signs up gets the last newsletter no problem. It's just that only the first person to confirm after the last queue process gets the little extra reminder message.

New user receives last newsletter automatically

PostPosted: 6:12am, Sat 16 Jun, 2007
by Herrin
Looks like the latest version 2.10.4 is different because the cant find the line of code mentioned above. I agree this should be built in to the features because it is a very obvious thing to be able to keep new users up to speed with the latest news. Then everyone gets something straight away.

code

PostPosted: 3:58am, Sun 17 Jun, 2007
by jsherk
I found it in 2.10.4, and made the changes, so maybe you looked in the wrong file, or maybe you included the brackets?

Do a search for this exact piece of code with NO brakcets around it (copy and paste it). Note the SPACE after $res but before the .=

$res .= $html;

I use a free editor called PSPad, and it shows up at line 535 of the file (index.php)

bugs-send last newsletter to new subscriber

PostPosted: 6:37pm, Sun 08 Jul, 2007
by jsherk
Okay, I found a couple bugs in this hack...

(1) The message "You will receive in a few minutes a copy of our latest newsletter" only appears to the FIRST person that confirms their subscription since the last time the queue was processed. This is because the code checks to see if there was a "success" at changing the status to "submitted". When a second (or third, etc) person confirms their subscription, since the status was already changed by the last person that confirmed their subscription, it does not change the staus again, and therefore the "success" fails, and it does not display that part of the message. This will continue until the queue is processed, at which time the status will changed from "submitted" back to "sent", and then the first person to confirm will receive the message, but nobody else.
Here is my solution to this problem... I simply commented out the "if ($success)" part, by putting a # in front of it.

I changed it from this:
Code: Select all
if ($success)
$res .= "You will revcieve in a few minutes a copy of our latest newsletter.<br>";

To this (I also fixed the spelling mistake in the word receive):
Code: Select all
#if ($success)
$res .= "You will receive in a few minutes a copy of our latest newsletter.<br>";


(2) The second bug has to do with the Message ID Number that it picks. It chooses the HIGHEST id, which should correspond to the most recent message that you sent, but the problem comes along when you DRAFT a new message. Since the drafted message now has a higher message id (since it's newer) than the last message that was sent out, this is the id that it picks. The good news is that it does not appear to send out this draft message because the process queue seems to recognize it as a draft (probably because it has not been assigned to a list yet), but the bad news is that it does not send anything at all, so all those people that were supposed to get your last newsletter didn't get anything. The other good news is that it (somewhere) keeps track of these people that were supposed to get the last newsletter, so when you make the fix, it should send them something.
Remember that this works only if you have ONE list, so I'm not sure how to fix this (or if it is even a problem) for the hack with mulitple lists.

I changed this:
Code: Select all
$job = Sql_Query("SELECT max(id) FROM $tables[message] WHERE embargo <= now()");

To this:
Code: Select all
$job = Sql_Query("SELECT max(messageid) FROM $tables[listmessage]");


Another possible solution is if you want to always send the SAME newsletter/message to new subscribers, and not the newest one. In this case, you simply need to find the Message ID of the message you want them to get, and change the WHERE portion of the line to id=xx where the xx is the message id number (I used 48 in my example).
Change the code above to this:
Code: Select all
$job = Sql_Query("SELECT max(id) FROM $tables[message] WHERE id = 48");


NOTE: This hack is in the list/index.php file

PostPosted: 9:39pm, Wed 05 Sep, 2007
by jsherk
Okay, just a comment on what I reported as BUG (2) above:
"...the problem comes along when you DRAFT a new message. Since the drafted message now has a higher message id (since it's newer) than the last message that was sent out, this is the id that it picks..."

You can avoid this problem as long as you do NOT assign your draft to a list! You can draft it and save it as often as you like, and then the very last step you should do just before you send it, is click on the LISTS tab and assign it to a list(s).

If you assign it to a list, while you are still in the draft stage, then your draft copy is what will get sent out as the most recent newsletter to everybody who confirms their subscription.

PostPosted: 6:13pm, Fri 14 Sep, 2007
by rjc
Did anyone make any progress using this for multiple lists? Myopenbar made some suggestions; I wondered if anyone has finalised and implemented them?

PostPosted: 1:48pm, Wed 23 Jan, 2008
by chewblocka
I got this working with multiple mailing lists. Really, the way I did it is probably the more correct way even for just one mailing list. in the confirmPage function in index.php, we are already getting information about each list the user is subscribed to and looping through the list to show them the list information. So in that loop, I added another query to requeue the most recently sent message. So, two modifications need to be made.

1) In the query where the lists are retrieved, we need to get the list id. For me, that is on line 535, but here is the original query:
Code: Select all
$req = Sql_Query(sprintf('select name,description from %s list, %s listuser where listuser.userid = %d and listuser.listid = list.id and list.active',$tables['list'],$tables['listuser'],$userdata['id']));

And here is my modified query:
Code: Select all
$req = Sql_Query(sprintf('select list.id listid,name,description from %s list, %s listuser where listuser.userid = %d and listuser.listid = list.id and list.active',$tables['list'],$tables['listuser'],$userdata['id']));


2) Add an update query in the loop through the lists. This is a while loop around line 540, first while loop after the query modified in step one. Just add the following query inside the loop...

Code: Select all
Sql_Query("update {$tables["message"]} set status = \"submitted\" where embargo <= now() and id = (select max(messageid) from {$tables["listmessage"]} where listid = ".$row["listid"].")");


That should do it. If you want the user to be notified that they will receive the most recent post, add some verbage to the subscribe page or the welcome email.