display records ordered by month and year >> help needed

[sofasurfer]

hello!

I need to display records from my DB according to the following schema:

Junho 2005
event1
event2
eventN

Setembro 2005
event1
event2
eventN

etc.

each "event" in the MySQL DB has several fields, including a date field (aaaa/mm/dd).

My question:

1st, how to display the date field in Portuguese: discarding the day, and showing for instance "Maio 2005";

2nd, how to display the events "inside" the "Month / year" that they belong.

I'll have 2 repeated regions: one for the Month/year and another one, neasted inside for the events.

My query is something like:
SELECT id, nome, data, localidade, concelho, organizacao, tipoprova, tipo_id, URLimagem, DATE_FORMAT( data , '%%M' ) , DATE_FORMAT( data , '%%Y' ) FROM prova WHERE prova.tipo_id = %s ORDER BY data ASC ", $colname_rsListaProvas);

in dreamweaver, date format can't be outputed... but when I try it at PHPMyAdmin, the query outputs well... I'm I doing something wrong?

if I do this
<?php echo $row_rsListaProvas['DATE_FORMAT( data , '%M' )']; ?>
<?php echo $row_rsListaProvas['DATE_FORMAT( data , '%Y' )']; ?>

in the php script, gives me a parse error:
Parse error: parse error, expecting `']'' in .... list_categoria.php on line 109 (the line of those two php echo)

What is wrong in the echo statements?? :rolleyes:

In order to output the events 'under' its respective month, I could select all the rows GROUP BY the month ORDER BY the day and then do a while (loop) and use a if () block to display the month and year header when the month and year changes... but how can i a group by month, if I just have a 'date' field in my DB?
or make 2 selects (Month and year and then select events in that month...)

All help is needed, 'cause I'm a newbie...

thx in advance!

[bradgrafelman]

For starters, look at these lines:

<?php echo $row_rsListaProvas['DATE_FORMAT( data , '%M' )']; ?> 
<?php echo $row_rsListaProvas['DATE_FORMAT( data , '%Y' )']; ?>

You have unescaped quotes! Try this:

<?php echo $row_rsListaProvas['DATE_FORMAT( data , \'%M\' )']; ?> 
<?php echo $row_rsListaProvas['DATE_FORMAT( data , \'%Y\' )']; ?>

An alternate solution would be to alias the columns in your query, i.e.:

SELECT id, nome, `data`, localidade, concelho, organizacao, tipoprova, tipo_id, URLimagem, DATE_FORMAT( data , '%%M' ) AS date_m , DATE_FORMAT( data , '%%Y' ) AS date_y FROM prova WHERE prova.tipo_id = %s ORDER BY data ASC ", $colname_rsListaProvas); 

Then, you could use the alias names in your code, like so:

<?php echo $row_rsListaProvas['date_m']; ?> 
<?php echo $row_rsListaProvas['date_y']; ?>