Php mysql group by date question

PHP
1

Hello forums,

I’m trying to create a monthly archives of a news table


$q = mysql_query("SELECT * FROM news GROUP BY post_date  DESC" );
while ($r = mysql_fetch_assoc($q)){
$a=  $r['post_date'] ;

echo  date('M  Y  ',strtotime($a)). '<br />';

}

This one is giving me something like

Feb 2010
Feb 2010
Feb 2010
Feb 2010
Jan 2010
Jan 2010
Dec 2009
Dec 2009

How do I group this so every entry on a particular month will be grouped together as one entry (something similar to wordpress monthly archives)

like:
Feb 2010
Jan 2010
Dec 2009

my MySql post_date column is datetime with a default value of 0000-00-00 00:00:00

Any ideas?
Thanks

2 
SELECT
  YEAR(post_date) AS year,
  MONTH(post_date) AS month
FROM
  news
GROUP BY
  YEAR(post_date),
  MONTH(post_date)
ORDER BY
  YEAR(post_date) DESC,
  MONTH(post_date) DESC
3

Thanks dan I tried that just now but the results are still the same

when I do this:


$q = mysql_query("SELECT post_date FROM news GROUP BY post_date  DESC" );
while ($r = mysql_fetch_assoc($q)){
$a=  $r['post_date'] ;
echo $a .'<br />';
}

it gives me this:

2010-02-08 19:44:50
2010-02-08 19:44:49
2010-01-30 10:22:48
2010-01-30 10:21:42
2010-01-30 10:19:11
2010-01-30 08:15:32
2010-01-29 15:20:48
2010-01-29 15:19:43
2010-01-29 15:19:05

I think it is grouping the entries using the mili seconds

4

Hit refresh, I edited my post with a better query

5

There seems to be an error am I doing something wrong?

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\xampplite\htdocs\baguioTrader\ est.php on line 15
2010-02-13


$q = mysql_query("SELECT   YEAR(post_date) AS year,   MONTH(post_date) AS month
                                   FROM  news GROUP BY   YEAR(post_date),   MONTH(post_date)
                                   ORDER BY  YEAR(post_date) DESC,   MONTH(post_date) DESC" );
while ($r = mysql_fetch_assoc($q)){
$a=  $r['post_date'] ;
echo $a .'<br />';
//echo  date('M  Y ',strtotime($a)). '<br />';

}
6

opps sorry i mis spelled news but it’s only giving me one entry… the latest entry

2010-02-13

7

eeek so weired… without grouping …

$q = mysql_query("SELECT   YEAR(post_date) AS year,  MONTH(post_date) AS month FROM news"  );
echo mysql_num_rows($q);

while ($r = mysql_fetch_assoc($q)){

$a  = $r['post_date'] ;
echo  date('M  Y ',strtotime($a)). '<br />';

}

138Jan 1970
Jan 1970
Jan 1970
Jan 1970
Jan 1970
Jan 1970
Jan 1970
Jan 1970
Jan 1970
Jan 1970

8

There is no post_date column in this result set anymore, just the “year” and “month” aliases.

9

Ok I know the query is returning results exactly the number of rows in my database when I echo the mysql_num_rows

How then can I echo the results …almost there

thanks

10

yse thats it … how? please help furthur huhuhuhu

11

You already know how, just think for a second :smiley:

$months = array(1 => 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');

echo $months[$r['month']] . ' ' . $r['year'];

Or SELECT MONTHNAME(post_date) instead of MONTH()

12

never mind I got it weee Thanks Dan you just saved my life … again

echo $r[‘year’]

Cheers

13

I have to add Dan rules !!!