Drupal database: how to join 2 tables? Currently I'm learning drupal database command as I'm pretty new to Drupal, I'm more familiar with mysqli api. I want to check the role of user before they are logged in to the system, the role table maps rid with a role (for example 1 - admin, 2 - author, etc.), the users table and users_roles table are linked by uid, which is unique. The role i want to verify before user login to the system has rid (role id) = 2. This is what I have so far, but it doesn't really work:

function is_author($name) {
$query = db_select('users_roles');
$query-&gt;join('users', 'users_roles.uid = users.uid'); // JOIN users_roles with users
$query-&gt;fields('users_roles', array('rid')) // SELLECT the field rid from users_roles
    -&gt;fields('users', array('name')) // SELLECT the field name from users
    -&gt;condition('users.name', ':name');

$result = $query-&gt;execute();

while ($record = $result-&gt;fetchAssoc()) {
    if ($record == 2) {
        break;          
    }else{
        return false;
    }
}   
return true;


}

This is the users and users_roles table:

table users: 

uid | name | pass | .....

1 | Michael | (encrypted password)

2 | Ted | (encrypted password)

3 | Mark | (encrypted password)


Table users_roles:

uid | rid

1 | 2

2 | 3


Please help!! Thanks in advanced!
To check user role before login you need to create a validate function for login. You can put this code in template.php of your theme or in custom module file. e.g my custom module name is MYMODULE. 

&lt;?php

/**
* Implements hook_form_alter().
*/
function MYMODULE_form_alter(&amp;$form, &amp;$form_state, $form_id) {
if ($form_id == 'user_login') {
$form['#validate'][] = 'MYMODULE_user_login_validate';
}
}

/**
* Additional handler for user_login form validate.
*/
function MYMODULE_user_login_validate($form, &amp;$form_state) {
  // check user role here by the function mentioned abve
   $name = $form_state['values']['name'];
   $user = user_load_by_name($name);
   $role = array_key_exists(2, $user-&gt;roles);
   // do something
}


Hope it will help you.