0

I am trying to grab a specific users profile info + custom profile fields WHERE uid = X, I have used the views module currently and copied that sql however that creates a massive query. I was wondering if there is a better way to query Drupal 6 other than views to grab the UID, name, email, + my custom fields?

SELECT profile_values_firstname.value AS profile_values_firstname_value, profile_values_firstname.uid AS profile_values_firstname_uid, profile_values_surname.value AS profile_values_surname_value, profile_values_surname.uid AS profile_values_surname_uid, profile_values_telnumber.value AS profile_values_telnumber_value, profile_values_telnumber.uid AS profile_values_telnumber_uid, users.mail AS users_mail, users.uid AS users_uid, users.access AS users_access, users.name AS users_name FROM node node INNER JOIN users users ON node.uid = users.uid LEFT JOIN profile_values profile_values_firstname ON users.uid = profile_values_firstname.uid AND profile_values_firstname.fid = '1' LEFT JOIN profile_values profile_values_surname ON users.uid = profile_values_surname.uid AND profile_values_surname.fid = '2' LEFT JOIN profile_values profile_values_telnumber ON users.uid = profile_values_telnumber.uid AND profile_values_telnumber.fid = '3'

1

There are a few ways your query can be improved. First, there were some multiple queries for the user's id which I removed. Second, your query was selecting from the node table and then inner joining with the users table which can cause some undesired results (e.g. only users who have created content on your site will show up) and it is also less performant.

Here's the improved query. Since all profile values are stored within the same table using a 'fid' field id to differentiate them one has to do an inner join for each value that you want to select. Hence the query has to be a little longer than one would hope.

SELECT profile_values_firstname.value AS profile_values_firstname_value, profile_values_surname.value AS profile_values_surname_value, profile_values_telnumber.value AS profile_values_telnumber_value, users.mail AS users_mail, users.uid AS users_uid, users.access AS users_access, users.name AS users_name FROM users users LEFT JOIN profile_values profile_values_firstname ON users.uid = profile_values_firstname.uid AND profile_values_firstname.fid = '1' LEFT JOIN profile_values profile_values_surname ON users.uid = profile_values_surname.uid AND profile_values_surname.fid = '2' LEFT JOIN profile_values profile_values_telnumber ON users.uid = profile_values_telnumber.uid AND profile_values_telnumber.fid = '3'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.