Crazy SQL scripts involving DATELINE and DATE()

[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:

PHP Code:

// 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.