PHP/MySQL: How to query row data information as column data?

Question

I'm trying out the "PHP Login & User Management" plugin, which allows custom fields in a user's profile.
However, for some reason, this is implemented on a seperate table, so in order to pull up "Phone Number" for example, I have to get my user_id from login_users, go into the login_profile_fields table, find the correct row, pull id and label, and find the row in login_profiles where id=pfield_id and user_id = user_id.

I'm trying to write an SQL query to display the following information in the end:

WHERE a chosen value in login_profiles is X (example: "List the above info for all users who have a Status code of 1")

Is there a way for me to do this?

Alternatively, is there a way I can automatically populate the login_profiles table with values from login_profiles, like so?

login_profiles

p_id | pfield_id | user_id | profile_value | Phone Number | Zip Code | ...
1    | 1         | 1       | 18005551212   | {Select profile_value from login_profiles where user_id=user_id and pfield_id=1} | {Select profile_value from login_profiles where user_id=user_id and pfield_id=2} | ...

Here are my current tables:

login_profile_fields

id | section   |   type     |   label       |
1  | User Info | text_input | Phone Number  |
2  | User Info | text_input | Zip Code      |
3  | Work Info | text_input | Department    |
4  | Work Info | text_input | Skills        |
5  | Work Info | text_input | Manager Email |
6  | Work Info | text_input | Company Code  |
7  | Work Info | checkbox   | Status        |

login_profiles

p_id | pfield_id | user_id | profile_value |
1    | 1         | 1 | 18005551212 | 
2    | 2         | 1 | 90210 | 
3    | 3         | 1 | Marketing |
4    | 4         | 1 | Segmentations and surveys |
5    | 5         | 1 | [email protected] |
6    | 6         | 1 | COMP1 |
7    | 7         | 1 | 1 |
1    | 1         | 2 | 18007771234 | 
2    | 2         | 2 | 90218 | 
3    | 3         | 2 | CEO |
4    | 4         | 2 | Business strategy |
5    | 5         | 2 | [email protected] |
6    | 6         | 2 | COMP1 |
7    | 7         | 2 | 1 |

login_users

user_id| name          | email |
     1 | Michael Bluth | [email protected] |
     2 | George Bluth  | [email protected] |

I'm not a MYSQL person by training, but I'm learning all I can, so any advice is very much appreciated!

You don't want to do this. Do a normal query, then do the column transformation in your code. It takes just ONE line of code in the client to do so while fetching the data, v.s. a HIDEOUS amount of sql to do so dynamically within the query. — Marc B
– Marc B, Commented Jun 24, 2013 at 15:54
@MarcB - I disagree strongly - Pivot tables are a very common need, and do not require a hideous amount of SQL, as proven by bluefeet's answer below. — Steven Moseley
– Steven Moseley, Commented Jun 24, 2013 at 15:56
yes, if mysql supported pivot queries, that'd be fine. but it doesn't, so there ya go. — Marc B
– Marc B, Commented Jun 24, 2013 at 15:57
@MarcB - All ANSI-compliant DBs support pivoting, via aggregation. — Steven Moseley
– Steven Moseley, Commented Jun 24, 2013 at 15:58

Taryn · Accepted Answer · 2013-06-24 15:53:59Z

You are joining to want to JOIN the tables on the foreign keys relationships and then you can use an aggregate function with a CASE expression to convert the rows into columns:

select u.name,
  u.email,
  max(case when f.label = 'Phone Number' then p.profile_value end) PhoneNumber,
  max(case when f.label = 'Zip Code' then p.profile_value end) ZipCode,
  max(case when f.label = 'Department' then p.profile_value end) Department,
  max(case when f.label = 'Skills' then p.profile_value end) Skills,
  max(case when f.label = 'Manager Email' then p.profile_value end) ManagerEmail,
  max(case when f.label = 'Company Code' then p.profile_value end) CompanyCode,
  max(case when f.label = 'Company Code' then p.profile_value end) Status
from login_profile_fields f
left join login_profile p
  on f.id = p.pfield_id
left join login_users u
  on p.user_id = u.user_id
group by u.name, u.email;

See SQL Fiddle with Demo

If you have an unknown number of values, then you might need to use a prepared statement to generate dynamic SQL to execute:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN f.label = ''',
      label,
      ''' THEN p.profile_value END) AS `',
      label, '`'
    )
  ) INTO @sql
FROM login_profile_fields;

SET @sql 
  = CONCAT('SELECT u.name,
              u.email, ', @sql, ' 
            from login_profile_fields f
            left join login_profile p
              on f.id = p.pfield_id
            left join login_users u
              on p.user_id = u.user_id
            group by u.name, u.email');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

Collectives™ on Stack Overflow

PHP/MySQL: How to query row data information as column data?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related