make an archive

Thursday 21 August 2008 7:06:41 am

hello all,
i want to know how can i fetch (displayed) or group nodes by year and by month. in
design\ezwebin\templates\parts\blog\extra_info.tpl i have see the following code :

<div class="attribute-archive">
                            <h1>{"Archive"|i18n("design/ezwebin/blog/extra_info")}</h1>
                            <ul>
                            {foreach ezarchive( 'blog_post', $used_node.node_id ) as $archive}
                                <li><a href={concat( $used_node.url_alias, "/(month)/", $archive.month, "/(year)/", $archive.year )|ezurl} title="">{$archive.timestamp|datetime( 'custom', '%F %Y' )}</a></li>
                            {/foreach}
                            </ul>
                        </div>

i have try this in my pagelayout with

{$used_node=2} 

but it doesn't work and i have the following sql errors :

Query error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY MONTH( FROM_UNIXTIME( ezcontentobject_attribute.data_int ) ),
        ' at line 14. Query: SELECT MONTH( FROM_UNIXTIME( ezcontentobject_attribute.data_int ) ) AS month,
                                                YEAR( FROM_UNIXTIME( ezcontentobject_attribute.data_int ) ) AS year,
                                                UNIX_TIMESTAMP( CONCAT( YEAR( FROM_UNIXTIME( ezcontentobject_attribute.data_int ) ) , '-', MONTH( FROM_UNIXTIME( ezcontentobject_attribute.data_int ) ) , '-', 01 ) ) AS timestamp
                                        FROM ezcontentobject_attribute,
                                             ezcontentclass,
                                             ezcontentclass_attribute,
                                             ezcontentobject_tree
                                        WHERE ezcontentclass_attribute.contentclass_id = ezcontentclass.id
                                            AND ezcontentclass.identifier = 'article'
                                            AND ezcontentclass_attribute.id = ezcontentobject_attribute.contentclassattribute_id
                                            AND ezcontentclass_attribute.identifier = 'publication_date'
                                            AND ezcontentobject_attribute.contentobject_id = ezcontentobject_tree.contentobject_id
                                            AND ezcontentobject_tree.parent_node_id = 
                                            GROUP BY MONTH( FROM_UNIXTIME( ezcontentobject_attribute.data_int ) ),YEAR( FROM_UNIXTIME( ezcontentobject_attribute.data_int ) )
                                                  

and this error

SELECT MONTH( FROM_UNIXTIME( ezcontentobject_attribute.data_int ) ) AS month,
                                                YEAR( FROM_UNIXTIME( ezcontentobject_attribute.data_int ) ) AS year,
                                                UNIX_TIMESTAMP( CONCAT( YEAR( FROM_UNIXTIME( ezcontentobject_attribute.data_int ) ) , '-', MONTH( FROM_UNIXTIME( ezcontentobject_attribute.data_int ) ) , '-', 01 ) ) AS timestamp
                                        FROM ezcontentobject_attribute,
                                             ezcontentclass,
                                             ezcontentclass_attribute,
                                             ezcontentobject_tree
                                        WHERE ezcontentclass_attribute.contentclass_id = ezcontentclass.id
                                            AND ezcontentclass.identifier = 'article'
                                            AND ezcontentclass_attribute.id = ezcontentobject_attribute.contentclassattribute_id
                                            AND ezcontentclass_attribute.identifier = 'publication_date'
                                            AND ezcontentobject_attribute.contentobject_id = ezcontentobject_tree.contentobject_id
                                            AND ezcontentobject_tree.parent_node_id = 
                                            GROUP BY MONTH( FROM_UNIXTIME( ezcontentobject_attribute.data_int ) ),
                                                      YEAR( FROM_UNIXTIME( ezcontentobject_attribute.data_int ) )

i need same help to resolve this problem.
thanks in advance

The theory is when we know everything and nothing works.
The practice is when everything works and nobody knows why.
If the practice and theory are met, nothing works and we do not know why.

Albert Einstein