StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWordpress extracting wp_usermeta
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload