Select query for Users/Profiles on single rows

Posted by thermalmusic on March 14, 2012 at 2:26am

Hi,

I'm trying to write an SQL query on a D6 database to return users and their profile values. My problem is getting profile values to return as separate fields (columns) in the resulting rows. Profile data is only saved in the values field in the profile_values table, as children, identified by fid.

I've tried Group_Concat but it is not a solution. Profile csv module is not sufficient either. I'd like to have an output like this:

If anyone has done this as a query, please let me know.

Thanks,
Thermal

Comments

Content Profile module

Posted by HJulien on March 14, 2012 at 12:22pm

Content profile module let's me add all of the profile fields I want and they are in the database as separate fields so you have full control over their appearance, etc.

Content Profile creates them as nodes so views, display suite, rules, etc can all be used. One thing I learned is to not use the Title and Body fields for names. One thing I noticed is that you also have address fields and for that I found that location field is good for that.

This is one of the reasons

Posted by dalin on March 14, 2012 at 1:13pm

This is one of the reasons that no one uses the old D6 Profile module. Basically what you need to do is add multiple left joins to the profile_values table, one for each field that you want to pull. Something like this (though I'm just making this up without knowing the exact field names)

SELECT u.uid, u.name, u.mail, field_firstname.value as firstname, field_lastname.value as lastname
FROM user u
LEFT JOIN profile_values as field_firstname ON u.uid = field_firstname.uid AND field_firstname.field_name = 'firstname'
LEFT JOIN profile_values as field_lastname ON u.uid = field_lastname.uid AND field_lastname.field_name = 'lastname'

Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his

multiple left joins

Posted by thermalmusic on March 15, 2012 at 2:25am

Dave,
Thanks for the example. Unfortunately the D6 profile module is what I have to work with on this site, blah. I've tried the multiple joins on that table and it works to a point. The query involves more tables, columns, and joins than those as well so I'm trying to work it in. There are about 4k users in the database with 7 profile values each so I'm wondering if this query uses some overhead when run.

Steven

views is your friend

Posted by adixon on March 15, 2012 at 12:43pm

I believe the core profile module is supported by views, so I'd recommend using it to generate the sql, or just use it directly to extract what you need. It can generate csv files (or even excel files) directly, and the sql that it generates is usually as efficient as anything you'd do by hand.

Does it have to be using SQL?

Posted by cjboranp on March 14, 2012 at 2:03pm

Does it have to be SQL?

Might be easiest if you did a user_load(uid), and then wrote a row to a flat file or into an html table.

$user_ids=array(1,2,3,4,5,6,7,8,9);
$out="<table><tr>
<td>User</td>
<td>Firstname</td>
<td>Lastname</td>
<td>Address</td>
<td>City</td>
</tr>";

foreach($user_id as $uid){
  $tmp_user=user_load($uid);
  $out.="<tr><td>" . $tmp_user->uid ."</td></tr>";
// add the rest of the rows accordingly. or dsm them to see their exact names. They're usually prefixed with "_profile"
}
$out.="</table>";

I'd think this to be good because you can write this into a module and it can be modified and such.

However, in D6 user_load() is

Posted by dalin on March 14, 2012 at 2:22pm

However, in D6 user_load() is not cached, so this method will incur a performance cost. On a small site with a short list of users it's probably not very noticeable though.

Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his

SQL ICE for Drupal Users

Posted by lfolta on March 14, 2012 at 2:24pm

(This tool is free to download and use.)

Business Integration Technology has a tool called SQL ICE, the Internet Computing Environment for SQL Experts. Although SQL ICE can do a lot of things, one that may be of interest to Drupal Users is the ability to connect to your Drupal database and write MySQL queries to get reports that can be displayed, downloaded, and shared with other users.

I hosted a webinar that demonstrated how you can use SQL ICE to access your Drupal data. You can view the video and download the queries I used here:
http://businessintegrationtechnology.com/SQLICE_Videos.html.

One of the examples I showed in the webinar is how to access your Drupal Users data, and the output can be downloaded as a CSV from the SQL ICE tool.

For more information, visit our web site:
http://businessintegrationtechnology.com/SQLICE.html

This is the link to the download page:
http://businessintegrationtechnology.com/Download_SQLICE.html

Try it out and then feel free to contact me if you have any questions.

Lori Folta
314-222-0610 direct line
lfolta@businessintegrationtechnology.com

SQL ICE

Posted by thermalmusic on March 15, 2012 at 2:28am

Lori,
This looks very interesting, I will have a look and see if its something I can use.
Thanks,
Steven