I'v recently installed the Site User List module and it works great except when I try to use the Role restriction functionality. I get the following error in the Site User List config as soon as I select a role to be resticted:

user warning: 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 distinct sul_ur.uid from users_roles as sul_ur where sul query: CREATE TABLE site_user_list_table AS SELECT DISTINCT u.uid as `@uid`, t_1.value as `@profile_organisation`, t_2.value as `@profile_contact_address`, t_3.value as `@profile_region`, t_4.value as `@profile_contact_person`, t_5.value as `@profile_contact_person_role`, t_6.value as `@profile_phone`, t_7.value as `@profile_fax`, t_8.value as `@profile_email`, t_10.value as `@profile_0800`, t_11.value as `@profile_profile`, t_12.value as `@profile_maori` FROM users as u LEFT OUTER JOIN profile_values as t_1 on (u.uid = t_1.uid and t_1.fid = 1) LEFT OUTER JOIN profile_values as t_2 on (u.uid = t_2.uid and t_2.fid = 2) LEFT OUTER JOIN profile_values as t_3 on (u.uid = t_3.uid and t_3.fid = 3) LEFT OUTER JOIN profile_values as t_4 on (u.uid = t_4.uid and t_4.fid = 4).................... and so on

Also whenever I have the Roles restriction active, and I view the user list, it comes up empty and the following error is displayed:

user warning: Table 'devint5_rja.site_user_list_table' doesn't exist query: SELECT DISTINCT `@uid`, `@profile_organisation`, `@profile_contact_address`, `@profile_region`, `@profile_contact_person`, `@profile_contact_person_role`, `@profile_phone`, `@profile_fax`, `@profile_email`, `@profile_0800`, `@profile_profile`, `@profile_maori` FROM site_user_list_table as cd ORDER BY `@profile_organisation` ASC

I'm using MySQL 4.0.24 and I saw the comment in the .module file where it says subselects are not available before MySQL 4.1 and was wondering if that had something to do with it. If that is the case is there any way I can remedy this and avoid the subselects?

It would be great if I can fix this asap as the project I'm working on requires just that kind of functionality and has a tight due date.

Thanks!

Comments

pukku’s picture

pukku commented
Assigned: Unassigned » pukku

Hi! Although I'm not going to update the official module (really, you should upgrade MySQL), you might test the following to see if it would work:

In site_user_list_roles.module, around line 109, you should see:

      $ret['where'][] = '(u.uid'
              . ' ' . $op . ' '
              . '(select distinct sul_ur.uid from {users_roles} as sul_ur'
              . ' where sul_ur.rid in (' . implode(',', $roles) . ')))'
              ;

Instead of this, you could try:

      $ret['joins'][] = 'INNER JOIN {user_roles} as sul_ur '
                      . 'ON (sul_ur.uid = u.uid '
                      . 'and sul_ur.rid'
                      . ' ' . $op . ' '
                      . '(' implode(',', $roles) . '))';

I make _no_ guarantee that this will work; in particular, I'm not sure that you won't get multiple copies of each user.

If it does work, do let me know...

Ricky

vaniavv’s picture

vaniavv commented

Hi pukku,

Thanks for the speedy response. I tried the code you suggested and I'm getting the same error. There was a typo with . '(' implode(',', $roles) . '))' which I changed to . '(' . implode(',', $roles) . '))' but it still didn't work.

It's not my sevrer so unfortunately I can't do much about the MySQL version... Anything else you can suggest?

vaniavv’s picture

vaniavv commented

Just played with it some more... The reason it wasn't working was another typo - it was doing an INNER JOIN on table user_roles instead of users_roles. Once that was fixed it worked perfect. So the corrected code is

$ret['joins'][] = 'INNER JOIN {users_roles} as sul_ur '
                      . 'ON (sul_ur.uid = u.uid '
                      . 'and sul_ur.rid'
                      . ' ' . $op . ' '
                      . '(' . implode(',', $roles) . '))';

Thanks heaps!

pukku’s picture

pukku commented
Title: MySql error when using Roles restriction » Enabling role restrictions on MySQL < 4.1
Status: Active » Fixed

Cool. Right now, I'm not going to put this into the default module (I'm still not 100% sure it works in all cases), but I've changed the title on this issue so that other people can find it...

Ricky

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)
frerin’s picture

frerin commented

The same error here:

When I try to enable the Role :

user warning: 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 distinct sul_ur.uid from dp5users_roles as sul_ur where query: CREATE TABLE dp5site_user_list_table AS SELECT DISTINCT u.uid as `@uid`, t_28.value as ...... on line 172.

But I cannot find the place, where the code shall be changed as it was suggested in this fix. So, how to get rid of the problem?

pukku’s picture

pukku commented

It's located in site_user_list_roles.module, around line 109.