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.

  • 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 Jan 13 '16 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 Jan 13 '16 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 Jan 13 '16 at 11:04
  • Yes, I am sure. I have compared table from Wordpress Admin and results from MyAdmin and they match. – Dawid Adach Jan 13 '16 at 12:01
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
  • But this doesn't give the same result - it returns list of users while I want to get count per day – Dawid Adach Jan 13 '16 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 – Mayur Chauhan Jan 13 '16 at 12:16
  • Did it worked?? – Mayur Chauhan Jan 13 '16 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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