Hello,

unfortunately I am not able to fix this problem and I cannot update to a new version of the module.

I am trying to use the Roles module and get this error when I try to change something in the Preferences of Admin -> Site_user_list -> Roles

When I click on the checkbox "Restrict the users in the list using user roles" I will get this error:

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 `@profile_privat_plz`, t_29.value as `@profile_privat_stadt`, t_56.value as `@profile_privat_bundesland`, t_57.value as `@profile_privat_land`, t_33.value as `@profile_privat_mobil`, t_34.value as `@profile_privat_fax`, t_2.value as `@profile_beruf_firma`, t_3.value as `@profile_beruf_stellung`, t_4.value as `@profile_beruf_strasse`, t_5.value as `@profile_beruf_plz`, t_6.value as `@profile_beruf_stadt`, t_54.value as `@profile_beruf_bundesland`, t_44.value as `@profile_beruf_studium`, t_17.value as `@profile_persoenliche_information_jahrgang`, t_24.value as `@profile_persoenliche_information_geburtstag`, t_39.value as `@profile_persoenliche_information_chatkontakt`, t_26.value as `@profile_persoenliche_information in /www/htdocs/p1234/includes/database.mysql.inc on line 172.

I guess (!!) it refers to the code in site_user_list_roles.module line 109 and following:

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

In the actual search I will get the error:

user warning: Table 'd00470b9.dp5site_user_list_table' doesn't exist query: SELECT DISTINCT `@uid`, `@name`, `@profile_persoenliche_information_spitzname`, `@profile_persoenliche_information_jahrgang`, `@profile_privat_plz`, `@profile_privat_stadt`, `@profile_privat_bundesland`, `@profile_privat_land`, `@profile_privat_mobil`, `@profile_privat_fax`, `@profile_beruf_firma`, `@profile_beruf_stellung`, `@profile_beruf_strasse`, `@profile_beruf_plz`, `@profile_beruf_stadt`, `@profile_beruf_bundesland`, `@profile_beruf_studium`, `@profile_persoenliche_information_geburtstag`, `@profile_persoenliche_information_chatkontakt`, `@profile_persoenliche_information_stimme`, `@profile_tcspezifisch_amt` FROM dp5site_user_list_table as cd ORDER BY `@name` ASC in /www/htdocs/p1234/includes/database.mysql.inc on line 172.

Also, no wonder, no user or information will be displayed in the search.

I am using Drupal 5.1, all of my databse tables have a prefix "dp5", including the site_user_list module and my MySQL Version is 4.0.22, PHP 4.4.4

I would be very gradefull, if someone can help me fix this problem.

Comments

pukku’s picture

pukku commented
Assigned: Unassigned » pukku
Status: Active » Closed (duplicate)

Hi! Please see http://drupal.org/node/151922 for an answer to this.

frerin’s picture

frerin commented

Thanks for the advice, I already found it, but it did not fix the problem.

function site_user_list_roles_site_user_list_query_sql($allowed) {
  $ret = array();

  if (variable_get('site_user_list_roles_static_use', 0)) {
    $inc_exc = variable_get('site_user_list_roles_static_type', 'exclude');
    $roles = variable_get('site_user_list_roles_static_roles', array());
    
    if (!empty($roles)) {
      $op = '';
      if ($inc_exc == 'exclude') {
        $op = 'not in';
      }
      elseif ($inc_exc == 'include') {
        $op = 'in';
      }

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

 
    }
  }
  
  if ($allowed['@sul_roles']) {
    $ret['cols'][] = "sul_roles.roles as " . site_user_list_escape_column("@sul_roles");
    $ret['joins'][] = "LEFT OUTER JOIN {site_user_list_roles} as sul_roles on (u.uid = sul_roles.uid)";
  }
  
  if (!empty($ret)) {
    return array('site_user_list_roles' => $ret);
  }
}

I get Error:

Parse error: syntax error, unexpected T_STRING in www/htdocs/p1234/profiles/default/modules/site_user_list/site_user_list_roles.module on line 110

frerin’s picture

frerin commented

Once more me:

I "changed" the code to the line:

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

which works fine now. Thanks!