User List Sorting Problem
Using Drupal version 4.7.x (4.7.4 current installed version)
I am not a PHP or SQL programmer, so I need a little help (code modification) to make the following User list sort correctly.
Below is both the PHP Snippet code and table of results for a custom User list of members that includes personal profile information.
The problem is that I only get the following type errors if I mouse click on the any of the "personal profile" field headers to sort the list.
For Example:
Error if "Profession" header is selected:
user warning: Unknown column 'u.profession' in 'order clause' query: SELECT u.uid, u.name, u.status, u.created, u.access FROM users u INNER JOIN users_roles ur ON u.uid=ur.uid WHERE ur.rid = 3 ORDER BY u.profession ASC LIMIT 0, 50 in /home/singlein/public_html/includes/database.mysql.inc on line 121.
Error if "gender" header is selected:
user warning: Unknown column 'u.gender' in 'order clause' query: SELECT u.uid, u.name, u.status, u.created, u.access FROM users u INNER JOIN users_roles ur ON u.uid=ur.uid WHERE ur.rid = 3 ORDER BY u.gender ASC LIMIT 0, 50 in /home/singlein/public_html/includes/database.mysql.inc on line 121.
I think the following line of SQL code needs to be modified to fix the problem, but I don't know how to do it.
$sql = "SELECT u.uid, u.name, u.status, u.created, u.access FROM {users} u INNER JOIN {users_roles} ur ON u.uid=ur.uid WHERE ur.rid = $rid";
Can anyone help?
Thank you in advance,
Sam Raheb
Drupal Theme Generator Developer
The following PHP Snippet code was based on the reference from: http://drupal.org/node/63422
<?php
//choose the role to list by value.
// Note ID 1 = anonymous, ID 2 = authenticated user
// so valid values here are > 2.
$rid = 3;
?>
<?php
$header = array(
array('data' => t('Username'), 'field' => 'u.name', 'sort' => 'asc'),
array('data' => t('Profession'), 'field' => 'u.profession'),
array('data' => t('Membership'), 'field' => 'u.membership'),
array('data' => t('Gender'), 'field' => 'u.gender'),
array('data' => t('Age'), 'field' => 'u.age'),
array('data' => t('Zip code'), 'field' => 'u.zipcode'),
array('data' => t('Status'), 'field' => 'u.status'),
array('data' => t('Member for'), 'field' => 'u.created'),
array('data' => t('Last access'), 'field' => 'u.access')
);
$sql = "SELECT u.uid, u.name, u.status, u.created, u.access FROM {users} u INNER JOIN {users_roles} ur ON u.uid=ur.uid WHERE ur.rid = $rid";
$sql .= tablesort_sql($header);
$result = pager_query($sql, 50);
$status = array(t('blocked'), t('active'));
while ($account = db_fetch_object($result)) {
$account = user_load(array('uid' => $account->uid));
$rows[] = array(theme('username', $account),
$account->profile_profession,
$account->profile_membership,
$account->profile_gender,
$account->profile_age,
$account->profile_zipcode,
$status[$account->status],
format_interval(time() - $account->created),
$account->access ? t('%time ago', array('%time' => format_interval(time() - $account->access))) : t('never'));
}
$output = theme('table', $header, $rows);
$output .= theme('pager', NULL, 50, 0);
print ($output);
?>The above PHP Snippet code yields the following results:
Username Profession Membership Gender Age Zip code Status Member for Last access
---------------------------------------------------------------------------------------------------------------------
admin Business Owner A-Member Male 41 91324 active 1 week 19 hours 4 sec ago
Debbie Executive A-Member Female 38 91377 active 11 hours 19 min never
Jimmy Business Owner B-Member Male 24 91405 active 5 days 23 hours 9 hours 41 min ago
Sammy Business Owner Non-member Male 37 97651 active 4 days 8 hours 32 min 35 sec ago
Is it possible to make this user table display multiple roles?
Besides finding a way to make this table sortable on the "personal profile" fields, Is is also possible to output the data for "multiple roles" or for "all roles"?
The above PHP Snippet outputs the data for only a single role. I would like to have the option to simply modify the code to output the data for either "multiple roles" or for "all users" signed up at the website.
By the way, the "personal profile" fields in the above examples are:
Profession = profile_profession
Membership = profile_membership
Gender = profile_gender
Age = profile_age
Zip code = profile_zipcode
Thanks,
Sam308
Currently having the same
Currently having the same issue.
replaced uid fields with the requiered profilefield by modifieing the theme_username function.
I now get a lost of all users with their realname profilefield. The realname colom is sorted by the uid instead of the profile field.
http://www.activiteitenkalender.be/?q=node/13
sorry the example is in dutch.
There are no stupid questions. Only stupid anwsers
What are you saying?
I don't understand what your are stating here.
Did you fine a way to make all the fields sortable, including making them sortable on the "personal profile" fields?
If so, can you post the code modifications needed?
Also, the link you provided does not show any results in reference to this topic.
Sam Raheb (Sam308)
This is the code that
This is the code that displays a list of all users with a custom profile field. The colum is sortable but not by the displayed profile field but by the default user name that's used for the link etc.
I'd like to sort it by the profile name. The question is how to do this.
<?php
//choose the role to list by value.
// Note ID 1 = anonymous, ID 2 = authenticated user
// so valid values here are > 2.
$rid = 4;
$header = array(
array('data' => t('Vereniging'), 'field' => 'u.name', 'sort' => 'asc'));
$sql = "SELECT u.uid, u.name, u.status, u.created, u.access FROM {users} u INNER JOIN {users_roles} ur ON u.uid=ur.uid WHERE ur.rid = $rid";
$sql .= tablesort_sql($header);
$result = pager_query($sql, 50);
while ($account = db_fetch_object($result)) {
$account = user_load(array('uid' => $account->uid));
$account->name .= ' (' . $account->profile_v_naam . ')';
$rows[] = array(theme('username', $account));
}
$output = '<div id="databank">';
$output .= theme('table', $header, $rows, $attributes = array('width' => 'auto;', 'align' => 'center'));
$output .= theme('pager', NULL, 50, 0);
$output .= '</div>';
print ($output);
?>
There are no stupid questions. Only stupid anwsers.
Sort by profile fields
My first post on drupal! After learning a lot, I hope the following is useful. Below you can find a code snipped that creates a table of users that can be sorted by the profile fields. I have put some comments in the code to explain.
<?php
// Choose the role to list by value.
// Note ID 1 = anonymous, ID 2 = authenticated user, ID 3 = some_role
// so valid values here are > 2.
$rid = 3;
$header = array(
array('data' => t('Username'), 'field' => 'u.name', 'sort' => 'asc'),
//For the 'field' value, I do not point to table columns directly, but to
//aliases that are defined inside the SQL query
//pv stand for the profile_values table
array('data' => t('First Name'), 'field' => 'pv_firstname'),
array('data' => t('Last Name'), 'field' => 'pv_lastname'),
array('data' => t('Function'), 'field' => 'pv_function'),
array('data' => t('Started'), 'field' => 'pv_started')
);
/**
* The problem with sorting on profile fields is that all the values for different fields are all stored in one single tabel (profile_values). Therefore, I've joined the same table as many times as I need a different profile field from it (in this example, this is 'profile_firstname, profile_lastname, profile_function and profile_started'. However, everytime, the tables are given a different alias (pv1, pv2, pv3 and pv4). Like this, different columns can be created from the same profile_values.value column in the drupal database. Then, the tables are joined together, based on the user id. After that, they are filtered using the WERE statement. This filter will select a different profile field for every column based on the field id value that can be found in profile_fields table. In this case:
pv1.fid = 4 : the first name
pv2.fid = 5 : the last name
pv3.fid = 6 : the function
pv4.fid = 8 : the start date
*/
$sql = "SELECT pv1.value AS pv_firstname,
pv2.value AS pv_lastname,
pv3.value AS pv_function,
pv4.value AS pv_started,
u.uid
FROM {users} u
LEFT JOIN (
{profile_values} pv1
CROSS JOIN
{profile_values} pv2
CROSS JOIN
{profile_values} pv3
CROSS JOIN
{profile_values} pv4
CROSS JOIN
{users_roles} ur)
ON pv1.uid = u.uid AND
pv2.uid = u.uid AND
pv3.uid = u.uid AND
pv4.uid = u.uid AND
u.uid = ur.uid
WHERE pv1.fid = 4 AND
pv2.fid = 5 AND
pv3.fid = 6 AND
pv4.fid = 8 AND
ur.rid = $rid";
$sql .= tablesort_sql($header);
$result = pager_query($sql, 50);
$status = array(t('blocked'), t('active'));
while ($account = db_fetch_object($result)) {
//If you don't need a link to the user profile, than don't call the following function, it's not needed
$account_user = user_load(array('uid' => $account->uid));
//The date info is a string representation of php variables
$start_date = unserialize($account->pv_started);
$rows[] = array(
theme('username', $account_user),
$account->pv_firstname,
$account->pv_lastname,
$account->pv_function,
$start_date['year'] . '/' . $start_date['month']
);
}
$output = theme('table', $header, $rows);
print($output);
?>
Thanks, I will give it a try
canardo:
Thanks, I will give it a try.
Sam Raheb (Sam308)
The Good and Bad News
canardo:
I tried the script and it works but... there is one MAJOR problem that arises from using it.
I placed the code in a node page set to php and after I refreshed the page a few times the website went down. No access to the site is available. After waiting 10 minutes, I tried to access the site and got the following Drupal error response:
"The MySQL error was: User vectorsz_drpl1 has already more than 'max_user_connections' active connections."
It seems that the script is making multiple connections to the MySQL Database which causes the problem.
Currently I am hosting my website with site5.com and they said that the maximum per user connection limit for MySQL is 15. So I guess that the script is exceeding the 15 connections per user.
Is it possible to modify your script shown in the above posting titled "Sort by profile fields" such that:
(1) It solves the above problem?
(2) Make it function for "ALL Roles"?
or
If you can find a way to modify the following PHP Snippet such that the "personal profile" fields are sortable?
Members User List - Works Great
The following PHP Snippet generates a Users List for "ALL Roles" and includes multiple personal profile data. This script outputs both the user's personal information profile fields and their photos.
This Members User list displays a table of "all users" from "all roles" including their profile images which are links.
Where the following are Personal Profile Fields:
------------------------------------------------
Membership = profile_membership
Zip code = profile_zipcode
Age = profile_age
Gender = profile_gender
Profession = profile_profession
Note:
Remember to replace the default image and path below (../assets/images/default-user-sm.gif) with your default image and path.
<?php
// Displays a list of all users including their profile images (all roles)
$header = array(
array('data' => t('Username'), 'field' => 'u.name', 'sort' => 'asc'),
array('data' => t('Photo'), 'field' => 'u.photo'),
array('data' => t('Membership'), 'field' => 'u.membership'),
array('data' => t('Zip code'), 'field' => 'u.zipcode'),
array('data' => t('Age'), 'field' => 'u.age'),
array('data' => t('Gender'), 'field' => 'u.gender'),
array('data' => t('Profession'), 'field' => 'u.profession'),
array('data' => t('Last access'), 'field' => 'u.access')
);
$sql = 'SELECT u.uid, u.name, u.status, u.created, u.access FROM {users} u WHERE uid != 0';
$sql .= tablesort_sql($header);
$result = pager_query($sql, 50);
$status = array(t('blocked'), t('active'));
while ($account = db_fetch_object($result)) {
$account = user_load(array('uid' => $account->uid));
if($account->picture){$account->picture = '<a href="?q=user/'.$account->uid.'"><img src="'.$account->picture.'" height="25" width="25" border="1" alt=""></a>';}
else{$account->picture = '<a href="?q=user/'.$account->uid.'"><img src="../assets/images/default-user-sm.gif" height="25" width="25" border="1" alt=""></a>';}
$rows[] = array(theme('username', $account),
$account->picture,
$account->profile_membership,
$account->profile_zipcode,
$account->profile_age,
$account->profile_gender,
$account->profile_profession,
$account->access ? t('%time ago', array('%time' => format_interval(time() - $account->access))) : t('never'));
}
$output = theme('table', $header, $rows);
$output .= theme('pager', NULL, 50, 0);
print ($output);
?>
The above PHP Snippet code yields the following results:
Note: Only the fields: Ussername and Last acces are sortable.
See an actual screen shot at: http://xlecom.com/assets/images/example-member-list.gif
Username Photo Membership Zip code Age Gender Profession Last access-----------------------------------------------------------------------------------------------------
admin (photo) Member 95421 47 Male Business Owner 6 min 51 sec ago
bachel (photo) Member 95488 46 Male Warehouse Supervisor 12 hours 21 min ago
cristob (photo) Member 90095 43 Male Career Counselor 5 days 9 hours ago
Debbie (photo) Member 95481 42 Female Executive never
dicemat (photo) Member 95450 40s Female Investigations 2 weeks 2 days ago
fourmu (photo) Non-Member 95421 44 Female Self Employed 1 week 2 days ago
Jimmy (photo) Member 95450 28 Male Business Owner 1 day 14 hours ago
michael (photo) Non-Member 95481 53 Male Public Affairs 1 day 14 hours ago
Sammy (photo) Member 95421 47 Male Business Owner 8 hours 40 min ago
soulhea (photo) Member 95420 42 Female Business owner never
TestUs (photo) Member 95421 33 Male Self Employeed 11 hours 5 min ago
Sam Raheb (Sam308)
for ALL roles
hey Sam,
if you want the code to be working for all roles, you have to change the SQL query. Just leave out the line
ur.rid = $rid. This line means that the result is filtered so that the user role id always equals $rid. In this case, $rid has been defined as 3. So for your case, just modify the last lines to:"WHERE pv1.fid = 4 ANDpv2.fid = 5 AND
pv3.fid = 6 AND
pv4.fid = 8 "
However, for the MYSQL error, I don't have an answer right away. To me, it seems there are the same number of connections for this query as before. The query will take more time to calculate, but nothing more. I'll try to see what's wrong.
max_user_connections
for your first problem, I think it is more a hosting problem than a coding error. It seems that several people had already this issue:
http://drupal.org/node/34858
http://drupal.org/node/19293
To be sure it is not a scripting problem you can explicitly close the mysql connection at the end of the script by adding the line
mysql_close();at the end of the php script.hope this works
Still having problems with your the script
Canardo:
First of all, thanks for all the time you have spent on this topic. I really appreciate it.
I used your suggestions about removing the
ur.rid = $ridstatement to make your script work for all roles and added themysql_close();statement to the last line of the script.The problem I am having is the same problem as before as mentioned in the previous post stated above: The Good and Bad News.
I placed the code in a node page and set the input format to PHP. After I refreshed the page two times the website went down displaying the error below and then totally died. No access to the site was available at all. I had to have my web hosting company via tech support kill all MySQL processes and restart MySQL in order to regain access to the site.
"The MySQL error was: User vectorsz_drpl1 has already more than 'max_user_connections' active connections."
It seems that the script is making multiple connections to the MySQL Database which causes the problem.
I have 33 personal profile fields and 6 core user fields as stated below. This may be too many for the script to handle any may be causing this behavior.
Fields:
Username
User
Photo
Email
Personal Information 1
Personal Information 2
Personal Information 3
Personal Information 4
Personal Information 5
Personal Information 6
Personal Information 7
Personal Information 8
Personal Information 9
Personal Information 10
Personal Information 11
Personal Information 12
Personal Information 13
Personal Information 14
Personal Information 15
Personal Information 16
Personal Information 17
Personal Information 18
Personal Information 19
Personal Information 20
Personal Information 21
Personal Information 22
Personal Information 23
Personal Information 24
Personal Information 25
Personal Information 26
Personal Information 27
Personal Information 28
Personal Information 29
Personal Information 30
Personal Information 31
Personal Information 32
Personal Information 33
Status
Member for
Last access
I do not know SQL or PHP to do this, but is it possible to modify the SQL statement in the code posted above in: The Good and Bad News under the heading Members User List - Works Great such that all the fields are sortable?
The SQL statement from the PHP Snippet is:
$sql = 'SELECT u.uid, u.name, u.status, u.created, u.access FROM {users} u WHERE uid != 0';This script functions well for all roles and has a fast response. It also works well in retrieving the user's photo as a thumbnail. This is why I perfer to use this format of the script. I have tested it with the 40 fields shown above and have experienced no problems.
See an actual "trimmed" screen shot here: Member's List
I think this would be an extremely valuable PHP Snippet for Site Administrators for which the Drupal community would benefit.
I also incorporated the script into a utility for the Drupal community called: Drupal Members List PHP Script Generator" but the personal profile fields are not sortable.
The only item addition to make the Member's List script complete would be to be able to retrieve the user's role ID.
Translating the role ID from a number to the actual Role's title would be great if possible.
If you can come up with a solution, I will release it to the Drupal community in an updated version of the "Drupal Members List PHP Script Generator" and post the script as a PHP snippet on this handbook page: Custom User Blocks and User Tables PHP Snippets.
If you cannot or do not have the time, I understand. I was just hoping for a solution.
Thank you for all your effort and time you have spent on this endeavor. It is much appreciated.
Sam Raheb (Sam308)
Just what I need
Thanks, Canardo and Sam. This is the first half of something I really need.
Which version of these snippets should I use? I want to select a single role to retrieve; I do not want user sorting; I do need to pick up profile fields.
I know I probably need to go post in the forum on this part, but I'll ask any way. Can I have a profile field that is visible only to those users who have this role?
Nancy W.
proudly running 3½ sites on Drupal so far
RE: Just what I need
See the following page for an assortment of scripts I posted: Custom User Blocks and User Tables PHP Snippets. One of them should suffice for your application.
If you do not want the fields sortable, then make the changes to the script as follows:
Sortable by Username and Last Access:
$header = array(array('data' => t('Username'), 'field' => 'u.name', 'sort' => 'asc'),
array('data' => t('Photo')),
array('data' => t('Membership')),
array('data' => t('Zip code')),
array('data' => t('Age')),
array('data' => t('Gender')),
array('data' => t('Profession')),
array('data' => t('Last access'), 'field' => 'u.access')
);
Non-sortable at all:
$header = array(array('data' => t('Username')),
array('data' => t('Photo')),
array('data' => t('Membership')),
array('data' => t('Zip code')),
array('data' => t('Age')),
array('data' => t('Gender')),
array('data' => t('Profession')),
array('data' => t('Last access'))
);
Sam Raheb (Sam308)
anybody has a a snipet like that for 5.x?
anybody has a a snipet like that for 5.x?
I am trying to build user lists for my company's intranet and would love something like that!
thanks!!!
Otavio
I used some of this on 5.1
I used some of this on 5.1 and it worked just as it did on 4.7.4. As a matter of fact, I am still using one of the snippets on both versions, completely unchanged. Check Sam's next posting that he points to. I believe my completed snippet is on my web site http://nanwich.info.
Nancy W.
now running 5 sites on Drupal so far
Drupal Cookbook (for New Drupallers)
Adding Hidden Site Design Notes
Is it possible to combine fields into one column?
For instance, I have about 5 checkboxs that are all different committees (e.g. charity, fundraising, treasurer, etc) and i want to make it so all that data is aggregated. So if they check 3 , ie: charity, fundraising and treasurer, under committees, all 3 of those would show up. Is that possible?
Thanks for your help.
Yes
It would be a simple change to the WHERE statement, or you could select every body and use an IF statement to select which ones you want.
Nancy W.
now running 5 sites on Drupal so far
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in Your Database
Try Members module
Hi,
Try Members modul. It's a very clever modul to create and sort user list (cont. profile fields as well).
-Gabor-
I have
Been there, not adequate for me.
Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database