sql - Is there a (better) way to access $wpdb results?

admin管理员组

文章数量:1022688

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;

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;

• wpdb
• sql

Share Improve this question edited Apr 16, 2019 at 14:32 TTT asked Apr 16, 2019 at 14:19 TTTTTT 32911 gold badge44 silver badges1717 bronze badges 2

• 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 Commented Apr 16, 2019 at 14:31
• This worked thank you. If you write it as answer I can check it as solution. – TTT Commented Apr 16, 2019 at 14:34

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 5

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.

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/Class_Reference/wpdb

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;

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;

• wpdb
• sql

Share Improve this question edited Apr 16, 2019 at 14:32 TTT asked Apr 16, 2019 at 14:19 TTTTTT 32911 gold badge44 silver badges1717 bronze badges 2

• 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 Commented Apr 16, 2019 at 14:31
• This worked thank you. If you write it as answer I can check it as solution. – TTT Commented Apr 16, 2019 at 14:34

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 5

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.

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/Class_Reference/wpdb

本文标签： sqlIs there a (better) way to access wpdb results