Crazy SQL scripts involving DATELINE and DATE()

[Hwulex]

Ok.
I'm in the process of writing a new hack, and I'm hoping to include a 'member of the month' function to it. I've been thinking of different ways to do this (preferably without Cron Jobs), and thought I'd come up with a solution, but I'm having problems.
The motm is determined by a condition attached to each post.

Basically, the way I thought I'd do it is (pseudocode):

if (currentmonth != storedmonth)
  query_first(
    SELECT username, userid
    FROM user
    WHERE dateline <= todaysdate
    AND dateline >= a month ago
    ORDER BY condition
    DESC
  )
}

Basically, I need a way (within MySQL) of retrieving ONLY the posts that were made in the last month. Now, I thought I could embed the date() function within it, but that doesn't work.

Anyone any ideas on how I can do this?

As a temp mock-up, I've used:

    WHERE p.dateline >= $today-(60*60*24*30)
    AND p.dateline <= $today-(60*60*24)

But that's not terribly accurate, depending on when it gets executed, and the fact that not every month has 30 days etc.

I did try:

    WHERE ".date('mY',."p.dateline".)." = $currentmonth

But that throws out a parse error.


I hope this makes some sort of sense. I know I've been a little vague, but I don't wish to give too much away at this point. But if people need more info I will do my best to comply

Hwu