CODEWITHSUNDEEP
wordpress
gdfgdfg
gdfgdfg

Reputation: 3566

Join users and usermeta tables - WordPress

I want to select columns from users and usermeta tables.

This is my query:

$sql = $wpdb->prepare(
    "SELECT {$wpdb->users}.ID FROM {$wpdb->users} 
    WHERE {$wpdb->users}.user_registered <= '%s' 
    AND {$wpdb->usermeta}.meta_key='custom_status' 
    AND {$wpdb->usermeta}.meta_value=0
    INNER JOIN {$wpdb->usermeta} ON {$wpdb->users}.ID= {$wpdb->usermeta}.user_id", $before);

, but it doesn't select nothing. I know that I have one user with this meta_value and user_registered less than the current date time ($before).

This is what $before is like: 2019-07-06 19:03:55

Upvotes: 0

Views: 1518

Answers (1)

cabrerahector
cabrerahector

Reputation: 3958

The problem is that you're wrapping the %s placeholder in single quotes. That's not needed because WordPress will replace all instances of %s with a proper string containing the value you passed to the prepare() method.

So, taking that into consideration, your code becomes:

$sql = $wpdb->prepare(
    "SELECT {$wpdb->users}.ID FROM {$wpdb->users} 
    WHERE {$wpdb->users}.user_registered <= %s 
    AND {$wpdb->usermeta}.meta_key='custom_status' 
    AND {$wpdb->usermeta}.meta_value=0
    INNER JOIN {$wpdb->usermeta} ON {$wpdb->users}.ID= {$wpdb->usermeta}.user_id", $before);

Additionally, the INNER JOIN ... part of your query has to be placed before your WHERE clause or else MySQL will throw an invalid syntax error:

$sql = $wpdb->prepare(
    "SELECT {$wpdb->users}.ID FROM {$wpdb->users} 
    INNER JOIN {$wpdb->usermeta} ON {$wpdb->users}.ID= {$wpdb->usermeta}.user_id
    WHERE {$wpdb->users}.user_registered <= %s 
    AND {$wpdb->usermeta}.meta_key='custom_status' 
    AND {$wpdb->usermeta}.meta_value=0", $before);

Upvotes: 3

Related Questions