Hello guys & girls
I'm having some issues here to create one join that I think it's not possible to do.
I've 2 tables: one with user information and the other with user registration.
So im trying to join them
If I do this:
SELECT u.ID, f.id as field_id, f.name as field_name, d.value as field_data, u.user_login, u.user_nicename, u.user_email, u.display_name, u.user_status, u.user_registered
FROM wp_users u
INNER JOIN wp_bp_xprofile_data d ON d.user_id = u.ID
INNER JOIN wp_bp_xprofile_fields f ON f.id = d.field_id
WHERE d.value != '' AND u.ID IS NOT NULL
ORDER BY u.ID, f.id
I get this result:
ID field_id field_name field_data user_login user_nicename user_email display_name user_status user_registered
1 1 Name Thiago Cruz admin admin
thiago@test.com Thiago Cruz 0 2010-04-06 15:47:31
1 2 Industry Aerospace admin admin
thiago@test.com Thiago Cruz 0 2010-04-06 15:47:31
As you guys can see I will have repeated data as email, id, name, status, registered date.
But the data that changes for each line is:
f.id as field_id, f.name as field_name, d.value as field_data
So because I didn't know how to do this query properly I just created an foreach and created lines in one array to get my real info for each user using PHP to do that.
I wanna know if its possible to create columns with the values from one table, or something like that.
Example:
instead of Name Thiago Cruz:
I would like to have:
Name as column header
Thiago Cruz as value of that column
So what I basecally want is that all my columns looks like this:
id user_id name industry jobtitle company company_size city province country email
Instead of what I have now.
Anyone knows if that is possible???
I have for each user like 10 fields that I would like to transform into columns even if that column is empty/null
Thank you very much in advance,
Thiago Cruz