Wordpress extracting wp_usermeta

I'm writing a script that will extract wp_usermeta in a readable layout.

I've got sql like below:

select distinct(user_id), meta_value as suburb from wp_usermeta where meta_key = 'suburb' order by user_id

Now is there a way we can include all other fields aswell using sql query to join the column on the right and make the result coming out in one table so I can use this for csv export?

UPDATE 25/SEP/2011, 12:34pm: Doesn't ma开发者_运维技巧tter guys, I found my answer refer to below sql!!!

SELECT
    u.id, u.user_login,
    MIN(CASE m.meta_key WHEN 'title' THEN m.meta_value END) AS title,
    MIN(CASE m.meta_key WHEN 'first_name' THEN m.meta_value END) AS first_name,
    MIN(CASE m.meta_key WHEN 'last_name' THEN m.meta_value END) AS last_name,
    MIN(CASE m.meta_key WHEN 'suburb' THEN m.meta_value END) AS phone,
    MIN(CASE m.meta_key WHEN 'state' THEN m.meta_value END) AS state,
    MIN(CASE m.meta_key WHEN 'country' THEN m.meta_value END) AS country,
    MIN(CASE m.meta_key WHEN 'postcode' THEN m.meta_value END) AS postcode,
    MIN(CASE m.meta_key WHEN 'contact_no' THEN m.meta_value END) AS contact_no,
    MIN(CASE m.meta_key WHEN 'email' THEN m.meta_value END) AS email,
    MIN(CASE m.meta_key WHEN 'occupation' THEN m.meta_value END) AS occupation,
    MIN(CASE m.meta_key WHEN 'workplace' THEN m.meta_value END) AS workplace,
    MIN(CASE m.meta_key WHEN 'maternitybg' THEN m.meta_value END) AS maternitybg,
    MIN(CASE m.meta_key WHEN 'trainingdate' THEN m.meta_value END) AS trainingdate,
    MIN(CASE m.meta_key WHEN 'traininglocation' THEN m.meta_value END) AS traininglocation,
    MIN(CASE m.meta_key WHEN 'coltraining' THEN m.meta_value END) AS coltraining,
    MIN(CASE m.meta_key WHEN 'trainingyear' THEN m.meta_value END) AS trainingyear,
    MIN(CASE m.meta_key WHEN 'coltraining' THEN m.meta_value END) AS coltraining,
    MIN(CASE m.meta_key WHEN 'isinstructor' THEN m.meta_value END) AS isinstructor,
    MIN(CASE m.meta_key WHEN 'gender' THEN m.meta_value END) AS gender,
    MIN(CASE m.meta_key WHEN 'idf_indig_tsi' THEN m.meta_value END) AS idf_indig_tsi,
    MIN(CASE m.meta_key WHEN 'idf_ct_ld' THEN m.meta_value END) AS idf_ct_ld,
    MIN(CASE m.meta_key WHEN 'comments' THEN m.meta_value END) AS comments

    FROM wp_users u
      LEFT JOIN wp_usermeta m ON u.ID = m.user_id
        AND m.meta_key IN ('title', 'first_name', 'last_name', 'suburb', 'state', 'country', 'postcode', 'contact_no', 'email', 'occupation', 'workplace', 'maternitybg', 'trainingdate', 'traininglocation', 'coltraining', 'isinstructor', 'gender', 'idf_indig_tsi', 'idf_ct_ld', 'comments')
    GROUP BY u.ID

Hope this helps people looking for this solution!

You really shouldn't use SQL directly within WordPress as they offer dozens of functions to do the work for you. Doing so will also keep your code flexible for future upgrades of WordPress.

In your case, you are interested in get_user_meta()