wp query - wpdb get_results() returns only 2 rows

I am trying to create custom admin widget to present how many users has registered within last days. I have created function as below:

function mdbootstrap_add_dashboard_recent_users(){ 
global $wpdb;

$query = "
    SELECT count(*) as counter, DATE(wp_users.user_registered) as regdate
    FROM wp_users 
    GROUP BY DATE(wp_users.user_registered) 
    ORDER BY DATE(wp_users.user_registered) desc";

$posts = $wpdb->get_results($query,OBJECT);
print_r( $posts);
 }

However for some reason it's returning only 2 rows:

Array ( [0] => stdClass Object ( [counter] => 1 [regdate] => 2016-01-12 ) [1] => stdClass Object ( [counter] => 3 [regdate] => 2016-01-04 ) )

Which BTW shows wrong values, as the same query run from PHP MyAdmin gives result as follow:

• counter regdate
• 16 2016-01-13
• 37 2016-01-12
• 51 2016-01-11
• 25 2016-01-10
• 21 2016-01-09
• 26 2016-01-08
• 24 2016-01-07
• 24 2016-01-06
• 18 2016-01-05
• 20 2016-01-04
• 5 2016-01-03

I was looking for similar issue however most of the topics covers issue with only 1 row when people use get_row instead of get_results. I have played with different parameters like ARRAY_N, OBJECT, ARRAY_A but didn't work - query always returns 2 rows.

I am trying to create custom admin widget to present how many users has registered within last days. I have created function as below:

function mdbootstrap_add_dashboard_recent_users(){ 
global $wpdb;

$query = "
    SELECT count(*) as counter, DATE(wp_users.user_registered) as regdate
    FROM wp_users 
    GROUP BY DATE(wp_users.user_registered) 
    ORDER BY DATE(wp_users.user_registered) desc";

$posts = $wpdb->get_results($query,OBJECT);
print_r( $posts);
 }

However for some reason it's returning only 2 rows:

Array ( [0] => stdClass Object ( [counter] => 1 [regdate] => 2016-01-12 ) [1] => stdClass Object ( [counter] => 3 [regdate] => 2016-01-04 ) )

Which BTW shows wrong values, as the same query run from PHP MyAdmin gives result as follow:

• counter regdate
• 16 2016-01-13
• 37 2016-01-12
• 51 2016-01-11
• 25 2016-01-10
• 21 2016-01-09
• 26 2016-01-08
• 24 2016-01-07
• 24 2016-01-06
• 18 2016-01-05
• 20 2016-01-04
• 5 2016-01-03

I was looking for similar issue however most of the topics covers issue with only 1 row when people use get_row instead of get_results. I have played with different parameters like ARRAY_N, OBJECT, ARRAY_A but didn't work - query always returns 2 rows.

• wp-query
• database
• query
• wpdb

Share Improve this question edited Aug 4, 2017 at 8:50 Johansson 15.4k1111 gold badges4343 silver badges7979 bronze badges asked Jan 13, 2016 at 10:20 Dawid AdachDawid Adach 17111 gold badge22 silver badges99 bronze badges 4

• Check result count after remove GROUP BY DATE(wp_users.user_registered) and compare counter value. I know this is not proper steps but we need to check it. – AddWeb Solution Pvt Ltd Commented Jan 13, 2016 at 10:47
• SELECT count(*) as counter, DATE(wp_users.user_registered) as regdate FROM wp_users ORDER BY DATE(wp_users.user_registered) desc" results in Array ( [0] => stdClass Object ( [counter] => 4 [regdate] => 2016-01-04 ) ) – Dawid Adach Commented Jan 13, 2016 at 10:54
• From the result [counter] => 4 [regdate] => 2016-01-04, Are you sure about result from PHP MyAdmin is correct (into your question)? – AddWeb Solution Pvt Ltd Commented Jan 13, 2016 at 11:04
• Yes, I am sure. I have compared table from Wordpress Admin and results from MyAdmin and they match. – Dawid Adach Commented Jan 13, 2016 at 12:01

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

You can try below query

SELECT (
    SELECT count(*) 
    FROM wp_users as temp 
    WHERE DATE(temp.user_registered) = DATE(wp_users.user_registered)
    ) as counter, 
wp_users.*
FROM wp_users

Issue might be related to the way the DATE() function is used in the GROUP BY and ORDER BY clauses. When you use DATE(wp_users.user_registered) in these clauses, it might not group the records as expected. Instead, try using the DATE_FORMAT() function to format the user_registered date in a way that's compatible with GROUP BY and ORDER BY.

Try modify your query below code like

function mdbootstrap_add_dashboard_recent_users(){ 

    global $wpdb;

    $query = "SELECT COUNT(*) as counter, DATE_FORMAT(wp_users.user_registered, '%Y-%m-%d') as regdate
FROM wp_users 
GROUP BY DATE_FORMAT(wp_users.user_registered, '%Y-%m-%d') 
ORDER BY DATE_FORMAT(wp_users.user_registered, '%Y-%m-%d') DESC";

    $posts = $wpdb->get_results($query, OBJECT);

    // Print the results for testing
    foreach ($posts as $post) {
        echo "Date: " . $post->regdate . ", Count: " . $post->counter . "<br>";
    }
}


// Call the function
mdbootstrap_add_dashboard_recent_users();