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

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

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

Solution

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