0

I have a MySQL DB where I store dates in the following format

2017-04-03

I need to split or search dates to get all dates and records for January, Feb, March and so on in between all the dates I have in DB

$result = mysql_query("SELECT * FROM lbs_trace_etrack WHERE MONTH(lbs_date) = MONTH(CURDATE()) AND YEAR(lbs_date) = YEAR(CURDATE()) ORDER BY lbs_date DESC, lbs_time DESC");

I use the above query to search Current month and year. I am drawing up a graph that shows me stats from each month this is the reason I want each months count

I need to place the counts for the search in the following format. 

var seriesData = [{
  name: 'Hijackings',
  data: [Value Jan, Value Feb, Value March, and all the other months ]
}, {

If anyone can just help me with the filter on each month query would help me greatly

Improve this question

2 Answers 2

Reset to default
2

Try this query:

select count(*) as total, MONTH(lbs_date) as track_month, YEAR(lbs_date) as track_year
FROM lbs_trace_stack t
GROUP BY track_year, track_month

Or if you only want month or only want year, you have just to remove

 MONTH(lbs_date) and track_month  --> if you want to see the year remove this

 YEAR(lbs_date) and track_year  --> if you want to see the month remove this

from the select part and group by part.

In addiction, if you want to filter on one or more months you can of course use the where statement, to set up your filter. There are a several ways to write some where condition that do the same things, for example:

SELECT COUNT(*) as total, MONTH(lbs_date) as track_month
FROM lbs_trace_stack t
-- WHERE track_month = 2  -> February
-- WHERE track_month > 2  -> Form March
-- WHERE track_month = 2 OR track_month = 3 -> February or March
-- WHERE MONTHNAME(lbs_date)='February'  --> if you want to use month name
GROUP BY track_month
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This works great but is there some way I can state in this query that it should group all January together. I will have to run a new query to group the other months Can I somewhere specify what month it should group to. I need to group months together even if I have to run different queries but need to get them month by month and state in that query it is a specific month it should filter
I've just edit the answer, please check now if fits your needs.
It still filters all the months I need to specify the month I have tried this $query = "select count(*) as total, MONTH(rep_date) as track_month FROM bureau WHERE type = 'Overdue' AND MONTH(rep_date)= 1 GROUP BY track_month " ~ where MONTH(rep)date)=1 trying to focus on month 01 January
please check again
Hi, if this post answer to your question could you please mark the question as solved? Thank you
0

I have found that this query works

$query = "SELECT rep_date FROM `bureau` WHERE MONTH(rep_date) = 1 AND type = 'Overdue'";
        $result = mysql_query($query);
echo " ".mysql_num_rows($result)." "
?>
                          <?php
while($rows=mysql_fetch_array($result)){
?>

Using the MONTH(rep_date)=1 will filter all January changing 1 to 2 will do Feb and so on

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.