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

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 );

score 2 · Accepted Answer · answered Nov 13 '22 at 22:09

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

score 0 · Answer 2 · edited Nov 14 '22 at 04:20

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 '22 at 04:20

mickmackusa

43,625
12
83
136

answered Nov 14 '22 at 04:08

theboss_dev

1,152
1
8
21

Why is `prepare()` necessary? – mickmackusa Nov 14 '22 at 04:21
it is not necessary until we bind a php variable to the query. – theboss_dev Nov 14 '22 at 04:28
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. – mickmackusa Nov 14 '22 at 04:28
but i always use prepared statement in my custom queries. is it good or not? – theboss_dev Nov 14 '22 at 04:29

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

2 Answers2