sql query working in phpmyadmin - not working within php

Question

I have a function in a WordPress plugin I am about to write (my first one) that should check if a given ID belongs to a user with a certain role ('dance_couple' in this case).

$sc_user_id is the id passed in the shortened code.

function user_is_dance_couple($sc_user_id){
   global $wpdb;
   $count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $wpdb->usermeta WHERE user_id = %d AND meta_value LIKE `%dance_couple%`", $sc_user_id));
   if($count == 1){ return true; } else { return false; }
}

if (user_is_dance_couple(1)) {
  // here comes what should be done if the user has the right role
}

Question: The weird thing is, that I copied a simpler version of the function (https://wordpress.stackexchange.com/questions/165691/how-to-check-if-a-user-exists-by-a-given-id) and adapted it. so the only thing I changed is the bit of sql code (that works fine in sql), the function as it was before also works fine - I have no idea what I did wrong.

I already tried:

changing the single quotes to the thing between the double quotes " ` "
taking the sting out and adding it in a variable
adding the table names in before the columns

Thanks for the help!

@maya Would you please remove $string variable from your query and after check it? — Purvik Dhorajiya
– Purvik Dhorajiya, Commented Jan 6, 2017 at 4:36
$string variable is gone - but can't be the problem ... wasn't there before (as explained in list, that just was one of several tries) @itzmukeshy7 - I tried that - didn't work :( — maya
– maya, Commented Jan 6, 2017 at 12:50

RRikesh · Accepted Answer · 2017-01-06 05:27:21Z

1

No need for SQL queries. You could do something like that:

$user_id = 1; # or whatever user you need
$role_to_find = 'dance_couple';

$user = get_userdata( $user_id );

if( false !== $user ){
    # User found, do your stuff
    $user_roles = $user->roles;
    # var_dump( $user_roles );

    if( in_array( $role_to_find, $user_roles ) ){
        echo 'User has the role';
    }else {
        echo 'User does not have the role';
    }
}else{
    echo 'User not found';
}

answered Jan 6, 2017 at 5:27

RRikesh

14.5k5 gold badges55 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

maya Over a year ago

thanks a lot - I already had something similar - but without the second variable in "in_array" - maybe the plain text killed it or I made some other mistake.

Marcello Perri · Accepted Answer · 2017-01-06 12:49:14Z

0

I don't think you can use the GLOBAL object $wpdb inside a query and inside the quotation. What I would do is create another variable and put the value of $wpdb->usermeta in that variable.

in this way:

$user = $wpdb->usermeta;
global $wpdb;
$query = "SELECT COUNT(*) FROM %d WHERE user_id = %d AND meta_value LIKE `%dance_couple%`";
$count = $wpdb->get_var($wpdb->prepare($query, $user, $sc_user_id));

Or the long version:

$count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %d WHERE user_id = %d AND meta_value LIKE `%dance_couple%`", $user, $sc_user_id));

The syntax of the query looks correct, try in this way

answered Jan 6, 2017 at 12:49

Marcello Perri

6181 gold badge9 silver badges26 bronze badges

Collectives™ on Stack Overflow

sql query working in phpmyadmin - not working within php

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related