Help with row count

I need a little help with row count. i manage to add today开发者_开发问答 and total members count (rows). i want to count this week and this month. can anyone point me out how to do it? thanks.

$result = mysql_query("SELECT * FROM members");
$num_rows = mysql_num_rows($result);

echo "$num_rows Members\n";

$utoday = date("j. n. Y");

$today = mysql_query("SELECT * FROM mambers WHERE date='$utoday' ");
$num_today = mysql_num_rows($today);

echo "$num_today Members\n";

---

If you stored the date as a type date, you can use the mysql built-in time functions.

For example, you can group by MONTH(date).

---

If you want to count for this week starting from the most recent Monday:

SELECT COUNT(1) WeekCount
FROM members A,
(
   SELECT
       (MondayDate + INTERVAL 0 SECOND) PastMonday,
       ((MondayDate + INTERVAL 7 DAY) + INTERVAL 0 SECOND) NextMonday
   FROM 
       (SELECT DATE(NOW() - INTERVAL WEEKDAY(NOW()) DAY) MondayDate) AA
) B
WHERE date >= PastMonday AND date < NextMonday
;

If you want to count for this month starting from the 1st query this:

SELECT COUNT(1) MonthCount
FROM members A,
(
    SELECT FirstOfThisMonth,
    ((FirstOfThisMonth + INTERVAL 32 DAY) - INTERVAL (DAY(FirstOfThisMonth + INTERVAL 32 DAY)-1) DAY) FirstOfNextMonth
    FROM
    (
        SELECT (DATE(NOW() - INTERVAL (DAY(NOW())-1) DAY) + INTERVAL 0 SECOND) FirstOfThisMonth
    ) AA
) B
WHERE date >= FirstOfThisMonth AND date < FirstOfNextMonth
;

Give it a Try !!!