2

I have this:

    global $wpdb;
    $wpdbp = $wpdb->prepare('SELECT EXISTS ([some query] WHERE user_id =%d);',$target_user_id);
    $result = $wpdb->get_results($wpdbp);

I want to know if the query result is 1 or 0. But a var_dump() of $result give something like:

array (size=1)
  0 => 
    object(stdClass)[4592]
      public 'EXISTS ([some query] WHERE user_id =2)' => string '0' (length=1)

Which means I should first get element 0 of array, but then, I need to access a property which name is literally the whole query.

I yet need to test if that is even doable in php (I guess yes but I don't remember in this language precisely), and what happens if I have multiline query ... Anyway I find that so ugly ... is there a cleaned way to get query result? Maybe there's a way to give a name string to the query or so?


Here is what I'm trying and this isn't even working ...

$qeryAsPropertyName = substr($wpdbp,7, -strlen($wpdbp-1));
$result0 = $result[0]->$qeryAsPropertyName;
  • 1
    That might be a MySQL-generated column name. You could try SELECT EXISTS (...) AS name to give it a different name (where you can quote name in backticks). – Rup Apr 16 at 14:31
  • This worked thank you. If you write it as answer I can check it as solution. – TTT Apr 16 at 14:34
4

This answer explains what the OP saw with column names and how to work with that, but the real answer is to use get_var() as in Howdy_McGee's answer.


The string you're seeing is the column name that MySQL is using for the result, because it doesn't have any better ideas. One way is to give it an explicit name to use instead with AS, e.g.

global $wpdb;
$wpdbp = $wpdb->prepare('SELECT EXISTS ([some query] WHERE user_id =%d) AS `exists`;',
                        $target_user_id);
$result = $wpdb->get_results($wpdbp);

then the column name will be exists, i.e.

$result = $result[0]['exists'];

However I'm surprised there isn't a 'execute query and return scalar' method in $wpdb that you can use instead to just fetch a single result like this. There is a better way, but I'd missed it as I was searching for terms like 'scalar', bah.

6

The WPDB Class has quite a few methods which vary what will be returned.

Using WPDB::get_results() returns an array of objects whose properties end up being what it expects to be returned. In this case may be best to alias your subquery. For example, if I wanted to check if user ID 1 exists I could say:

$results = $wpdb->get_results( "SELECT EXISTS( SELECT ID FROM {$wpdb->users} WHERE ID = 1 ) AS 'exists'" );

if( ! empty( $results ) && $results[0]->exists ) {
    /* ... */
}

A better solution would be, if you just want one thing returned, you could use WPDB::get_var()

$exists = $wpdb->get_var( $wpdb->prepare( "
    SELECT EXISTS ( [some query] WHERE user_id = %d )
", $user_id ) );

if( $exists ) {
    /* ... */
}

Or if you wanted the username by ID:

$username = $wpdb->get_var( $wpdb->prepare( "
    SELECT user_login FROM {$wpdb->users} WHERE ID = %d
", $user_id ) );

if( ! empty( $username ) ) {
    printf( 'User %d user name is: %s', $user_id, $username );
}

That being said your best bet is to read through the documentation and look at the available methods to figure out which is best in your user case:

https://codex.wordpress.org/Class_Reference/wpdb

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.