title: Wordpress extracting wp_usermeta
body: <p>I'm writing a script that will extract wp_usermeta in a readable layout.</p>
  
  <p>I've got sql like below:</p>
  
  <pre><code>select distinct(user_id), meta_value as suburb from wp_usermeta where meta_key = 'suburb' order by user_id
  </code></pre>
  
  <p>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?</p>
  
  <p><strong>UPDATE 25/SEP/2011, 12:34pm:
  Doesn't matter guys, I found my answer refer to below sql!!!</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>Hope this helps people looking for this solution!</p>