Ignoring Custom Titles

[mawby]

Ok, here's the problem. Using paid subscriptions, a member pays their membership fee and gets moved into a "Members" group which amongst other things allows the user to have a custom title. When their membership expires they get moved into an "Expired" group. This "Expired" group does not allow custom user titles, and has a user title of "Expired" so our members can easily see what's going on. However, if a custom title was created for the user whilst in the "Members" group, this custom title is still shown when they get moved into the "Expired" group, even though custom titles are turned off for that group.

I contacted vbb about it and they said it wasn't a bug, but it was an oversight, and suggested I ask here for some help with it.

I don't want the custom titles cleared, but rather have it so that if the user is in a group that doesn't allow custom titles then any custom title will be ignored. That way when they pay their membership again their previous custom title comes back.

Can anyone help?

[Marco van Herwaarden]

It wouldn't be to difficult to make a script that would update the Use Custom Title field in the user table based on the settings in the groups.

[djjeffa]

yea I have the same problem I have benn manually changing them back but its a pain.

[mawby]

It wouldn't be to difficult to make a script that would update the Use Custom Title field in the user table based on the settings in the groups.I'm going to be cheeky now and ask for such a script. :)

[Marco van Herwaarden]

Will try to find some time to have a go at it today (unless someone else already do so).

Please bump me if i didn't come back on this in 2 days (memory overflow) ;)

[Marco van Herwaarden]

Ok here you go, load the attached file into you includes/cron directory, make sure permissions are set correct.

You can schedule it to run every hour, or every day or whatever suits you.

A few remarks though:
- in this version it only work on primary usergroups
- Because of the was vB works the custom usertitle is lost, even if the user would have permission to use a custom title again in a later stadium, the usertitle have been reset.


Please post feedback (positive or negative) :D

PS I did a quick testrun on a testboard. This is NOT fully tested yet, so you should take care of testing on a testboard yourself before deploying it to a production baord.

Edit: File removed, see later post for new version.

[mawby]

I just tried it on my 3.0.3 NT test system and it errors. :( Cheers for trying though. It doesn't give me any clues as to whats wrong either.

[Marco van Herwaarden]

What error you get? And how did you try it? From Scheduled tasks?

Been testing it on (a heavily hacked) test board on winXP. Will try it against a clean install tomorrow.

[Marco van Herwaarden]

No feedback anymore?

You MUST run it from scheduled tasks.

PS Is there anybody else who tried it with positive or negative result?

[mawby]

Sorry mate I've been busy with work. The error I get isThere seems to have been a slight problem with the The mkiv Supra Owners Club database.
Please try again by pressing the refresh button in your browser.

An E-Mail has been dispatched to our Technical Staff, who you can also contact if the problem persists.

We apologise for any inconvenience.but my test system doesn't have an e-mail server set up so I don't get an e-mail telling me what went wrong. I'll see how easy it is to set up the e-mail to work and maybe that will reveal whats going on.

[mawby]

ok, couldn't get the e-mail working but by trial and error I've traced the problem to the first SQL query. If I put it into plain SQL like...
UPDATE user AS user SET customtitle = 0 WHERE customtitle = 2 AND user.usergroupid IN (SELECT usergroupid FROM usergroup where (genericpermissions & 2048) = 0)and run it through mySQL it returns the following error...#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT usergroupid FROM usergroup where (genericpermissions & 2 ...and I'm using mySQL version 4.0.22-nt

Does that give any clues?

[Marco van Herwaarden]

Hmm running MySQL 4.1.8nt myself, but the functions used in there are nothing new i think. I copied your sql statement back into my sql manageer and it worked like a charm. Strange.

Hmm MySQL documentation not clear on this, but it might be that the IN statement not work like expected in your version of mysql (http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html#IDX1235)

Try changing the line:
AND user.usergroupid IN (SELECT usergroupid FROM " . TABLE_PREFIX . "usergroup where (genericpermissions & 2048) = 0)");

into:

AND user.usergroupid = ANY (SELECT usergroup.usergroupid FROM " . TABLE_PREFIX . "usergroup AS usergroup where (usergroup.genericpermissions & 2048) = 0)");

See also:
http://dev.mysql.com/doc/mysql/en/ANY_IN_SOME_subqueries.html

[mawby]

Still doesn't work mate, says there's an error near "(SELECT usergroup.usergroupid ". :(

I can see what the SQL is trying to do, is there a way to move the logic out into the PHP maybe?

[Marco van Herwaarden]

Give me a litle time, will try to rewrite it tonight (CET)

[Marco van Herwaarden]

Ok a new attempt. Should work on all mySQL version now.

Replace the file and try again.

[mawby]

Mate you're a star, that worked perfectly!

Cheers!

:banana:

[Marco van Herwaarden]

Thanks for the feedback.

Since more people seem to be looking for this, i might change it to include also membergroups and release it as a mini mod.

[djroketboy]

Thanks for the feedback.

Since more people seem to be looking for this, i might change it to include also membergroups and release it as a mini mod.
I'm about to install this, seeing I am having this issue as well, is there a way to set which usergroups are affected? i only need 2 groups, 1 paid members, and 2 free members that need to be used, the rest can be left as default.