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].' '.$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;
}
?>