Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress – Can not get my SQL Select statement to work with CASE

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.

Tags:

2

Answers


  1. 0

    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. 0

    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