How to pass users pw through email?

[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

[kh99]

Originally Posted by 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?

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

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

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

[kh99]

Originally Posted by John Lester

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

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

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

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]

Originally Posted by 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.

Shouldn't the ON block be:

ON user.userid = subscribethread.userid

You can't compare an email to a userid.