Ignoring Custom Titles

[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/Co...s.html#IDX1235)

Try changing the line:

PHP Code:

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


into:

PHP Code:

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/AN...ubqueries.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]

Originally Posted by MarcoH64

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.