Drupal database: how to join 2 tables?

Question

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->join('users', 'users_roles.uid = users.uid'); // JOIN users_roles with users
$query->fields('users_roles', array('rid')) // SELLECT the field rid from users_roles
    ->fields('users', array('name')) // SELLECT the field name from users
    ->condition('users.name', ':name');

$result = $query->execute();

while ($record = $result->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!

Answer 1

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.

<?php

/**
* Implements hook_form_alter().
*/
function MYMODULE_form_alter(&$form, &$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, &$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->roles);
   // do something
}

Hope it will help you.

Answer 2

If you are trying to filter based on a rid, you should make it a condition.

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

That would only return you the users who have role 2 and have the name of $name.

However, you have the user's name already. If you are trying to determine whether or not the specified user has the specified role, use Drupal to help you with that. I understand you want to learn better how to do queries, but it looks like what you really want is:

function is_author($name) {
  $user = user_load_by_name($name);
  return array_key_exists(2, $user->roles);
}