0

I'm looking to query a bunch of user and usermeta data into a single table. I'm able to do so by creating multiple temporary tables and a pivot:

    create temporary table meta

    select u.ID, u.user_login, meta_key, meta_value from wp_users u
    inner join wp_usermeta m on m.user_id = u.ID;


    create temporary table users

    select ID, user_login,
        MAX(CASE WHEN meta_key = 'first_name' THEN meta_value END) as first_name,
        MAX(CASE WHEN meta_key = 'middle_name' THEN meta_value END) as middle_name,
        MAX(CASE WHEN meta_key = 'last_name' THEN meta_value END) as last_name,
        MAX(CASE WHEN meta_key = 'university_program' THEN meta_value END) as university_program, 
        MAX(CASE WHEN meta_key = 'wp_capabilities' THEN meta_value END) as wp_capabilities 
    from meta
    group by ID, user_login;

    drop temporary table meta; 

    select * from users 
    where wp_capabilities like '%um_student%';

    drop temporary table users;  

However, is there a better and shorter way to run this query without the need to create temporary tables or the pivot?

0

I was just trying out the wp_get_users() function and I think that's a much cleaner way to get the user data in a table. I can simply do something like this:

$users = get_users('role=um_student');

        foreach ( $users as $user )
        {
            echo "<tr>";
                echo "<td>$user->ID</td>";
                echo "<td>$user->user_login</td>";
                echo "<td>$user->first_name</td>";
                echo "<td>$user->middle_name</td>";
                echo "<td>$user->last_name</td>";
                echo "<td>$user->university_program</td>";
                echo "</tr>";
        }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.