How to fetch all data from a SELECT query as an array of associative rows in Wordpress? [duplicate]

Question

I take it from the database and send it via ajax (wordpress). Everything works fine, except that I don't get the first row from the database. As I read on the Internet, a similar problem is in the array, maybe. Can someone explain and help me fix it so that all rows are displayed?

Code:

$sql = $wpdb->prepare( "SELECT * FROM users" );

$count = 0;
$user_object = array();
foreach( $wpdb->get_results( $sql ) as $key => $row ) {
    $user_id = $row->user_ID;
    $user_name = $row->user_name;

    $user_object[$count]= array(
        "user_ID"=>$user_id,
        "user_name"=>$user_name,
    );

    $count++;
}
 
return wp_send_json( $user_object );

mickmackusa · Accepted Answer · 2022-11-13 22:09:29Z

2

You don't need to loop your results at all, your code can be simplified further.

There are no placeholders to bind variables to, so there is no need to use prepare().
There is no need to loop and manually set the indexes on the first level or the associative keys on the second level because get_results() with ARRAY_A will already do this for you.

Code:

 return wp_send_json($wpdb->get_results('SELECT * FROM users', ARRAY_A));

See: https://developer.wordpress.org/reference/classes/wpdb/get_results/

When you want to add PHP variables to your SQL, then using a prepared statement is appropriate. For example: WordPress prepared statement with IN() condition

answered Nov 13, 2022 at 22:09

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

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

Comments

mickmackusa · Accepted Answer · 2022-11-14 04:20:01Z

0

WordPress database tables always use prefix so you have to include prefix using $wpdb->prefix.'users' Or if you are trying to get WordPress core users then you can use $wpdb->users to get the table name with prefix.

Reference: https://developer.wordpress.org/reference/classes/wpdb/.

You can use ARRAY_A for second output parameter of get_results() so you don't have to loop through results.

Reference: https://developer.wordpress.org/reference/classes/wpdb/get_results/.

Code:

global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->users}" );
return wp_send_json($wpdb->get_results($sql, ARRAY_A));

Or alternative there is another way to getting users in WordPress using get_users().

Reference: https://developer.wordpress.org/reference/functions/get_users/

edited Nov 14, 2022 at 4:20

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

answered Nov 14, 2022 at 4:08

theboss_dev

1,1961 gold badge10 silver badges25 bronze badges

4 Comments

mickmackusa Over a year ago

Why is prepare() necessary?

theboss_dev Over a year ago

it is not necessary until we bind a php variable to the query.

mickmackusa Over a year ago

Okay, that's what I thought. I prefer to minimize the number of method calls in my script -- especially if there is no benefit. It looks like you could have just mentioned {$wpdb->users} as a comment under my answer. We have reason to believe that the asker's project isn't using table prefixes. There seems to be a significant amount of redundancy between our answers.

theboss_dev Over a year ago

but i always use prepared statement in my custom queries. is it good or not?

Collectives™ on Stack Overflow

How to fetch all data from a SELECT query as an array of associative rows in Wordpress? [duplicate]

2 Answers 2

Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Linked

Related