Need help on serendipity SQL query..

[andy0212]

Hello guys, I'm currently making a blog using Serendipity as the backend and Flash as the interface. But to get some info about my blog to appear at my Flash interface, such as yearly and monthly archives, it's quite complicated for me.

My problem is, in Wordpress, for example, the SQL query to count archives is

Code: Select all

SELECT DISTINCT YEAR( post_date ) AS `year` , MONTH( post_date ) AS `month` , count( ID ) AS posts
FROM andyWP_posts
WHERE post_type = 'post'
AND post_status = 'publish'
GROUP BY YEAR( post_date ) , MONTH( post_date )
ORDER BY post_date DESC
LIMIT 0 , 30

But because of Serendipity using timestamp and i'm not so familiar with timestamp, I have no clue what should I do. I'm sure that Serendipity already have their own SQL query for archives. But I can't trace how it works. I hope you guys can help me.

[garvinhicking]

Hi!

S9y uses PHP code to format the date/year. You can use the SQL functions "FROM_UNIXTIME" and "DATE_FORMAT" though to format a timestamp.Have a look at the MySQL documentation, then you can embed that in your SQL query.

If you don't succeed, tell me again in the middle of next week, then I can have a closer look if no one else can give you a full SQL query for it

Regards,
Garvin

[andy0212]

Thank you for the tips! Before this I'm looking at the wrong SQL functions, now I know the answer. That's what I need for now. I will ask another question when I have one.

Thanks again.