php,MySql json output for number of days of current month

Question

I need to query data in order to display number of registered users for each day of the current month, in order to pass json array to jQuery Flot.

This is what I have so far:

      for ($i = 1; $i <= date('t'); $i++) {
          $date = date('Y') . '-' . date('m') . '-' . $i;
          $sql = $db->first("SELECT COUNT(*) AS total FROM users WHERE (DATE(created) = '" . $date . "' LIMIT 1) GROUP BY DAY(created)");

          ($sql) ? $jdata['regs']['data'][] = array($i, (int)$sql->total) : $jdata['regs']['data'][] = array($i, 0);
          $jdata['xaxis'][] = array($i, date('j', strtotime($date)));
      }

      print json_encode($jdata);

The above works fine, except that it makes 30 queries due to the for loop. I would like to be able to optimize it, by eliminating for loop and use it on $sql array instead.

I guess you can do that with only one query, grouping by day. Something link SELECT day(created), COUNT(*) FROM users GROUP BY day(created); — Choma
– Choma, Commented Apr 19, 2015 at 1:55

A l w a y s S u n n y · Accepted Answer · 2015-04-19 01:59:48Z

1

You can try this way to get number of registered users for each day of the current month using BETWEEN clause . Let me know it's works for you or not.

SELECT DATE(created),COUNT(id) as total
FROM users
WHERE DATE(created) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 MONTH)  
GROUP BY DATE(created)

edited Apr 19, 2015 at 1:59

answered Apr 19, 2015 at 1:33

A l w a y s S u n n y

38.8k9 gold badges68 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3176519 Over a year ago

INTERVAL 30 DAY will not work, since some months have more than 30 days.

Choma Over a year ago

The interval should be between 1 and 31. Anyway, I think it would be easier to calculate the interval in php and pass it as a parameter.

Collectives™ on Stack Overflow

php,MySql json output for number of days of current month

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related