dates

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

dates

Post by gurjit »

Hi all,

I have this query which bring me the results for a counter weekly and displays the date.

I need for it to start at the start of every month (e.g. 01 May 2005), count the results for every week and then make the last date the end of the month (31st May 2005).

How can i do this:

Code: Select all

<?php
select DATE_FORMAT(DATE_SUB(h_date, INTERVAL WEEKDAY(h_date) DAY), \"%d/%m/%y\" ) AS start_of_week, DATE_FORMAT( DATE_SUB( DATE_ADD(h_date, INTERVAL 6 DAY ), INTERVAL WEEKDAY(h_date) DAY), \"%d/%m/%y\" ) AS end_of_week, DATE_FORMAT(h_date, \"%u %M %Y\") AS week, SUM(h_count) AS week_total,e.* FROM tbl_hitcounter e WHERE DATE_FORMAT(h_date, \"%M %Y\") = DATE_FORMAT(NOW(), \"%M %Y\") GROUP BY week ORDER BY week DESC
?>
the display should look like this

25/05/05 - 31/05/05 4802

17/05/05 - 24/05/05 5053

09/05/05 - 16/05/05 4788

01/05/05 - 08/05/05 5000
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

$start = strtotime("1-$month-$year");
$end = strtotime("1-$month-$year +1 months");

Code: Select all

SELECT YEARWEEK(h_date) AS yearweek, SUM(h_count) AS week_total
FROM tbl_hitcounter
WHERE UNIX_TIMESTAMP(h_date) &gt;= $start AND UNIX_TIMESTAMP(h_date) &lt; $end
GROUP BY YEARWEEK(h_date)
ORDER BY yearweek DESC
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

Hi timvw

does the same as my other query.

I need the dates to display like a calendar, counting only to the end of the month and displaying dates to end of the month, for example

06/06/05 - 12/06/05 900

01/06/05 - 05/06/05 800

-----------------------------------------------------------
30/05/05 - 31/05/05 600

23/05/05 - 29/05/05 4802

16/05/05 - 22/05/05 5053

09/05/05 - 15/05/05 4788

01/05/05 - 08/05/05 5000

The cut of point for the month is where it ends (30/05/05 - 31/05/05)

so when the new month starts it counts to sunday again (01/06/05 - 05/06/05)
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

need help on this urgently..... cant get it to just read like a calendar
Post Reply