Php Dates

[c_shelswell]

Hi I'm getting a bit stuck here and would really appreciate some help. I had made a bit of code to find the first and end date of each month over a range i.e. if a user selected to view 3 months of data it would find and create an array like

 

2008-01-01, 2008-01-31, 2008-02-01, 2008-02-29, 2008-03-01, 2008-03-31 etc I could then use the first and last date in my mysql query to cycle thru each month range and get the relevant data. This was all working fine till I started getting over a years worth of data. This is where i'm struggling I've got the first date i.e 2007-02-14 and I have the last date which will always be today. Is there an easy way to get all the first and end dates of each month in that range?

 

My code is below but i think it might be pretty useless:

 

if ($startMonth > $endMonth)
    {
        for ($i = $startMonth; $i <= 12; $i++)
        {
            $startDatesArray[] = strtotime(($currYear - 1) . "-" . $i . "-01");
        }
        $x = count($startDatesArray);
        for ($i = 01; $i <= $endMonth; $i++)
        {
            $startDatesArray[$x] = strtotime(($currYear) . "-" . $i . "-01");
            $x++;
        }
    }
    /*** if it's not a new year and the previous month is less than the end month do below ********/
    else
    {
        for ($fm = $startMonth; $fm < $endMonth + 1; $fm++)
        {

            $monthArray[$fm] = $fm;
            $days_in_month[$fm] = cal_days_in_month(CAL_GREGORIAN, $monthArray[$fm], $currYear);
            $startDatesArray[$fm] = strtotime($startYear . "-" . $monthArray[$fm] . "-01");
        }
    }

 

many thanks

 

 

[PFMaBiSmAd]

Not knowing how you are using the results, I would just perform one query to get the data from the first start date through the final end date. There would generally be no need to perform a query for each month and if you need something like a monthly sum or count, why not just GROUP BY the extracted year/month from the records and let the database do the work for you. If you need to format and output the data by year/month, just test for a change in the year/month to start a new section in the output.

[c_shelswell]

I need to display the data in one big table by month it's for an accounting area. So if the user selects "Show last 2 years" it will show each month at a time on one big table.

 

Cheers

[PFMaBiSmAd]

I recommend that you add the following to your SELECT statement -

 

EXTRACT(YEAR_MONTH FROM your_date_column) as yearmonth

Then you can just test the yearmonth value in your application code to start a new section in your output when the value changes.