Question 2254 in Wordpress Question posted in
The official Wordpress documentation can be found here.

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

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 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/

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search