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

[Icheb]

First, get the number of the current month with PHP's date (http://www.php.net/manual/en/function.date.php), then get the UNIX time of the first day in the current month and query the table for all entries which have a higher value in the time column.
There you go :-) .


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

The reason why this doesn't work is the fact that p.dateline is no valid timestamp. Even it were, it wouldn't work since the query gets parsed by PHP and then it's being sent to the MySQL server, which returns the values for the queried fields. But date() needs the timestamp *before* the query is sent.
And btw, it doesn't work for a second reason: The value to the left has to be the name of a column, but i doubt date() will return the name of an already existing column.

[Hwulex]

Thanks for the suggestion, but I still don't think that will work, for the same reason I post above. AFAIC tell, that's the same logic as I was trying to use first time.
The problem is, I need to retrieve all posts from the previous month to this one, and I can't see a short way of doing it without much code writing.


I understand what you're saying about the quoted code. I never expected it to work, was just a wild stab in the dark out of desperation.
Btw, the p.dateline that you refer to is valid, but I haven't quoted the rest of the query. p is an alias for the post table which is set earlier in the query.

Cheers.

[Icheb]

I never said that p.dateline isn't valid. What i said was that the where-clause is not valid. You have to supply the name of a column there, but date() sure won't give you the name of an existing column.

[Hwulex]

Ah right, I see what you mean.
Sorry :(

Looks like I might have to forget this without cron jobs. Shame :(

[Icheb]

Why shouldn't this work? Just try it!

"First, get the number of the current month with PHP's date, then get the UNIX time of the first day in the current month and query the table for all entries which have a higher value in the time column."

[Hwulex]

Originally posted by Icheb
Why shouldn't this work? Just try it!

"First, get the number of the current month with PHP's date, then get the UNIX time of the first day in the current month and query the table for all entries which have a higher value in the time column."
Hi Icheb.
Thanks for your pointers. I didn't quite follow what you were getting at the first time I read it, but looked again last night with a clear head, did some research, and have solved the problem.

Code looks something like this:
// Member of the month stuff
$thismonth = mktime (0,0,0,date("m"),1, date("Y"));
$lastmonth = mktime (0,0,0,date("m")-1,1, date("Y"));
$motm = $DB_site->query_first("
SELECT
u.userid,
u.username
FROM post p
LEFT JOIN user u ON (p.userid = u.userid)
LEFT JOIN usergroup g ON (u.usergroupid=g.usergroupid)
WHERE p.dateline >= $lastmonth
AND p.dateline <= $thismonth
GROUP BY u.userid
ORDER BY condition
DESC
");

Cheers.