Getting error while joining two tables

Question

Here is query which is, I am using

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

  $results = $query->execute();

Actually i want the user name and id for that those user who are under role id = 5

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'u': SELECT users.name AS name, users.uid AS uid FROM {users} u INNER JOIN {users_roles} u.uidusers_roles.uid WHERE (users_roles.rid = :db_condition_placeholder_0) ; Array ( [:db_condition_placeholder_0] => 5 ) in user_assign_list() (line 80 of C:\xampp\htdocs\nic\sites\all\modules\user_assign\user_assign.module).

This error shown on the browser. Actually i am creating the custom module file for this requirement. Is there any need to include sql connection for those custom module? here is .module file

<?php
function user_assign_permission() {
  return array(
    'submit user_assign' => array(
      'title' => t('Submit user_assign'),
      'description' => t('Submit the user_assign form'),
    ),
    'access user_assign list' => array(
      'title' => t('Access user_assign list'),
      'description' => t('Access the user_assign submissions'),
    ),

  );
}
function user_assign_menu() {
  $items = array();

   $items['users-list'] = array(
    'title' => t('User Assign'),
    'page callback' => 'user_assign_list',
    'access callback' => TRUE,
    'access arguments' => array('access user_assign list'),
    'type' => MENU_CALLBACK,
  );
  return $items;
}


function user_assign_theme($existing, $type, $theme, $path) {
  return array(
    'user_assign_function' => array(
      'variables' => array('rows' => NULL),
    ),
    'user_assign_template' => array(
      'variables' => array('rows' => NULL),
      'template' => 'user_assign_template',
    ),


  );
}



function theme_user_assign_function($variables)
{
    $output = '';

//   print_r($variables);
    foreach($variables['items'] as $row) {

     $output .= '<div class="views-row"><div class="do_featured">
   <div class="do_title">
    <span>    <div class="field"><div class="field-items">'.$row[1].'&nbsp'.$row[2].'</div></div>
    </span> 
   </div></div>';



    }

    return $output;
}

function user_assign_list() {
 // $query = db_select('users', 'n');

//  $query->condition('n.empname', '%' . db_like($_GET['empname']). '%','LIKE');

 // $query->fields('n', array('fe_id', 'empcode','empname','empphoto','empemail','empdesignation'))
  //    ->limit(4)
  //    ->addTag('node_access')
  //    ->groupBy('n.fe_id');
$query = db_select('users','u');
$query->join('users_roles', 'u.uid = users_roles.uid'); // JOIN users_roles with users
$query->fields('users', array('name','uid')) // SELLECT the field name from users
    ->condition('users_roles.rid', 5);


  $results = $query->execute();
     $rows = array();
  foreach($results as $result)
  {
    $rows[] = array(
        $result->uid,
        $result->name,
        );

  }
  $output = theme('user_assign_function', array('items' => $rows));
  return $output;
}

?>

Answer 1

Hi Sumit check the below query and it not showing any error.

Use Query coder module to create any query in drupal format

Query coder Convert SQL query to Drupal Database abstraction layer code.

$query = db_select('users','u');

$query->join('users_roles', 'r', 'u.uid = r.uid'); // JOIN users_roles with users

$query->fields('u', array('name','uid')) // SELECT the field name from users ->condition('r.rid', 5);

$results = $query->execute(); $rows = array();

foreach($results as $result) {

$rows[] = array(
    $result->uid,
    $result->name,
    );

}