1

I'm wanting to get all the datetime column in my db but but it in a graph using Chart.js, now hard coding data in works fine but I want to know i can use my db datetime col to count how many sign ups happened is each month then return this to the graph, anyone know how to do this?

Currently when someone signs up it enters the date like: 2013-12-18 12:03:50

So I need to grab the months then post this to the graph :)

the_unforgiven_II
  • 361
  • 3
  • 10
  • 28

1 Answers1

0

Just use MySQL MONTH() function, like:

SELECT MONTH(table.date) from table WHERE ...

In case you want to count the amount by month, so use the count function and group the query by MONTH(datetime). If you need help with this, please leave a comment.

For example, if you have a table that has a list of users, it will look something like that:

SELECT YEAR(register_date), MONTH(register_date) AS MONTH, COUNT(*) AS registered FROM users GROUP BY YEAR(register_date), MONTH(register_date) - this will give you the result of registered users count by year and month.

user
  • 3,058
  • 23
  • 45
  • Will that count the months, or will i need to include count() – the_unforgiven_II Dec 18 '13 at 12:27
  • This last col is what i need:http://d.pr/i/OiUs signup_date which has type of datetime – the_unforgiven_II Dec 18 '13 at 12:30
  • I called it `register_date`, just use the other name. – user Dec 18 '13 at 12:30
  • I just want it to then display in the chart like this hard coded version http://d.pr/i/dIOK – the_unforgiven_II Dec 18 '13 at 12:33
  • Then the question you are asking in the top is a different one. Here is an example for the chart with some code - http://www.arcadiacharts.com/create_JS_chart – user Dec 18 '13 at 12:34
  • using the example you gave and i called it with a set $var in json_encode format i get [{"YEAR(signup_date)":2013,"MONTH":6,"registered":10},{"YEAR(signup_date)":2013,"MONTH":7,"registered":3},{"YEAR(signup_date)":2013,"MONTH":11,"registered":2},{"YEAR(signup_date)":2013,"MONTH":12,"registered":9}] Which is great but now how do i order those to go in the correct months based on my graph format like so : http://paste.laravel.com/1ffI – the_unforgiven_II Dec 18 '13 at 12:43
  • You need to create a map of month names to month numbers in order to match the query return result. There is a bunch of solutions over here - http://stackoverflow.com/questions/1643320/get-month-name-from-date-using-javascript – user Dec 18 '13 at 12:46