How to pass users pw through email?

[John Lester]

Ok long story short, hostmonster (my host) shut off my outgoing emails because someone reported the birthday email as spam. They said I need an auto opt out link put in place before they enable my outgoing emails.

I installed ... https://vborg.vbsupport.ru/showthread.php?t=246163 and then asked them to enable my emails. They said no because it's not an auto opt out. They said since the user has to click on the confirm button it's not automated.

So I need to know how to pass the users pw in the automated emails to them, and pass the userid and userpw back to the system in order to automatically log them in, and then automatically opt them out of the emails.

I don't think this is a good idea (to pass the login info back and forth), but it's what the host demands and I need to get it done asap.

[kh99]

I'm not sure how you'd go about doing that, but I have another idea that might work - put the email address on the url as a parameter, then use a plugin similar to the one for the mod you have installed excpet that the query would check the email address rather than the userid (which it could do without being logged in). It's true that with that scheme anyone who knew a member's email address could unsubscribe them, but that's not really a serious security issue. If you wanted, later you could add something like an automatic PM to the user telling them they'd been unsubscribed, so they could resubscribe if it's a mistake.

If you did implement this, one thing you might want to do is change the plugin code so that it only ever displays one message (like "thank you, you're unsubscribed") even if you're passed an invalid email, that way it couldn't be used to guess at member's emails.

[John Lester]

Hi again Kevin :)

I want to thank you for all of your help today sure made it go a lot faster :) However I don't have a clue about how to make this plugin. I thought that you had to login in order to change settings, are you saying that the plugin will change the settings for the user based on their email address without them having to log in?

I had an idea, include the form in the outgoing emails ... but would it work? I can't really test this as the host has outgoing emails turned off, and I don't have any email clients configured on my localhost :p

[kh99]

I want to thank you for all of your help today sure made it go a lot faster :)

You're welcome. Are you talking about over on vbulletin.com? I didn't really think anyone knew who I was over there because I have a different username.

However I don't have a clue about how to make this plugin. I thought that you had to login in order to change settings, are you saying that the plugin will change the settings for the user based on their email address without them having to log in?

The plugin can do whatever it wants - it can check for a user being logged in, but it doesn't have to. But one thing I didn't really think of is that this method would mean customizing the email for each user by inserting the email address in the link. The existing mod currently doesn't do that because it knows the userid by being logged in.


I had an idea, include the form in the outgoing emails ... but would it work? I can't really test this as the host has outgoing emails turned off, and I don't have any email clients configured on my localhost :p


You could write a plugin that checks the username and password if it was passed in a form, but it would have to be html email and so it would depend on the user viewing the mail in html.

[John Lester]

You are klh over there right?

I'll have to go through this tomorrow as I'm a bit too tired to be working on it now :(

I mainly need this for the birthday emails since that is what has been reported by some disgruntled members.

Would it be as simple as replacing $username with whatever the email variable is?

So instead of saying hello John Lester blah blah blah it would say hello jlester@whatever.com

Then making a plugin (that's where I'll need the most help) that will set the user options without them having to login?

[kh99]

You are klh over there right?

Yeah, I guess I'll have to be more careful - I thought I was relatively anonymous over there. :)

It's late for me as well. I don't think inserting the address will be a problem. Maybe tomorrow I can throw something together.

[John Lester]

Morning Kevin :)

So I had a thought, all this plugin would have to do is move the user to a different user group when the "unsubscribe" link is clicked on from their email.

[kh99]

You could move the user to another group if that's what you want. The mod you have installed unsubscribes the user from admin emails using pretty much the same sql that we talked about in the other thread, so that should work too.

I've been thinking about this too. What I wanted was a way to either automatically add the link to every email that goes out, or else have a way to put something in the email body phrase that would insert the link. I haven't figured out either one yet. But that doesn't mean we couldn't come up with something just for the birthday emails, if that's your current problem. I was thinking I might come up with something general that could be released and might help others, but that can wait.

[John Lester]

Well my plan was to edit every outgoing email body phrase to add the link :) What I'm unsure of is how to execute the link permission wise. I'm still a complete noob more or less when it comes to coding.

Sometimes I can take a mod and tweak it (like I did with unsubscribe one), but as far as knowing how to do something from scratch ... well I can't :D

[kh99]

Well my plan was to edit every outgoing email body phrase to add the link :)


The problem with that (using my scheme, anyway) is that the user's email address can be in a different variable for each body phrase. I also think that the address would need to be encoded in some way to be part of a url, so that makes it more complicated. There may not be any "good" way (using only hooks) to do what I was thinking about. But I think it's exactly the same problem if you need a way around the user having to log in, no matter how you choose to do it. I suppose to some people it's important enough that it's worth editing one or more of the vb files if necessary.

BTW, I was just wondering - someone else asked about this same problem just yesterday - is that someone else from your site? Or maybe someone having the same problem with the same host.

[John Lester]

Umm I don't know who that someone else is, point me to the thread and I'll go see :D

I never knew the email address would be in different variables ... to me it makes no sense.

What really baffles me is how the host can insist on something like this that doesn't appear to be easily accomplished on one of the biggest forum softwares out there. I mean I know (ok assume since I can't "know") that 98% of all vBulletin users don't have this retarded one click opt out. I haven't found a mod anywhere that does this.

But, I have seen other places that automatically opt you out by clicking a link sent in an email, but I have no real idea how. I assumed it was a javascript and all it did was delete the address from a mailing list. Either that or it was a completely bogus opt out :D Which to be honest is what I'm thinking of doing until I get a real working solution :D

It wouldn't be hard to pull off (I know hostmonster will check I've raised too much hell over this for them not to once I tell them it's done), just disallow registered users to subscribe to anything, then run a couple queries to deselect some options and viola! the wool has been pulled over their eyes :D

[kh99]

I was talking about this: www.vbulletin.org/forum/showthread.php?t=285357 . It looks like he found the same mod you did.

I would say it isn't so much that it's difficult to implemt as it is that the system just wasn't designed for it. A lot of the code (I think) is pretty much the same as it's been for many years. And the software also wasn't really designed for modifying (without file edits). The plugin system helps a lot but you're still at the mercy of the hook placement within the code (in other words there are a lot of things you just cannot change using a plugin). I've heard that long ago all mods were done by file edits, so I suppose we're spoiled. :)

Anyway, I know there's some urgency on your end, so I'll think about what we can do to get you going, but it will probably be later this afternoon at the earliest (unless someone else reads this thread and comes up with a better idea).

--------------- Added 1341932122 at 1341932122 ---------------

...but I guess I'm kind of defending vbulletin when your point was really that your host is being unreasonable. I think you're right, like you said, if many other people had that requirement it would be done already.

[John Lester]

Ah I saw his replies and told him I can not give him the changes I made without the author's ok.

I've been thinking, could a plugin be created to prevent a usergroup from being able to check the options for emails? I know you can turn them off by default via the acp, but then the user can just turn them on. I figure with the changes to the user groups I made last Aug when we switched hosts to hostmonters, if the registered usergroup is not allowed to change those settings I won't have this issue anymore.

I'm not in a life or death hurry, my users know it's the hosts fault :D I had to turn of confirmation of new registrations though so I prepared my moderators for a possible influx of spammers :(

I think a plugin that moves them to a different usergroup would be easiest right?

--------------- Added 1341938412 at 1341938412 ---------------

If we can update this to work on 4.1.x it will satisfy my retarded host.

https://vborg.vbsupport.ru/showthread.php?t=224315

--------------- Added 1341938804 at 1341938804 ---------------

The main difference between the old mod I linked and the one I installed is that the old one doesn't require the user to log in. But since I just spent 1.5 hours with my crappy hosts "support" I can't see how it works.

[kh99]

If we can update this to work on 4.1.x it will satisfy my retarded host.

https://vborg.vbsupport.ru/showthread.php?t=224315


Oh, OK - so I guess the problem isn't that the link didn't unsubscribe with one click, but that you needed to log in? That makes it easier since you can ask the user to enter his email address instead of having to customize the message.

Anyway, we can convert that mod - it's even marked reusable.

[kh99]

OK, I just ported the mod you mentioend above to vb4.2.0, and I'm attaching it here so you can try it. Let me know if you need anything changed. If it works I'll post it in the mod section.

[John Lester]

Afternoon :)

It looks like it works just fine on my test site (4.1.12) but I don't think it will conform with their guidelines. I don't see the difference between having it send an unsubscribe email that they have to wait on, then click again, and having to log in to click a button.

Since I'm still blocked from sending emails I can't test it on the live site, and I still don't have an email client configured for my localhost so I never got the unsubscribe email :D

--------------- Added 1341962024 at 1341962024 ---------------

Basically it needs to do all of the work once the user clicks the link/button. It can not send an email, or go to a confirmation page (unless there's no other links/buttons to click).

I'm seriously starting to hate hostmonster.

--------------- Added 1341962673 at 1341962673 ---------------

Yep just as I thought, they don't like that one either. I so wish I could crawl through the screen and light their damn lawyers on fire and toast some marshmellows while listening to Black Sabbath :D

[kh99]

OK, well, I can make the "double opt-out" (as it's apparently called when you have to click the email link) optional. I can make it so that you just enter an email address and it's unsubscribed. BTW, you shouldn't have to log in at all for this to work - but I probably should have mentioned that you need to uninstall the other mod first, I think they use the same hooks and values for "do", so if you saw a log in message it may be because they're interfering with each other.

--------------- Added 1341963004 at 1341963004 ---------------

Yep just as I thought, they don't like that one either. I so wish I could crawl through the screen and light their damn lawyers on fire and toast some marshmellows while listening to Black Sabbath :D


What don't they like about it? Just having to click the email link? So if I can get it to be a screen where you enter an email and press "unsubscribe", and you don't have to log in to do it, that should be OK?

[John Lester]

Yes I uninstalled the other one first :)

They don't like the 3 step process. Step 1 being clicking on the link in the email. Step 2 entering the email address and clicking a button/link. Step 3 being receiving another email with another link/button to click.

They want at most 2 steps. Click on link from email and goto an unsubscribe page, enter email on the page and click a link/button and be done.

--------------- Added 1341965571 at 1341965571 ---------------

So no "double opt outs" or "double confirmation" pages.

--------------- Added 1341966737 at 1341966737 ---------------

Holy cow I'm some sort of stupid. I may have found a ready made answer here at vbulletin.org :D

https://vborg.vbsupport.ru/showthread.php?t=266142

Depending on what Fillip says to my questions of course :) I have no idea why I didn't look for an email manger system...I concentrated on an "opt out" system.

--------------- Added 1341967265 at 1341967265 ---------------

I am eternally grateful for all of the time and patience you have devoted towards my problem, and I would be even more grateful if you continued on this quest in case the dbtech mod won't satisfy my host.

I think you should expand the settings that your ported version of this mod changes. These are what I had done in the other mod that my host says won't work for them.


$db->query("UPDATE " . TABLE_PREFIX . "user SET options= options - 256 WHERE (options & 256) AND userid=" . $vbulletin->userinfo['userid']);
$db->query("UPDATE " . TABLE_PREFIX . "user SET options= options - 2097152 WHERE (options & 2097152) AND userid=" . $vbulletin->userinfo['userid']);
$db->query("UPDATE " . TABLE_PREFIX . "user SET options= options - 32 WHERE (options & 32) AND userid=" . $vbulletin->userinfo['userid']);
$db->query("UPDATE " . TABLE_PREFIX . "user SET options= options - 2048 WHERE (options & 2048) AND userid=" . $vbulletin->userinfo['userid']);
$db->query("UPDATE " . TABLE_PREFIX . "user SET autosubscribe = -1 WHERE autosubscribe = 0 OR 1 OR 2 OR 3 AND userid=" . $vbulletin->userinfo['userid']);

It disallows vcard download, unchecks all 3 of the email ticks (where receive email from admin is), sets thread subscriptions to "do not subscribe", and turns off pms (to prevent any pm related emails going out).

Of course you don't have to, but I think it would be a popular addition in my nooby opinion :D

--------------- Added 1341969797 at 1341969797 ---------------

OK, well, I can make the "double opt-out" (as it's apparently called when you have to click the email link) optional. I can make it so that you just enter an email address and it's unsubscribed.

The more I think about it, the more I think this route is the way to go, and what you described here is exactly what is required. I think I'd rather go this route than installing a semi complex mod (at least to me since I don't know jack about email managers :D ) like the one I listed earlier.

[kh99]

I've started to work on this, but I haven't gotten very far - I almost forgot to check this thread to see if you made any progress. Anyway, do you still need this? I only glanced at the dbtech mod, but it looks like it does a lot, so there may still be some use for a mod that simply makes it easy for members to unsubscribe by setting the options.

Do you happen to know if setting each of those user options to No and deleting all subscriptions covers everything? I was actually thinking of maintaining a list of addresses and checking it in the mail send function as a way of ensuring that no email was sent to someone who opted out (assuming they wanted to opt out of all emails from the site).

Edit: you also mentioned moving the user to another group, do you still think that would be a useful option? I thought I'd just start with setting the admin email option to no, then go from there.

[Simon Lloyd]

Couldn't you use the misc.php?do=$userid as the link posted and then in a pluging get the userid (remember i know nothing of JS :))<script type="text/javascript">
function vistedurl()
{
$UnSubsUrl = window.location.href;
}
</script> like thisif ($_REQUEST['do'] == 'vistedurl')
{
$uid = substr( $UnSubsUrl , strrpos( $UnSubsUrl , '=' )+1 );
} that line will find the last occurrence of = and give everything after it. Ok thats probably messed up but it might give you an idea for the link they can click to unsubscribe in a 1 click action if the plugin then has a query to unsubscribe the userid :)

If you do happen to sort this code out as a one link click (vb3 & vb4 hopefully) i'd like to see it ;)

[kh99]

I'm not sure I follow what you're doing there. You wouldn't want a link that unsubscribes someone by userid (and didn't require logging in) because it would be too easy for someone to unsubscribe everyone, for instance.

But anyway, we're talking about a link you click on from an email, so we probably wouldn't want to use javascript. And after more discussion in the thread we've decided that it's enough if it takes you to a page where you enter your email to unsubscribe. Apparently the big problem they had was making it so you have to log in to unsubscribe. I actually appreciate that because sometimes you just don't want to receive email from a site anymore and you don't want to have to try to remember how to log in.

[John Lester]

I've started to work on this, but I haven't gotten very far - I almost forgot to check this thread to see if you made any progress. Anyway, do you still need this? I only glanced at the dbtech mod, but it looks like it does a lot, so there may still be some use for a mod that simply makes it easy for members to unsubscribe by setting the options.

The mod won't work as far as my host is concerned because it doesn't handle the system emails which are the only emails I send out.


Do you happen to know if setting each of those user options to No and deleting all subscriptions covers everything? I was actually thinking of maintaining a list of addresses and checking it in the mail send function as a way of ensuring that no email was sent to someone who opted out (assuming they wanted to opt out of all emails from the site).

As far as I can tell those settings are all of the settings that would trigger an email. I suppose you could not turn off pms and turn off the emailonpm bitfield, but that bitfield is in the group name=regoptions (vs the group name=useroptions where all of the bitfields we talked about are grouped) which I haven't really fiddled around with yet.


Edit: you also mentioned moving the user to another group, do you still think that would be a useful option? I thought I'd just start with setting the admin email option to no, then go from there.

I think it is a viable option as it would be another layer of opting out in my opinion. I had created a user group back in Aug of last year that has all of the settings that would send emails turned off except for pms. I would move those users that sent me a reply to the birthday email asking to be un-subscribed, or if the emails were bounced. Since then I've installed https://vborg.vbsupport.ru/showthread.php?t=138884 to handle the bounced emals and am loving that mod :) (well until the host blocked all outgoing emails but that's not the mods fault :D )

I'm actually going to tweak that user group once we get this working to limit the pm inboxes and such to encourage people who do use the unsubscribe but want to use pms to "double opt in (opt in 1 by requesting a user group change and opt in 2 by having to select (or not) the options to get emails) to cover my butt in case they later complain.

--------------- Added 1342028394 at 1342028394 ---------------

Oh my sometimes I really wish I could afford to get new glasses :D There is a bitfield for emailonpm in the useroptions group. It's 4096.

I guess the choice depends (in my case for sure) on whether or not you include the changing of user groups. If you include the changing of user groups then I suggest turning off the emailonpm option vs turning off pms altogether.

If you don't include the changing of user groups then i would suggest turning off pms altogether.

Just my $.02 as this is your baby and I'm just along for the ride :D

[Simon Lloyd]

I'm not sure I follow what you're doing there. You wouldn't want a link that unsubscribes someone by userid (and didn't require logging in) because it would be too easy for someone to unsubscribe everyone, for instance.

But anyway, we're talking about a link you click on from an email, so we probably wouldn't want to use javascript. And after more discussion in the thread we've decided that it's enough if it takes you to a page where you enter your email to unsubscribe. Apparently the big problem they had was making it so you have to log in to unsubscribe. I actually appreciate that because sometimes you just don't want to receive email from a site anymore and you don't want to have to try to remember how to log in.Ok well it could still be the email address that you tag on so when the user clicks the link in his email something like /misc.php?do=vistedurl&myemail@myaddress.com it will take you to the site, you wouldn't need to log inbut triggering the misc should cause the plugin to do the rest - well thats as simple as i saw it in my head :)

[John Lester]

Just tested the emailonpm bitfield and it works like a charm :)


$db->query("UPDATE " . TABLE_PREFIX . "user SET options= options - 4096 WHERE (options & 4096) AND userid=" . $vbulletin->userinfo['userid']);

--------------- Added 1342028910 at 1342028910 ---------------

Ok well it could still be the email address that you tag on so when the user clicks the link in his email something like /misc.php?do=vistedurl&myemail@myaddress.com it will take you to the site, you wouldn't need to log inbut triggering the misc should cause the plugin to do the rest - well thats as simple as i saw it in my head :)

If that would indeed work as I can envision it in my head, the user wouldn't have to enter their email address as the plugin could populate the email address automatically. That way all they'd have to do is click the unsubscribe button.

However I am the noob here so who knows if I'm even close to being right :D

--------------- Added 1342029051 at 1342029051 ---------------

Kevin once the method is decided (either the current route you're pursuing or this new one suggested by Simon) and working I really think that adding these options in the ACP would be a great idea. Give the admins the choice on which of the options are affected by the unsubscribe button, which user group to move them to (if that's included), and give them the ability to customize the phrase without having to edit the xml (which is how I do it if the mod doesn't have ACP settings :D )

[kh99]

Ok well it could still be the email address that you tag on so when the user clicks the link in his email something like /misc.php?do=vistedurl&myemail@myaddress.com it will take you to the site, you wouldn't need to log inbut triggering the misc should cause the plugin to do the rest - well thats as simple as i saw it in my head :)

Yeah, that's pretty much what I thought (see post #2), and it's almost what we're doing except that I haven't quite worked out a way to get the email address into a customized email, so we're using a form for the user to enter their email instead. Most vb emails are sent using a phrase but the info like user name and email that are used to customize it are in different variables depending on where the phrase is used, and there's something like 70 email body phrases. Also (and as usual) it's limited by available hooks. I think what I might end up doing is releasing this with the form method, then later adding instructions for a code modification in case someone wants the automatic links.


Kevin once the method is decided (either the current route you're pursuing or this new one suggested by Simon) and working I really think that adding these options in the ACP would be a great idea. Give the admins the choice on which of the options are affected by the unsubscribe button, which user group to move them to (if that's included), and give them the ability to customize the phrase without having to edit the xml (which is how I do it if the mod doesn't have ACP settings :D )

That's what I'm doing right now. I think it's almost done but I haven't tested it yet. So, probably I'll attach another version here in an hour or two.

[Simon Lloyd]

Yeah, that's pretty much what I thought (see post #2), and it's almost what we're doing except that I haven't quite worked out a way to get the email address into a customized email, so we're using a form for the user to enter their email instead. Most vb emails are sent using a phrase but the info like user name and email that are used to customize it are in different variables depending on where the phrase is used, and there's something like 70 email body phrases. Also (and as usual) it's limited by available hooks. I think what I might end up doing is releasing this with the form method, then later adding instructions for a code modification in case someone wants the automatic links.




That's what I'm doing right now. I think it's almost done but I haven't tested it yet. So, probably I'll attach another version here in an hour or two.Couldn't you add to current phrases $userinfo['email'], or in each of the cron files add your custom misc.php link? just thinking out loud ;) brain farts usually but sometimes there's pearls!

EDIT: this is out of the cron for birthdayswhile ($userinfo = $vbulletin->db->fetch_array($birthdays))
{
$username = unhtmlspecialchars($userinfo['username']);
eval(fetch_email_phrases('birthday', $userinfo['languageid']));
vbmail($userinfo['email'], $subject, $message);
$emails .= iif($emails, ', ');
$emails .= $userinfo['username'];
}
so you could even do something like $message = $message . "<p><a href='www.mysite.com/misc.php?do=vistedurl&$userinfo['email']'>Unsubscribe</a>"

[kh99]

Yes, that would work for birthdays. That's what I was thinking a couple of days ago, and I told John if he just wanted something to deal with the birthday emails we could throw together something quick, but since then it's become my understanding that he'd really like to have a more general solution (but maybe I'm wrong about that).

Edit: oh, right - the other thing I was concerned about is that I think some email addresses might have characters that require URI encoding before adding the addreses to a url, so I didn't want to just add it directly. (In particular, I believe '&' is allowed in an email name).

[kh99]

OK, here's the latest. There are some options in the adminCP, probably all the way at the end in the last section. The "require login" doesn't completely work yet so just leave it as "no" (and that makes it pretty much is like the "unacceptable" version anyway).

[John Lester]

Kevin it's not inserting the unsubscribe link on my live site (emails are temporarily restored for testing). I have it enabled and have the double opt out and require log in set to no.

I have "receive emails from admin" checked and am using instant notification for subscribed threads.

[kh99]

Kevin it's not inserting the unsubscribe link on my live site

OK, I think we may have our signals crossed - this one doesn't automatically insert anything, it's just a port of the one that you liked but wasn't acceptable because of the "double opt-out" - or was it the one that required you to log in? In any case, did the previous one insert a link? Maybe I messed something up without realizing it was there.

[John Lester]

Ummm hmm let me try something real quick :D NO the other did not insert a link in the outgoing emails.

--------------- Added 1342041474 at 1342041474 ---------------

Ok with the old one I'd visit urlhere/misc.php?do=unsub and click the unsubscribe button.

This one when I visit urlhere/misc.php?do=unsub I see my smilies page :D

So I have no idea what's going on right now :(

[kh99]

Oh, sorry, that is my fault. I decided to change the "do" values so that they wouldn't conflict with the other mod (which now that I think about it might not have been a good idea since some people might want to replace the other mod with this one).

Anwyay, the current one uses do=unsubscribe instead of do=unsub.

--------------- Added 1342041966 at 1342041966 ---------------

I guess I should try that dbtech mod. So far this hasn't been a big deal, but before I spend any more time I should make sure I'm not making a poor imitation of what they've done already.

[John Lester]

Ok it works :) But it can't find phrase klh99_unsublink_done

--------------- Added 1342042115 at 1342042115 ---------------

If you don't mind I'll be tweaking it to turn off those other options as well just to cover my butt :)

[kh99]

Ok it works :) But it can't find phrase klh99_unsublink_done

--------------- Added 1342042115 at 1342042115 ---------------

If you don't mind I'll be tweaking it to turn off those other options as well just to cover my butt :)

Yeah, a phrase or two was misnamed (I went and renamed everything because I like to add kh99 and mod name just to avoid possible comflicts). I've been working on little things like that in the current version.

Tweaking what to turn off what? You can do whatever you want of course, but if you want to install the final mod when it's done then you probably won't want to do a lot of phrase editing just yet (unless you don't mind repeating it :) )

I'm going to try to release a first version tonight. If nothing else that will let us continue this discussion in the mod thread instead of annoying everyone in the forum by continuously bumping this thread :).

[John Lester]

Right now your version only deselects "receive emails from admin", the older one that I tweaked deselects a few more options and sets thread subscriptions to "do not subscribe".

I forgot to attach the tweaks I made to the older mod for you to see :p

I changed the name of the mod so I wouldn't import the wrong one, but the attached is the old mod with my tweaks.

[kh99]

Right now your version only deselects "receive emails from admin", the older one that I tweaked deselects a few more options and sets thread subscriptions to "do not subscribe".

I forgot to attach the tweaks I made to the older mod for you to see :p

I changed the name of the mod so I wouldn't import the wrong one, but the attached is the old mod with my tweaks.

Oh, I see. Good, I'll take a look at that. Yeah, I was thinking about a set of checkboxes for what it should turn off, in the adminCP and maybe optionally on the user form (where you enter the email) so a user could decide what to opt out of.

--------------- Added 1342042972 at 1342042972 ---------------

BTW, I think setting autosubscribe to -1 just stops the automatic adding of subscriptions to threads. If you want to also delete existing subscriptions, you'd have to delete the records from the subscription tables.

[John Lester]

Yes all it does is change the setting, I haven't even attempted to delete existing subscriptions because technically it's not needed since deselecting "receive email from admins" stops them from being sent.

At least that is my understanding of what "receive email from admins" actually does.

--------------- Added 1342043294 at 1342043294 ---------------

The reason I added the change to subscriptions was basically just testing to make sure I understood what I was doing :D

--------------- Added 1342043372 at 1342043372 ---------------

As you probably have seen with my tweaked version of the old mod, I'll be changing the phrase to something very similar depending on the final product :)

[kh99]

Yes all it does is change the setting, I haven't even attempted to delete existing subscriptions because technically it's not needed since deselecting "receive email from admins" stops them from being sent.

At least that is my understanding of what "receive email from admins" actually does.

Hmm...if it's important to you then I'd test that. Looking at the code, it doesn't look like that's true. In fact after a quick glance it looks like the only things affected by the admin email setting is birthday emails, infraction notices, and emails sent from the admincp (but I may have missed a few things).

I'll try to have a somewhat stable version tonight so you don't have to change things more than once (unless of course you're happy with the current one, then you don't really *have* to install any other version).

[John Lester]

Well the only emails we send out are: birthday, subscriptions, pm notifies, infractions, and some user to user emails.

I will be putting the url in every "email body text" so I should be covered either way :) No need to go digging around for past subscription stuff :D

--------------- Added 1342046806 at 1342046806 ---------------

Well now this is a bit odd, when I click on the "more" link on my smilie page it tells me it can't find the phrase unsubscribe_done and redirects me to the index page O.o

[kh99]

Well the only emails we send out are: birthday, subscriptions, pm notifies, infractions, and some user to user emails.

I will be putting the url in every "email body text" so I should be covered either way :) No need to go digging around for past subscription stuff :D

Well, in a way that would only make the situation worse, if there was a link but it didn't actually stop the emails.

I've been trying to test if turning off admin emails stops subscription emails, but I'm frustrated by the fact that it can take 10 minutes or more just to receive an email that I send myself. I guess I need to put debug statements in the code to make sure.


Well now this is a bit odd, when I click on the "more" link on my smilie page it tells me it
can't find the phrase unsubscribe_done and redirects me to the index page O.o


What does the url say? I don't see how that could happen in my mod - all the code is inside if statements checking the value of "do".

--------------- Added 1342050219 at 1342050219 ---------------

OK - I'd feel better if I could do a "real" test with emails, but I'm pretty sure that turning off admin emails doesn't stop subscription emails. I think the idea is that the users have control over what they subscribe to, so there's no need for a setting.

But, it OK because it's actually an easy query or two to remove all subscirptions for a user.

Well, OK, more than a couple. I was just looking at the database and there's also socialgroup group and discussion subscriptions and event reminders.

Edit: ..and one more thing, I was hoping to release this tonight but I think I'm done with this stuff for today, I hope that's not a problem.

--------------- Added 1342052683 at 1342052683 ---------------

One more thing - I think I am being stupid. While I was testing the subscription emails I noticed that not only does it already contain a link to remove the subscription, the link also has some kind of "auth" code that removes the subscription in one click without needing to log in. I'm going to look in to that tomorrow.

[John Lester]

Yes I saw that unsubscribe auth link while going through the phrases for email body text. I also saw a couple of links that led to the usercp being used as unsubscribe links.

Either way, what you've done works awesome as far as my host is concerned, and I've already gone though all of the email body text phrases adding the link to your mod and removing any others I found.

I suppose I could have done it the other way around, but I didn't look at what the unsubscribe links did and felt my way was just ... easier :D

No problem at all with the release not being tonight :) I'll do some more sleuthing when it cools off here in regards to the smilie page issue and get back to you.

--------------- Added 1342060732 at 1342060732 ---------------

Ok the message that pops up is - could not find phrase unsubscrib_done and then sends me to the index page :D

You are correct, turning off "receive admin emails" does not stop thread subscriptions (and I'm going to assume group and others as well), and I think I figured that out yesterday which is why I added the condition to set thread subscriptions to "do no subscribe". I figured doing that and adding the unsubscribe link would be enough to cover my butt ... and the host agrees. In fact they wonder why I bothered adding all of that other stuff ... but screw them they suck :D

[ForceHSS]

You should post this as a plugin once all bugs are worked out

[John Lester]

You should post this as a plugin once all bugs are worked out

I believe he (Kevin) intends to do just that :)

--------------- Added 1342065400 at 1342065400 ---------------


Ok the message that pops up is - could not find phrase unsubscrib_done and then sends me to the index page :D


I figured out why this was happening. During my sleep deprived state last night comparing the new version and the old version I accidentally copied over a snipet of code from the old version and imported the contaminated version to my live site :D Once I removed the code everyone works properly now :)

[kh99]

OK, I'm glad you got the error message worked out. Sounds like you've pretty much got yourself straightened out. As fo rthe thread subscription unsubscribe links, I had a look and it's actually pretty much the same thing we're doing. The auth code is a hash of the user id and subscription id, and it's used to unsubscribe without needing to log in, the same as we're using the email address. I think in general it might still be useful to expand the page on our mod to let the user choose what they want to unsubscribe from, and have an "I don't want any more email" box.

When you said your host "wonders why you bothered adding all of that other stuff", what do you mean exactly - that you added another unsubscribe link to the subscription emails?

In any case, do you need anything else? If you're all set with your host I'll just take my time (...and probably never get it released :) ).

[Simon Lloyd]

In any case, do you need anything else? If you're all set with your host I'll just take my time (...and probably never get it released :) ).That would be a crying shame as that should be built in to vb!

[kh99]

Yeah, I was just joking because that's happened to me with a few other mods - that I have them virtually complete then never actually get around to releasing them. I'll make sure I get this one done.

[John Lester]

I want to run a couple of tests today with regards to the thread subscriptions. Mainly I want to know if setting the default to 'do not subscribe' will prevent existing subscriptions from sending emails. If it does not, then I'd test to see if setting the default to 'control panel' stops sending emails for existing subscriptions. That way we don't have to delete existing subscriptions :) I'll actually be doing that after this post :D

If either one of those works, then you do not need to incorporate anything different as I have already done so in the modified version of the old mod.

In regards to the host, I told them exactly what I tweaked the old mod to do (ie turn off pms, turn off vcard, deselect the 3 email settings, and change the thread description. Personally I'd rather do all of that vs just the 'receive admin emails'.

--------------- Added 1342113466 at 1342113466 ---------------

Ok simply changing the default subscription mode does not prevent emails from being sent on existing subscriptions :( If you would be so kind as to include the ability to unsubscribe from existing subscriptions that would be awesome.

Personally I don't have the blog system on, but I'm sure others do, and I can wait until you get it all sorted out since my host turned on my emails :)

[kh99]

I want to run a couple of tests today with regards to the thread subscriptions. Mainly I want to know if setting the default to 'do not subscribe' will prevent existing subscriptions from sending emails. If it does not, then I'd test to see if setting the default to 'control panel' stops sending emails for existing subscriptions. That way we don't have to delete existing subscriptions :) I'll actually be doing that after this post :D

Let me know what you find out - but like I said, I'm pretty sure neither the "admin email" nor the "default subscription" settings affect existing subscription emails. But what you can do is set all existing subscriptions to "no notification" (or whatever it's called) using a few queries, that way if someone changes their mind they won't have lost their list of subscriptions.

[John Lester]

I guess you read my first reply but missed the second before logging off :) Changing the option does not affect existing subscriptions. I absolutely love the idea of setting the existing subscriptions to "do not subscribe" instead of unsubscribing them. That is awesome!

So are you done yet? ;p ;p

--------------- Added 1342115148 at 1342115148 ---------------

I have finished placing the unsubscribe link in all of the email body text phrases. I haven't tested EVERY possible scenario but I feel confident enough that adding a couple of lines in each phrase will cover any outgoing email with the exception of the admin sending mass emails. I'll look into that a little later today.

For those interested this is what I added to all of my "email body text" phrases.


Click the link to unsubscribe from further emails from $vboptions[bbtitle]:

$vboptions[bburl]/misc.php?do=unsubscribe

If you can not click on the link copy and paste the following url into your browsers address bar; www.yoururlhere.whatever/misc.php?do=unsubscribe


There are some phrases that already contain $vboptions[bbtitle] so make sure you delete them or you will have duplicate bbtitles in one email ;)

[kh99]

I guess you read my first reply but missed the second before logging off :) Changing the option does not affect existing subscriptions. I absolutely love the idea of setting the existing subscriptions to "do not subscribe" instead of unsubscribing them. That is awesome!

So are you done yet? ;p ;p

Yeah, I did see your reply after I posted. I'll work out how to reset the subscriptions.

I'm not done - I have to confess I haven't made any progress on it today, and I have to go out for a while this afternoon. Maybe tonight.

Edit: ...and if you have any other changes you want me to look at and possibly include, go ahead and post them.

[John Lester]

With the reset of the subscriptions I can't think of anything else that would be needed.

[Simon Lloyd]

With the reset of the subscriptions I can't think of anything else that would be needed.Don't know what you think of this but maybe a way to re-subscribe or request a mail back with the subscribe options in it. I only say this because you know how people are, if they've turned it off as sure as eggs are eggs they'll want to turn it back on at some point!, if we could make that process automated then they won't bother admins or staff and create more work for their pay! (what, you don't pay your staff? :D )

[John Lester]

So you want a "re-subscribe" option? How would that work if they delete the unsubscribe email? Perhaps have this plugin send the user a pm explaining what options were changed in case they would like to change them back in the future. If I could pay my staff I surely would :D

[Simon Lloyd]

If I could pay my staff I surely would :DMe too!, i suppose the pm is fine as long as notifications we're turning off are ones that we can turn back on easily, if for instance you blocked birthday emails that would be done with a database query...etc so not something we can easily turn back on this is why i suggested a way of requesting a "Subscribe Options" email, if we're just blanket turning off admin mails then thats easy enough to direct them on how to turn those back on.

Like i've said before, just thinking or brain farting out loud :)

[John Lester]

Actually the blocking of birthday emails is done by deselecting the "receive emails from admin" option. But yes the pm would contain a list of options that were changed and how to change them back to however you want them :)

--------------- Added 1342196403 at 1342196403 ---------------


Edit: ...and if you have any other changes you want me to look at and possibly include, go ahead and post them.

It just dawned on me, I don't know for sure which settings you have already included into this mod.

What I think it should include (to be the most comprehensive unsubscribe mod to date) is: turn off email from admins, turn off email from other members, turn off friend requests, turn off emails for new pms, turn off vcard download, set subscriptions to "through control panel only", reset existing subscriptions to "through control panel only", move the user to a different user group, send the user a pm explaining what changes where made and where to change them back if they want.


$db->query("UPDATE " . TABLE_PREFIX . "user SET options= options - 256 WHERE (options & 256) AND userid=" . $vbulletin->userinfo['userid']);
$db->query("UPDATE " . TABLE_PREFIX . "user SET options= options - 2097152 WHERE (options & 2097152) AND userid=" . $vbulletin->userinfo['userid']);
$db->query("UPDATE " . TABLE_PREFIX . "user SET options= options - 32 WHERE (options & 32) AND userid=" . $vbulletin->userinfo['userid']);
$db->query("UPDATE " . TABLE_PREFIX . "user SET options= options - 4096 WHERE (options & 4096) AND userid=" . $vbulletin->userinfo['userid']);
$db->query("UPDATE " . TABLE_PREFIX . "user SET autosubscribe = 0 WHERE autosubscribe = 1 OR 2 OR 3 AND userid=" . $vbulletin->userinfo['userid']);

That bit takes care receive email from members, receive email on friendship request, new subscriptions to "through control panel only", turns off vcard, and turns of emailonpm.

[John Lester]

So at 4 am this morning I woke up with an idea on how to incorporate into this mod Simon's idea for a resubscribe link.

In the pm that is sent out stating that such and such settings have been changed to ensure you no longer receive emails from our site, a link is included to "resubscribe" the user. The link would take you to a page (not the user cp) with only the settings that were changed and explain in a little more detail what the settings do and the option to change / reselect them. Then the users clicks a radio button saying "yes I want to receive emails from websitehere that correspond with the settings I have selected". Once the radio button is checked the input box for their email address becomes available and they enter their email and click the resubscribe box.

That way a user has to "double opt in" and has no ground to claim any of the emails as spam :)

Under the resubscribe box is a note that states "In order to receive emails from websitehere you need a valid email address. To update/change your email address click here before submitting." That link would open a new window to the change email / password in the users cp (since they're already logged on to read the pms).

I think if you could incorporate that, this mod will become very popular, and perhaps if the devs really do have some smarts they'll incorporate it into the core at some point :)

[Simon Lloyd]

John, exactly my thoughts, only i was thinking of it in a one shot mail, the one they get to unsubscribe also has the options to resubscribe (negates the need for extra coding in templates..etc) but your idea can work just as well and like you say, a seperate page gives you the space to explain a little more (Kevin, don't forget to make this compatible with at least vb3.7.x onward, it's gotta be the most "Must have" mod idea to have ever come about), and hopefully they'll take this on in vb5 :)

[kh99]

The only small issue I see with the resubscribe stuff is that it's actually possible (as far as I know) for a member to be subscribed to hundreds of threads and forums and have a different type of notification for each, so if we set them all to "no email" then we won't remember what they were (unless of cource we save all that somewhere - I suppose we could make a huge string).

[Simon Lloyd]

And that is the issue, if you turn off forum subscriptions (the user may have only wanted to turn off the odd one or two) then it's a pain for the user to resubscribe to each, so either you do need to save all the unsubscribes in a string in the db or explain to the user that it turns all of them aff and that they'd have to visit each forum to turn on the ones they want (i've built a mod for this vb3 for easy selection all in one place, which if you want the code i'll gladly donate it), see that attached for how my mod works :)

[John Lester]

Just set them to "through control panel only" that way they keep their subscriptions but don't get the emails. They can then choose to receive emails upon thread subscription, sure they'll have to go through it thread by thread ... but it's the price they'll have to pay for unsubscribing then resubscribing.

My thoughts are, if they want to unsubscribe from all emails then it's on them to reselect the ones they want if they change their mind later on.

I think having options in the ACP to choose what the unsubscribe button does would be the best bet. Then you would have to tell them (admins) to change the phrase(s) to reflect what settings are changed when a user clicks the button.

The reason I suggest a pm vs email for the resubscribe is (at least in my case(s) ), the user will most likely delete the email and therefore have no idea what was changed. There is a better chance at them saving a pm then the email ;)

[John Lester]

Hi Kevin :)

Have you been able to work on this mod lately? Curious to know where you are at with it :)

[kh99]

I haven't made much progress but I haven't forgotten about it - I'll get to it soon. :)

Have you solved all your email problems?

[John Lester]

As far as the host knows I have ;) I'm actually just waiting for this mod as (assuming it does everything we've discussed) it's a complete solution for unsubscribing :)

[John Lester]

I remember it being brought up on the first page, were you going to incorporate and option to move the unsubscribers to a different user group? I think that should be an option so we can see how many people are unsubscribing ;)

[kh99]

Hey John - I kind of figured you had given up on this since I saw that you had posted in one of the other mods. Anyway, I asked Simon Lloyd if he wanted to take over working on this (so it might actually get done), but I think he's been too busy.

[John Lester]

Send me what you have and I'll fiddle around with it :D I only posted in that other mod to help that guy. This mod here (this thread) is the one I'm after ;)

--------------- Added 1343974087 at 1343974087 ---------------

Ok all I have left to do is code the move to a different user group and code the options for the ACP. I worked out how to change existing subscriptions to "through control panel only" :)

I'll wait and see what you've got before I continue as I'm quite tired tonight.

[Simon Lloyd]

Hi John, Kevin's right, i haven't had time at the moment, i've been looking at the product that Kevin uploaded here and he's pretty6 much got it down except the re-subscribe or keeping a record of what the user unsubscribed from, or allowing a page where all the things they unsubscribed from are present to resubscribe, i cant say when i'll have time at the moment as i have many things going on on along with 7 day working at the moment.

[John Lester]

That's alright I've been working on it myself :) However I'm a bit stumped. Trying to add the option (I add them one at a time to make trouble shooting my screw ups easier :D ) to change new thread subscriptions to "through control panel only" and when I import the XML I get the following error;

XML Error: XML_ERR_NAME_REQUIRED at Line 153

Line 153
<phrase name="setting_adv_unsublink_new_thread_sub" date="1312347600" username="Mike_W" version="1.0"><![CDATA[Set new thread subscriptions to through control panel only.]]></phrase>Every solution I have seen says it's the & not being parsed (which I have none), so that's where I'm stumped, it looks fine to me.

The setting looks like (followed Kevin's format obviously)

<setting varname="adv_unsublink_new_thread_sub" displayorder="40">
<datatype>free</datatype>
<optioncode>yesno</optioncode>
<defaultvalue>0</defaultvalue>
</setting>

[kh99]

Send me what you have and I'll fiddle around with it :D

The last one posted here is pretty much all I have.


...and when I import the XML I get the following error;

XML Error: XML_ERR_NAME_REQUIRED at Line 153

Line 153
<phrase name="setting_adv_unsublink_new_thread_sub" date="1312347600" username="Mike_W" version="1.0"><![CDATA[Set new thread subscriptions to through control panel only.]]></phrase>


What does the line before that look like?

[John Lester]

Line 152
<phrase name="setting_adv_unsublink_verify_title" date="1312347600" username="Mike_W" version="1.0"><![CDATA[Verify Email (Double Opt-Out)]]></phrase>

[kh99]

Hmm...that doesn't help. Look at the surrounding line for missing quote or unusual characters.

[John Lester]

It was a missing ! in the following line :p

[John Lester]

I'm about to head to bed after staring at this damn mod for 2 hours trying to find my error.

It imports just fine and the settings work, but when I go to /mics.php?do=unsubscribe all I get is the smiley page. No errors.

So I'm going to attach it here and maybe someone can find the error for me :D

It's not complete yet, still have to do the user group stuff, the resubscribe stuff, and find a way to store the existing thread subscriptions. The plan for the subscriptions is to create another table to copy the users subscriptions to and the query to take the data from the backup table and repopulate the original.

EDIT: It was a simple typo in the template name :D So with no errors I can now work on the user group stuff :)

[John Lester]

Ok I'm a bit stuck and a little confused (and not about the same thing :D ).

I basically followed Kevin's "select" and "update" queries to add the other options to be changed. However I'm a little confused why the "select" query selects username and userid when the "update" query doesn't use those variables.

Select query

$unsubscribe = $db->query_first("SELECT username, userid, email FROM ".TABLE_PREFIX."user WHERE email = '$esc_email' AND options &16");
Update query

$unsubscribe = $db->query("UPDATE ".TABLE_PREFIX."user SET options = options - 16 WHERE (options & 16) AND email= '" . $db->escape_string($email) . "'");
Now to the part I'm stuck on.... thread subscriptions. When I use my "select" query it triggers this if statment:

if (empty($unsubscribe['email']))
{
eval(standard_error(fetch_error('advanced_unsubscr ibe_link_error_unsubscribed', $vbulletin->options['contactuslink'])));
}
This is the select query

$unsubscribe = $db->query_first("SELECT * FROM ".TABLE_PREFIX."subscribethread WHERE userid = '$id' AND emailupdate = 1 OR 2 OR 3");
I have other issues with thread subscriptions, but I figure I should find out what's wrong with my select query first :D

Is it because the subscribethread table doesn't have an email column?

[kh99]

I don't remember why the query included username and userid, I suppose I thought I needed them (or might need them) for one of the other features we talked about. But it also doesn't hurt anything to have them there.

You're right, the subscription table doesn't include email, so there's no reason to check for that field being blank. Also I don't think you're checking correctly for multiple values of emailupdate. I think you want something like:

$unsubscribe = $db->query_first("SELECT * FROM ".TABLE_PREFIX."subscribethread WHERE userid = '$id' AND emailupdate IN (1, 2, 3)");


I think have "OR 2 OR 3" on the end of the query like that will cause it to return every row.

Oh, that brings up another issue - since you want to return every record of a user's subscriptions, you can't use query_first(), you need to use query_read() and then use a loop to get each record.

[John Lester]

Thanks for the tips on thread subscription :) I thought it was the '$id' that was returning every row, I'll use IN and see what happens :D

[John Lester]

Ok I have confused myself once again :D The subscribethread table has one column (subscribethreadid) that is auto increment, according to this website https://www.vbulletin.com/docs/html/codestandards_sql_query I'm not supposed to include that field in an insert query.

I have tried the following queries which result in different errors.



$db->query_write("
INSERT INTO ".TABLE_PREFIX."unsub_subscribethread
SELECT userid,
threadid,
emailupdate,
folderid,
canview
FROM ".TABLE_PREFIX."subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)");
error


Database error in vBulletin 4.1.12:

Invalid SQL:

INSERT INTO unsub_subscribethread
SELECT userid,
threadid,
emailupdate,
folderid,
canview
FROM subscribethread
WHERE userid = ''
AND emailupdate IN (1, 2, 3);

MySQL Error : Column count doesn't match value count at row 1
Error Number : 1136
Request Date : Sunday, August 19th 2012 @ 08:18:50 PM
Error Date : Sunday, August 19th 2012 @ 08:18:50 PM
Script : http://localhost/vb4112/misc.php
Referrer : http://localhost/vb4112/misc.php?do=unsubscribe
IP Address : 127.0.0.1
Username : zombie
Classname : vB_Database
MySQL Version : 5.1.36-community-log
I don't understand how to use VALUES in this case (because the values can't be hard coded IE 5 for userid etc etc), everything I've tried results in syntax errors. So what would I put after VALUES?


$db->query("
INSERT INTO ".TABLE_PREFIX."unsub_subscribethread
VALUES What goes here?
SELECT (userid, threadid, emailupdate, folderid, canview)
FROM ".TABLE_PREFIX."subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)");
So I went with the SELECT * to insert everything from subscribethreads.


$db->query_write("
INSERT INTO ".TABLE_PREFIX."unsub_subscribethread
SELECT *
FROM ".TABLE_PREFIX."subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)");
error

None but it doesn't populate unsub_subscribethread, is it because of the auto increment column in subscribethread?

According to this website http://www.w3schools.com/sql/sql_select_into.asp I should be using SELECT INTO to copy subscribethread to unsub_subscribethread (vs INSERT INTO) at least that's how I understand it so I tried;


$db->query_write("
SELECT userid,
threadid,
emailupdate,
folderid,
canview
INTO ".TABLE_PREFIX."unsub_subscribethread
FROM ".TABLE_PREFIX."subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)");
error


Database error in vBulletin 4.1.12:

Invalid SQL:

SELECT userid,
threadid,
emailupdate,
folderid,
canview
INTO unsub_subscribethread
FROM subscribethread
WHERE userid = ''
AND emailupdate IN (1, 2, 3);

MySQL Error : Undeclared variable: unsub_subscribethread
Error Number : 1327
Request Date : Sunday, August 19th 2012 @ 08:28:26 PM
Error Date : Sunday, August 19th 2012 @ 08:28:26 PM
Script : http://localhost/vb4112/misc.php
Referrer : http://localhost/vb4112/misc.php?do=unsubscribe
IP Address : 127.0.0.1
Username : zombie
Classname : vB_Database
MySQL Version : 5.1.36-community-log
Now I can only guess that this is caused by me creating the table unsub_subscreibethread in the "<installcode>" at the start of the plugin (code is below for reference). If I understand what SELECT INTO is supposed to do, it's creating the table "unsub_subscribethread" on the fly? So this wouldn't work because it would try to create the table each time the script is run?


$db->query_write("CREATE TABLE IF NOT EXISTS ".TABLE_PREFIX."unsub_subscribethread LIKE ".TABLE_PREFIX."subscribethread");

[kh99]

I haven't tried these, but I think there's two things you can do: when a column is auto increment, you don't want to supply a value but you can put NULL as a placeholder. You can put NULL where you'd put a column name in a select, and that value will always be null. So I think this would work:

$db->query_write("
INSERT INTO ".TABLE_PREFIX."unsub_subscribethread
SELECT NULL,
userid,
threadid,
emailupdate,
folderid,
canview
FROM ".TABLE_PREFIX."subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)");


The other way would be to list each column and leave out the id column, like:

$db->query_write("
INSERT INTO ".TABLE_PREFIX."unsub_subscribethread
(userid, threadid, emailupdate, folderid, canview)
SELECT userid,
threadid,
emailupdate,
folderid,
canview
FROM ".TABLE_PREFIX."subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)");


BTW, for future reference you can see some of this in the mysql manual, for instance here's the page for the INSERT statement: http://dev.mysql.com/doc/refman/5.0/en/insert.html

[John Lester]

Well so far nothing has worked as far as populating unsub_subscribethread. However plugging each query into phpmyadmin (removing the table prefix and defining a user) works.

So it's either not pulling the userid properly, or my loop is screwed up (which certainly wouldn't surprise me). I'll have to do some more reading and hopefully find an good loop example instead of the crap that's in the tutorials.

[John Lester]

A week ago or so I screwed up and deleted my "good" file for the mod and instead saved the "working" copy. Well when I navigate to the page on my local test site for testing I get the following error;


Parse error: parse error, expecting `']'' in C:\wamp\www\vb4112\misc.php(89) : eval()'d code on line 85
What on earth is it looking for? I have not found any open brackets anywhere :(

While I am on this subject ... why are there two ' after the ] ? For that matter why is there a ` before it?

misc.php code from line 80 to 89


// ######################### REQUIRE BACK-END ############################
require_once('./global.php');

// ################################################## #####################
// ######################## START MAIN SCRIPT ############################
// ################################################## #####################

($hook = vBulletinHook::fetch_hook('misc_start')) ? eval($hook) : false;

// ############################### start buddylist ###############################

[Simon Lloyd]

The error isn't in misc.php it's in one of your plugin's, misc.php is called when one of yourplugins doesn't give a result.

[John Lester]

I know it's not in misc.php and the only plugin is the mod I'm working on. What I don't understand about the error is ... what exactly is it looking for?

I mean there are no ` in the mod, so is it looking for ]' or ']' or ']'' or ] ?

--------------- Added 1346177661 at 1346177661 ---------------

Found the elusive missing ] :D

[Simon Lloyd]

Good man :), i know what it's like, you look so hard for so long you get code blind and can't see the nose in front of you!

[John Lester]

Well it's my own fault. I had two copies of the xml, one was the master copy and the other was used to work on. I accidentally deleted the master copy instead of the borked working copy :D

So now I'm still using the borked working copy (for some reason it keeps giving me the error that I'm already unsubscribed even though I'm not), but once I get it unborked it will become the new master.

[John Lester]

I'm still stuck on the existing subscribe threads. I'm unable to get a working loop done. Just wanted to update those that are following this, it's not forgotten it's just stalled :D

[John Lester]

I need a fresh set of eyes to help me find out why this code is returning a parse error (or tell me how screwed up the code itself is as I'm just not getting this stupid loop thing :( ) Since the rest of the queries start with $unsubscribe does this one have to use it as well? I was understanding that the loop variable should be different but as I said before I'm obviously not getting it :D

Another related question, in order to get around the email error being returned with this query, would I use another JOIN statement to add the email to the insert query? If so which one?


if ($vbulletin->options['advanced_unsubscribe_link_thread_sub_backup'])
$thead_sub = $db->query_read("
SELECT NULL,
userid,
threadid,
emailupdate,
folderid,
canview
FROM ".TABLE_PREFIX."subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)
AND usergroupid NOT IN (5, 6, 7)");
}
$thread_sub = array();
while($row = mysql_fetch_assoc($thread_sub))
$db->query("
INSERT INTO ".TABLE_PREFIX."unsub_subscribethread LEFT JOIN user ON (user.usergroupid = user.usergroupid)
SELECT NULL,
userid,
threadid,
emailupdate,
folderid,
canview
FROM ".TABLE_PREFIX."subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)
AND usergroupid NOT IN (5, 6, 7)");
}

[nerbert]

I need a fresh set of eyes to help me find out why this code is returning a parse error (or tell me how screwed up the code itself is as I'm just not getting this stupid loop thing :( ) Since the rest of the queries start with $unsubscribe does this one have to use it as well? I was understanding that the loop variable should be different but as I said before I'm obviously not getting it :D

Another related question, in order to get around the email error being returned with this query, would I use another JOIN statement to add the email to the insert query? If so which one?

Missing curlies


if ($vbulletin->options['advanced_unsubscribe_link_thread_sub_backup'])
{
$thead_sub = $db->query_read("
SELECT NULL,
userid,
threadid,
emailupdate,
folderid,
canview
FROM ".TABLE_PREFIX."subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)
AND usergroupid NOT IN (5, 6, 7)");
}
$thread_sub = array();
while($row = mysql_fetch_assoc($thread_sub))
{
$db->query("
INSERT INTO ".TABLE_PREFIX."unsub_subscribethread LEFT JOIN user ON (user.usergroupid = user.usergroupid)
SELECT NULL,
userid,
threadid,
emailupdate,
folderid,
canview
FROM ".TABLE_PREFIX."subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)
AND usergroupid NOT IN (5, 6, 7)");
}
[/QUOTE]

[John Lester]

Oh my .... how silly of me :D Thanks nerbet :)

[John Lester]

Ok so I'm still having trouble with the stupid looping through the subscribethread table and decided that I needed to exclude the mods, supermods, and admins from having their subscriptions or usergroups (that code isn't shown here) changed.

However I'm a bit confused on how to do this. I need to LEFT JOIN the usergroupid from the user table, but the following code spits out a syntax error and I'm not seeing it.


// If you selected to backup existing thread subscriptions this code will be executed.
if ($vbulletin->options['advanced_unsubscribe_link_thread_sub_backup'])
{
// Copy users existing thread subscriptions to the unsub_subscribethread table except for mods, admins, and supermods
$db->query("
INSERT INTO ".TABLE_PREFIX."unsub_subscribethread LEFT JOIN user ON (user.userid = user.usergroupid)
SELECT NULL,
userid,
threadid,
emailupdate,
folderid,
canview
FROM ".TABLE_PREFIX."subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)
AND usergroupid NOT IN (5, 6, 7)");
}

[Simon Lloyd]

Other than spacings around TABLE_PREFIX it seems fine (altough i haven't checked the tables...etc it calls)// If you selected to backup existing thread subscriptions this code will be executed.
if ($vbulletin->options['advanced_unsubscribe_link_thread_sub_backup'])
{
// Copy users existing thread subscriptions to the unsub_subscribethread table except for mods, admins, and supermods
$db->query("
INSERT INTO " .TABLE_PREFIX. "unsub_subscribethread LEFT JOIN user ON (user.userid = user.usergroupid)
SELECT NULL,
userid,
threadid,
emailupdate,
folderid,
canview
FROM " .TABLE_PREFIX. "subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)
AND usergroupid NOT IN (5, 6, 7)");
}

--------------- Added 1349116350 at 1349116350 ---------------

This should help with your syntax (remember to only use the query part rather than the whole php :)) http://developer.mimer.com/validator/parser200x/index.tml#parser

[kh99]

Are you getting an sql syntax error or a php syntax error?

You're trying to select the rows from subscribethread for only one user so you really only need to check the usergroupid and either do the query or not. If you don't have the usergroupid for that user available then you do save one query by combining it like that, but since this is for something that is executed rarely compared to everything going on on the forum, it might be a better idea to do two separate queries and have the code be less complicated.

Having said all that, the way you have it is doing a join to the UPDATE query, but the WHERE goes with the select, so it will probably tell you that there's no column named 'usergroupid'. Maybe you should do this instead:

INSERT INTO " .TABLE_PREFIX. "unsub_subscribethread AS unsub_subscribethread
SELECT NULL,
userid,
threadid,
emailupdate,
folderid,
canview
FROM " .TABLE_PREFIX. "subscribethread
LEFT JOIN " .TABLE_PREFIX. "user AS user ON (user.userid = unsub_subscribethread.userid)
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)
AND usergroupid NOT IN (5, 6, 7)

[John Lester]

Hmmm so it would be better to run the check before executing any of the queries (I was planning on excluding those user groups from each query) instead of building the conditional into the query? I suppose I should go a step further and create a setting to allow admins to exclude certain user groups, what do you guys think?

My thinking was that since the subscribethread table doesn't have the usergroupid column, that I needed to call if from the user table. But I don't see why you went with user.userid = unsub_subscribethread.userid?

I'm pretty much stalling on this due to the fact that I just can't seem to wrap my head around the proper loop. I've done some reading and I've searched for examples, but nothing I have found seems to have "clicked" with my brain yet :D

[kh99]

Hmmm so it would be better to run the check before executing any of the queries (I was planning on excluding those user groups from each query) instead of building the conditional into the query? I suppose I should go a step further and create a setting to allow admins to exclude certain user groups, what do you guys think?

Yeah, something like that sounds better to me.

My thinking was that since the subscribethread table doesn't have the usergroupid column, that I needed to call if from the user table. But I don't see why you went with user.userid = unsub_subscribethread.userid?

When you do a join with an 'ON', the condition in the ON needs to be true for the rows that get joined. In your case, it needs to compare userids so the parts of the joined rows are for the same user (otherwise the results don't make a lot of sense). The 'ON' doesn't affect which columns appear in the results.


I'm pretty much stalling on this due to the fact that I just can't seem to wrap my head around the proper loop. I've done some reading and I've searched for examples, but nothing I have found seems to have "clicked" with my brain yet :D

I'm sorry I dumped this on you. I keep hoping maybe I'll get back to it, but instead I just keep piling up other mods that are half done. :) But I can certainly help with things like getting loops to work if you want.

[John Lester]

Don't worry about dumping this on me, I'll eventually either figure out the loop or ask you to figure it out for me :D

Your reason for editing the post has me intrigued ;)

Now back to the JOIN statement just for clarification since I'm almost done setting up the exclude user groups settings :)

user.userid = unsub_subscribethread.userid ... I thought that this would only pull the userid from the user table, hence my confusion. The entire point of joining the user table was to grab the usergroupid for use in checking user groups in the query. I am assuming that is how JOIN works (grabbing only the column listed in the ()'s ) but I also assumed that I would be done with this mod by now :D

[kh99]

Your reason for editing the post has me intrigued ;)

Yeah, that's my way of saying I made so many typos or other mistakes that part of it was almost unreadable.


user.userid = unsub_subscribethread.userid ... I thought that this would only pull the userid from the user table, hence my confusion. The entire point of joining the user table was to grab the usergroupid for use in checking user groups in the query. I am assuming that is how JOIN works (grabbing only the column listed in the ()'s ) but I also assumed that I would be done with this mod by now :D

You're really creating a "virtual" table that you're selecting from, and the ON is just instructions on how to do that. You still need to list any columns you want returned (from either table) after SELECT. I didn't list user.usegroupid in the code I posted above because I only used it in the WHERE and didn't think there was any need to include it in the returned data.

[John Lester]

AH I see what happened here, you were referring to the code I pasted (#86) showing a failed attempt at looping :D

I should've mentioned that I scrapped that bit of code and my question regarding the LEFT JOIN was for the code in post #89 :)

But it's a moot point now, I get the gist of what you were saying in regards to the LEFT JOIN :)

[John Lester]

I'm getting a parse error when I have this code un-commented, I suspect it's the LEFT JOIN but then again what do I know :D

I'm trying to join the user table to pull the email column so the mod doesn't return the email error if there is no email column in the table.


// If you selected to change existing thread subscriptions this code will be executed
if ($vbulletin->options['advanced_unsubscribe_link_existing_thread_sub'])
{
// Sets existing thread subscriptions to through control panel only
$db->query{"
UPDATE " .TABLE_PREFIX. "subscribethread
LEFT JOIN " .TABLE_PREFIX. "user AS user ON (user.email = user.userid)
SET emailupdate = 0
WHERE emailupdate IN (1,2,3)
AND userid = $id
AND email= '" . $db->escape_string($email) . "'");
}

[squidsk]

Your ON clause is wrong, one of the fields needs to be from the subscribethread table.

[John Lester]

Thanks for replying squid :) I changed the ON clause and still have a parse error :(


// If you selected to change existing thread subscriptions this code will be executed
if ($vbulletin->options['advanced_unsubscribe_link_existing_thread_sub'])
{
// Sets existing thread subscriptions to through control panel only
$db->query{"
UPDATE " .TABLE_PREFIX. "subscribethread
LEFT JOIN " .TABLE_PREFIX. "user AS user ON (subscribethread.userid = user.email)
SET emailupdate = 0
WHERE emailupdate IN (1,2,3)
AND userid = $id
AND email= '" . $db->escape_string($email) . "'");
}
Tried using this as well and get a parse error.


(user.email = subscribethread.userid)


If I comment out the block of code the mod works fine.

[squidsk]

Thanks for replying squid :) I changed the ON clause and still have a parse error :(


// If you selected to change existing thread subscriptions this code will be executed
if ($vbulletin->options['advanced_unsubscribe_link_existing_thread_sub'])
{
// Sets existing thread subscriptions to through control panel only
$db->query{"
UPDATE " .TABLE_PREFIX. "subscribethread
LEFT JOIN " .TABLE_PREFIX. "user AS user ON (subscribethread.userid = user.email)
SET emailupdate = 0
WHERE emailupdate IN (1,2,3)
AND userid = $id
AND email= '" . $db->escape_string($email) . "'");
}
Tried using this as well and get a parse error.


(user.email = subscribethread.userid)


If I comment out the block of code the mod works fine.
Shouldn't the ON block be:

ON user.userid = subscribethread.userid

You can't compare an email to a userid.

[John Lester]

Dangit ... I knew I didn't fully understand the JOINs ...

The subscribethread table doesn't have an email column, but if I JOIN the user table I don't have to mention the email table (in the JOIN) in order to use it in the UPDATE ... WHERE as long as the userid from the user table matches the userid from subscribethread table?

[squidsk]

Dangit ... I knew I didn't fully understand the JOINs ...

The subscribethread table doesn't have an email column, but if I JOIN the user table I don't have to mention the email table (in the JOIN) in order to use it in the UPDATE ... WHERE as long as the userid from the user table matches the userid from subscribethread table?
What's the point of the email address in the query? Since you have a users userid, in the $id variable, that will uniquely identify everything you need to update the appropriate stuff in the subscriptionthread table.

If what I understand you want to achieve why not use the following query:

$db->query("UPDATE " .TABLE_PREFIX. "subscribethread
SET emailupdate = 0
WHERE emailupdate IN (1,2,3)
AND userid = $id");

Also note two things, first your $db->query should use () not {} and have a ; at the end, and also an update query can only be applied to a table and not a join.

[John Lester]

Ok so JOINs can't be used in an UPDATE? I don't recall reading that anywhere :(

The reason I'm trying to include the email field is that if I don't it trips an error further along the mod about not inputting a valid email and doesn't complete the query.


// If an email isn't entered or is invalid show this error
if (empty($unsubscribe['email']))
{
eval(standard_error(fetch_error('advanced_unsubscr ibe_link_error_unsubscribed', $vbulletin->options['contactuslink'])));
}
How I understand that code it checks each query for an email address and if the query doesn't have one it spits out the error. I have a couple of ideas about how to get around that (move the code so it comes before the queries that don't have email fields, or just not use it :D ) but not sure if there's a better way.

I do use ( for the $db->queries and they do have the ; at the end :)

[John Lester]

Ok so I sorta started working on this again, and I can't figure out what's wrong here. I'm trying to copy the rows from subscribethread to unsub_subscribethread *yes it does exist*. Yes the option is selected in the ACP. The mod runs but doesn't spit out ANY errors, it also doesn't copy the rows :(

This is the select query I'm using


$unsubscribe = $db->query_read("SELECT NULL, userid, threadid, emailupdate, folderid, canview FROM " .TABLE_PREFIX. "subscribethread WHERE userid = '$id' AND emailupdate IN (1, 2,3)");

This is the code inside of the function



// If you selected to backup existing thread subscriptions this code will be exectued.
if ($vbulletin->options['advanced_unsubscribe_link_thread_sub_backup'])
{
// Copies existing thread subscriptions to the unsub_subscribethread table.
$db->query("
INSERT INTO " .TABLE_PREFIX. "unsub_subscribethread
SELECT NULL,
userid,
threadid,
emailupdate,
folderid,
canview
FROM " .TABLE_PREFIX. "subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)");
}
If I run this query in phpmyadmin it works ...


INSERT INTO unsub_subscribethread
SELECT NULL,
userid,
threadid,
emailupdate,
folderid,
canview
FROM subscribethread
WHERE userid = 1
AND emailupdate IN (1, 2, 3)

[kh99]

Are you sure $id has the right value?

[TheSupportForum]

Ok so I sorta started working on this again, and I can't figure out what's wrong here. I'm trying to copy the rows from subscribethread to unsub_subscribethread *yes it does exist*. Yes the option is selected in the ACP. The mod runs but doesn't spit out ANY errors, it also doesn't copy the rows :(

This is the select query I'm using


$unsubscribe = $db->query_read("SELECT NULL, userid, threadid, emailupdate, folderid, canview FROM " .TABLE_PREFIX. "subscribethread WHERE userid = '$id' AND emailupdate IN (1, 2,3)");

This is the code inside of the function

for example where is


// If you selected to backup existing thread subscriptions this code will be exectued.
if ($vbulletin->options['advanced_unsubscribe_link_thread_sub_backup'])
{
// Copies existing thread subscriptions to the unsub_subscribethread table.
$db->query("
INSERT INTO " .TABLE_PREFIX. "unsub_subscribethread
SELECT NULL,
userid,
threadid,
emailupdate,
folderid,
canview
FROM " .TABLE_PREFIX. "subscribethread
WHERE userid = '$id'
AND emailupdate IN (1, 2, 3)");
}
If I run this query in phpmyadmin it works ...


INSERT INTO unsub_subscribethread
SELECT NULL,
userid,
threadid,
emailupdate,
folderid,
canview
FROM subscribethread
WHERE userid = 1
AND emailupdate IN (1, 2, 3)



you havent registered the variable


$unsubscribe

here is an example of how to do so

$templater = vB_Template::create('unsubscribe');
$templater->register('$unsubscribe', $unsubscribe);
$template_hook[forumhome_wgo_pos2] .= $templater->render();

you will then need to call $unsubscribe in a template which will display the content from the query remembering to change the $template_hook

you will then need to place {vb:raw unsubscribe}
in template unsubscribe

as long as you place the correct hook location with the correct template hook the query will show in the hook location you selected

[John Lester]

Oh great now I'm even more confused ... this mod uses misc.php?do=unsubscribe as the url. In the mod a basic template is "built" and all of the other code executes fine (so far). I have a few more select queries structured like the one I posted earlier ... they seem to work just fine.

So now I'm wondering ... is the mod even executing those select queries? If it's working as is now (aside from the code in question) do I still need to tie it into a hook?

Keep in mind I'm a complete novice at this, all I'm doing is added options to an existing mod (that was marked as reusable and updated for 4.x by kh99).

Just for shits n giggles I'm going to attach what I have so far, if someone would be kind enough to look at it and tell me if there's a better way to go about doing this I might as well find out now and switch gears :D