skip to Main Content

SQL Noob here. Pulling some data from a custom table in a WP install and I want to get the user information from both user_meta and users table.

The resulting array should look like this.

array(
 user_id => 1234,
 user_email => [email protected]
 first_name => foo
 last_name => bar
)

I just cant get my CASE statement right.

$query = $wpdb->prepare(
    "SELECT s.user_id, u.user_email         
    FROM wp_user_stages s 
    INNER JOIN wp_users u ON s.user_id=u.ID 
    INNER JOIN wp_usermeta m ON s.user_id=m.user_id
    CASE 
        WHEN m.met_key = 'first_name' THEN m.meta_value AS first_name
        WHEN m.met_key = 'last_name' THEN m.meta_value AS last_name
    END
    WHERE 
        s.quest_comp = 0 AND
        s.registered = %s           
    ",
    '2024-05-11'    
    );
$users = $wpdb->get_results($query, ARRAY_A);

I’ve looked at various manuals and tried the case statement in different formats and different places. I’ve used it before but so long ago I just cant remember.

2

Answers


  1. Change your sql script as:

    SELECT s.user_id, u.user_email,
        CASE 
            WHEN m.met_key = 'first_name' THEN m.meta_value
            ELSE ''
        END  AS first_name,
        CASE 
            WHEN m.met_key = 'last_name' THEN m.meta_value
            ELSE ''
        END  AS last_name      
        FROM wp_user_stages s 
        INNER JOIN wp_users u ON s.user_id=u.ID 
        INNER JOIN wp_usermeta m ON s.user_id=m.user_id
        WHERE 
            s.quest_comp = 0 AND
            s.registered = %s 
    
    Login or Signup to reply.
  2. You need to aggregate by user and then use pivoting logic to extract the key values:

    SELECT
        s.user_id,
        u.user_email,
        MAX(CASE WHEN m.met_key = 'first_name' THEN m.meta_value END) AS first_name,
        MAX(CASE WHEN m.met_key = 'last_name' THEN m.meta_value END) AS last_name       
    FROM wp_user_stages s 
    INNER JOIN wp_users u ON s.user_id = u.ID 
    INNER JOIN wp_usermeta m ON s.user_id = m.user_id
    WHERE 
        s.quest_comp = 0 AND
        s.registered = %s
    GROUP BY
        s.user_id,
        u.user_email       
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search