[SOLVED] query last 6 months

[borabora12]

I want to show the last 6 months like this:

 

Jul

Aug

Sep

Oct

Nov

Dec

 

I already have this:

SELECT date, date_FORMAT(date, '%b') as 6thmonth FROM the_table ORDER BY id DESC LIMIT 1

 

Which gets the last date entered into the table. I was wondering how I could make either mysql or PHP find out the other 5 months. Thanks in advance!

[fenway]

Huh? You have limit 1.

[borabora12]

I know its limit 1 but is there any way for PHP or SQL to figure out what the 5 months prior to the month fetched from the table is?

[fenway]

Oh, i see what you want... do this in php.

[borabora12]

How would i manage this?

[borabora12]

can anyone help?

[thirdearth]

Can you clarify a little?  Are you just wanting it to pull the previous 5 months from whatever month you pulled out of the table?  Or the previous 5 months that an event happened?  Do you have any code of what you are trying to accomplish?

 

Without knowing exactly the purpose you are trying to accomplish here, I will assume that you are trying to output the previous 5 months of the calendar year.  There are many ways that you could do this.  If they are listed in incremental order within this table, you could move your LIMIT 1 to LIMIT 6.  If that doesn't work, you could always create a new table with a first column that lists all of the months in the year, then the second column with their previous 5 months, or something to that nature.  Then, when you want to call those previous months, simply do a select on the table you just created like so:

 

SELECT month1, month2, month3, month4, month5 FROM table WHERE currentmonth = $variable

 

Am I even on the right track?

[borabora12]

Are you just wanting it to pull the previous 5 months from whatever month you pulled out of the table?

YES!

 

The table itself contains weekday values. So if there is a way to do it that way it would be great, and if it can't just allow php to figure out the last 5 months based on what was extracted from the table,

[borabora12]

SELECT date_FORMAT(date, '%b') as 6months FROM the_table GROUP BY 1 ORDER BY id DESC LIMIT 6

 

the "date" field is a in the "DATE" MySQL format.

 

i figured it out, i used the code above. thanks for the help!