0

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.

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. 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 ) ) 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)? 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. Commented Jan 13, 2016 at 12:01

2 Answers 2

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
3
  • But this doesn't give the same result - it returns list of users while I want to get count per day Commented Jan 13, 2016 at 12:01
  • You can sort it by user_registered and 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.user_registered as registered_date FROM wp_users Commented Jan 13, 2016 at 12:16
  • Did it worked?? Commented Jan 13, 2016 at 13:20
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.